SMP 7.1 Database Table containing Hardware Model?
I was hoping to find someone that can quickly answer where to find the Hardware resource table contianing the Model of the resource. The closest I can come up with is the following query;
SELECT
[vri1_Resource].[Guid],
[vri1_Resource].[Name]
FROM
[vRM_Resource_Item] AS [vri1_Resource]
INNER JOIN Inv_HW_Baseboard
ON Inv_HW_Baseboard._ResourceGuid = vri1_Resource.Guid
WHERE
[Part Number] = '0DW634' OR
[Part Number] = '09M4GV' OR
[Part Number] = '023HKR' OR
[Part Number] = '03PH4G';
Looking at the Hardware resource from the console I can see the text "Latitude E5500" and I know in our environment all of our HW_Baseboard.[Part Numbers]. However, is there an easier query that I can do to include;
WHERE Model LKE '%Latitude E55%';
Instead of having to track down the seemingly random characters each vendor/product users? This query would really be beneficial for us to pull a quick report of particular models or even build a filter to use as a target for model specific actions.
Comments
Most manufacture & model
Most manufacture & model information is going to be found in the table Inv_HW_Logical_Device.
The table will contain multiple entries per client machine. To make queries easier for customers a view has been created for each hardware type that already includes the information from Logical Device. Modifying your query to the one below should provide the information you are looking for.
SELECT vr.[Guid], vr.[Name]
FROM [vRM_Resource_Item] vr
JOIN vHWBaseboard bb
ON bb._ResourceGuid = vr.Guid
WHERE
bb.Model LIKE '%Latitude%'
Several
Several hold this data, or at least several views do.
Here's a quick and dirty:
SELECT vc.[Name],vc.[User],vc.[OS Name],vc.[IP Address],cs.[Manufacturer],cs.[Model]
FROM vComputer vc
JOIN vHWComputerSystem cs ON vc.[Guid]=cs._ResourceGuid
Does this get you what you're looking for?
Mike Clemson, Senior Systems Engineer
Intuitive Technology Group -- Symantec Platinum Partner
When I tried TGiles query it
When I tried TGiles query it returned no results and I'm not sure why exactly but I did find mclemson's query to pull up the 61 machines that my originally posted query returns so I'm leaning towards the following query;
SELECT vc.[Name],vc.[User],vc.[OS Name],vc.[IP Address],cs.[Manufacturer],cs.[Model]
FROM vComputer vc
JOIN vHWComputerSystem cs ON vc.[Guid]=cs._ResourceGuid
WHERE Model LIKE '%Latitude E55%';
Thank you both for the fast response and the queries.
You could also add a parameter
You could also add a report parameter and then a query parameter and then add it into your query. So if your report parameter was named Computer Model your WHERE close would look like: "WHERE Model LIKE '%Computer Model%'"
When you run the report, if the default value is %, it will return all computers, but if you enter %Latitude D% it will return all Latitude D-series laptops. (Of course, you could just run the entire query then type Latitude D in the filter field, thus limiting the results.)
Mike Clemson, Senior Systems Engineer
Intuitive Technology Group -- Symantec Platinum Partner
Would you like to reply?
Login or Register to post your comment.