How to create a report on computers, their associated monitors and their primary user

Products

• Asset Management Solution

Issue/Introduction

Question

How would I create a report showing monitors associated with computers and their primary user?

Resolution

Answer
The following SQL query can be used in a raw sql type report;

-- COMPUTER AND MONITORS ATTACHED
SELECT vc.[name] AS [Computer Name], i.[name] AS [Monitor Name], vc.[Domain], vc.[User] FROM vComputer AS vc
 JOIN ResourceAssociation AS ra
  ON ra.[ParentResourceGuid] = vc.[guid]
 JOIN item AS i
  ON i.[guid] = ra.[ChildResourceGuid]
 WHERE i.[ClassGuid] = '7D82AFFD-C0A1-47D9-9C09-E6DCA6E51789' -- Monitor classguid
   AND vc.[guid] in (
 -- LIST OF PC's IN COST CENTERS WE WANT TO LOOK AT
 SELECT vc.[guid] FROM vComputer AS vc
  JOIN ResourceAssociation AS ra
   ON ra.[ParentResourceGuid] = vc.[guid]
  JOIN item AS i
   ON i.[guid] = ra.[ChildResourceGuid]
  WHERE (i.[ClassGuid] = '1CF09D9E-B060-4B01-AC7F-EDFE4B08E646'
     OR i.[ClassGuid] = '9663B667-C60D-4246-81C8-0F7E09587384') -- Cost Center classguids
    AND i.[name] in ('009001','009007','009171','009271','009301','009401','009551','009621','009701','009902','009903') -- cost centers
   )
ORDER BY vc.[user], vc.[name]

The query should provide you with a starting point for this sort of information.

 

The WHERE statement in the sub-query narrows the results down to certain cost centers as well. If you do not have this need or have a different need you can use this as an example to perform such a 'narrowing down'.