Report help

Asmodeus's picture
Asmodeus
September 17th, 2008

Hi all,

I am looking for some advice on report writing. I am trying to write a report that lists all laptop/notebook/portable devices. i can get the report to show these but I want to view the department that they are in.

This is what I have so far....

SELECT T0.[Name] AS 'Name', T1.[Computer Type] AS 'Computer Type', T1.[Computer Model] AS 'Computer Model', T1.[System Manufacturer] AS 'System Manufacturer' FROM [vResourceEx] T0 INNER JOIN [Inv_AeX_HW_Serial_Number] T1 ON T0.[Guid] = T1.[_ResourceGuid] WHERE (T1.[Computer Type] = 'Subnotebook' OR ((T1.[Computer Type] = 'Notebook' OR T1.[Computer Type] = 'Laptop') OR T1.[Computer Type] = 'Portable')) ORDER BY T0.[Name] ASC

Any ideas what table I need to join to get the department data?

Thanks in advance.

Filed under: ,
0 votes
cwitter's picture
cwitter
1 year 7 weeks ago

Query Help

You'll need to use the resourceassociation table. A good way to see all the associations is to figure the guid of a computer for example

select guid from item where name = 'computername'
then
select * from resourceassociation where parentresourceguid = 'guid'

There will be a column called resourceassociationtype this will be different for each association for example there will be one for owner, status, etc. The parentresourceguid will be the guid of the computer and the childresourceguid will be the owner, status, etc. You can always look up the guid to see what it is by

select name from item where guid = 'guid'. I suspect you'll want the asset owners association. That's what we currently use to return the owner of a specific asset.

Hope this helps,

Craig

0 votes