Reporting Group

I'm looking for a report that includes all clients in CMS with (at least) the following fields.  I'm fine with extra fields I can hide once exported into Excel if that's easier.

Computer Name, IP address, operating system (or OS Name), Model, Serial number, RAM, Type (laptop/desktop/docking station), Primary Owner & also last check in time (or client date).

I have 2 reports that are close to what I want, but neither are perfect.

Attached report has almost everything I need except last check in/client date.  I have another report that has last checkin date but is missing the hardware items.

Can anyone help, please?

Attachment(s)

SQL inventory report.txt   1 KB 1 version

For Last Checkin which field are you using from your other report?

Add a Field

, i.[Client Date]

Add a JOIN

LEFT JOIN Inv_Aex_Ac_Identification i
    ON i._ResourceGuid = vc.[Guid]

Depending on the SMP version and which Field you wish to use you could add details from

Inv_AeX_AC_Client_Status

Other report column on other report is Client Date.  The report is just set to show all fields I think (below), but is missing the hardware stuff.  I'm on 8.1 RU6.

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 

So is the above addition of [Client Date] to your attached Report enough?

I believe [Client Date] from the Inv_Aex_Ac_Identification table is the local date on the client when the last basic inventory was sent.  If your clients only send basic inventory once a day, then last config request or last event received would be a better gauge if your goal is to see what machines are active.  However, If you are just wanting to know machines that have been offline for days or weeks, you may not care about time zones or recent activity. 

I see you are filtering the results to take out machines with blank serial numbers.  This will cause slow processing of the query and you may miss machines that have not run hardware inventory so I replaced it with filtering on only managed machines.  This should avoid reporting on machine records imported from AD for example that aren't actually running a client but include the rest.  

Here is an updated report with Last Config Request date added and the 'Type' field commented out so you can see how much faster it runs.  If you have machines in multiple time zones, this method shows the date / time of the NS when the event was received instead of client time.  

SELECT    vc.[GUID]
        , vc.Name AS 'Computer Name'
        , vc.[Domain]
        , vc.[IP Address]
        , vc.[OS Name]
        , vc.[OS Revision]
        , cs.Manufacturer
        , cs.Model
        , cs.[Identifying Number] AS 'Serial Number'
        , p.Model AS 'Processor'
        , cs.[Total Physical Memory (Bytes)] / 1048576 AS 'RAM (MB)'
        , vi.Name AS 'Location'
        , vc.[User] AS 'Primary Owner'
        , c.[Chassis Package Type]
        , ISNULL(s.[String],'Other') [Type]
        ,(    SELECT TOP 1 CC.[StartTime] FROM [evt_ns_client_config_request] CC
        WHERE CC.[ResourceGuid] = vc.[Guid]
        ORDER BY CC.[StartTime] DESC
    ) AS [Last Config Request]
FROM vComputer vc

LEFT OUTER JOIN ResourceAssociation  loc
    ON (loc.ChildResourceGuid = vc.Guid OR loc.ParentResourceGuid = vc.Guid)
    AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'
LEFT OUTER JOIN vItem vi
    ON loc.ChildResourceGuid = vi.Guid
LEFT OUTER JOIN vHWProcessor p
    ON vc.Guid = p._ResourceGuid
INNER JOIN vHWComputerSystem cs
    ON vc.Guid = cs._ResourceGuid
INNER JOIN Inv_HW_Chassis c
    ON vc.Guid = c._ResourceGuid
LEFT OUTER JOIN String s
    ON s.StringRef = 'listitem.chassis.chassistypes.' + CAST(c.[Chassis Package Type] as nvarchar(2))
    AND s.Culture=''
WHERE vc.ismanaged = 1
    --and cs.[Identifying Number] IS NOT NULL
   and vc.[OS Name] NOT LIKE '%server%'
ORDER BY vc.Name 

Thanks @joevan that report works perfectly for me, I think.  Load time isn't really an issue for me (perhaps because we only have ~600 clients).  Going to run through it and will mark your post as solution shortly once I double check.

Don't know if I noticed before, but nice surprise CMS inventories docked computers as docked, helps me know who needs a dock budgeted for them.