Video Screencast Help

Inventory Reporting - User Information Report

Created: 20 Aug 2013 • Updated: 21 Aug 2013 | 8 comments
This issue has been solved. See solution.

I am trying to create a report that shows the machine and user/department/company information. Machine with user info is easy enough but what about department and company information?  We do have the information in AD,  I created an import rule from ad to import user info.

Operating Systems:

Comments 8 CommentsJump to latest comment

etk1131's picture

If you have Asset Management installed, you can run the Assets by Owner's department report - which gives you the machine, user, and department information.  It's under Reports\Service and Asset Management\Assets.

JeanWilson's picture

we dont have asset management, would make life much easier.

MichaelCiv's picture

Sure, run this, let me know how it works...as long as your AD import is working this should return what you need. If your missing anything you want, let me know and ill be happy to add it for you. I also used a LEFT join for the user data table. This will return rows even if their is missing user info (like department, email, phone etc) if you want to turn that off and only return machines that have full user info just take out that LEFT and leave it as JOIN...

------

SELECT

       vc.[Name] AS Computer_Name,

       vc.[OS Name] as Operating_System,

       pu.[User] AS Primary_User,

       vu.[office telephone] AS Telephone,

       VU.[Department],

       vu.[Email],

       s.[Manufacturer] [System Manufacturer],

       s.[Model] [Computer Model],

       s.[Identifying Number] [Serial Number]

FROM

       vComputer vc

       JOIN Inv_AeX_AC_Primary_User pu ON pu._ResourceGuid = vc.Guid

       LEFT JOIN vUser vu ON vu.[Name] = pu.[User]

       JOIN dbo.vHWComputerSystem s

    ON s.[_ResourceGuid]=vc.Guid

WHERE

       vc.[Name] like '%'

SOLUTION
JeanWilson's picture

Michael,

Thanks for the query, only one thing.  Some fields are missing in the department, company fields.  When I look at the table Inv_Global_User_General_Details the department and company fields field out.  I tried joining that table, but I am not having any success.  By the way I configured the 'default column mappings' Company and Department.  Things look good in the table Inv_Global_User_General_Details, but the vUser table is missing some data in Company and Department fields.  Could you help me joining v_Global_User_General_Details to the report?

Thanks!

JeanWilson's picture

I figured out what i was doing wrong, I was trying to joing Primary user's guid to global users guid instead of joining vuser table display name and v_Global_User_General_Details display name.  Thanks a whole bunch Michael!

sdmayhew's picture

I do this query a little bit differently as I have noticed issues at time with primary user in my company

select * 
 
FROM dbo.Inv_AeX_AC_Identification ac
left join (SELECT     _ResourceGuid, MAX([user]) AS [primary user]  
FROM         dbo.Inv_AeX_AC_Primary_User   
WHERE     ([User] IS NOT NULL) AND ([User] <> '') 
GROUP BY _ResourceGuid  ) Prime                          
on ac._resourceguid =  Prime._ResourceGuid
LEFT OUTER JOIN  dbo.Inv_Global_Windows_Users  T20                                                                                        
ON Prime.[primary user] = t20.[USERID] 
LEFT OUTER JOIN dbo.Inv_Global_User_General_Details  T10                                                                                        
ON t20._ResourceGuid = T10._ResourceGuid 

Altiris user since 2001, Asset Management for 25 years