Patch Management Group

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.

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.

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

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 '%'

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!

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!

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

No problem, I'm glad it worked for you!