Handy SQL Queries
Like many others, I found the current report builder for 6.5 to be a little hard to use. So, out of necessity, I learned SQL the hard way.
With that, I will share here, some of the queries that I have found to help me tremendously. Please note that I am using some real life examples, so you may need to tweak some of the data that is matching in the queries:
Find SWD Package if I know the GUID:
SELECT a.name [Advertisement Name], pg.name [Program Name] FROM swdadvertisement a JOIN swdprogram pg ON a.programid = pg.programid WHERE pg.packageid = '315A773A-1F7F-4910-AD90-E7346B19CA78'
Find results from SWD job:
SELECT vc.[Name], vc.[OS Name], swd.[AdvertisementName], swd.[Start], swd.[End], swd.[Status], swd.[ReturnCode] FROM Inv_AeX_SWD_Execution_Summary swd JOIN vComputer vc ON swd._ResourceGuid = vc.Guid WHERE [advertisementname] like '%SQL_Native_Client%'
Restrict results to a specific collection:
SELECT vc.[Guid], vc.[Name] FROM vComputerEx vc join [Inv_AeX_SW_Audit_Software] sw ON vc.[Guid]=sw.[_ResourceGuid] JOIN CollectionMembership cm ON vc.[Guid]=cm.[ResourceGuid] WHERE sw.[File Name]='patrolagent.exe' AND sw.[ProductVersion] like '3.7.0%' AND vc.[IsManaged]='1' AND collectionguid='4ec2f9d8-6a8d-4803-9818-760804a77a30'
Find computers that have been deleted in last N=1 days:
SELECT itemname AS 'Computer Name', deleteddate AS 'Deleted Date' FROM itemdeleted WHERE itemclassguid = '539626D8-A35A-47EB-8B4A-64D3DA110D01' AND datediff(day, deleteddate, getdate()) <=1
Find last time computer checked in:
SELECT * FROM evt_NS_client_config_request WHERE resourceguid like 'PlaceMachineGuidHere'
Last time computer sent Basic Inventory:
SELECT vc.name, vc.domain, vc.[os name], rus.[ModifiedDate] AS 'Last Basic Inventory' FROM vComputer vc JOIN ResourceUpdateSummary rus ON vc.Guid = rus.ResourceGuid AND rus.InventoryClassGuid = 'C74002B6-C7B9-47BB-A5D6-3031AF73BB8D' JOIN collectionmembership cm ON vc.guid = cm.resourceGuid AND cm.CollectionGuid = '75dfb8bb-9695-48b5-9539-053443007211'
Find when specific Data Class was last updated:
SELECT i.Name, rus.ModifiedDate FROM DataClass dc JOIN ResourceUpdateSummary rus ON dc.Guid=rus.InventoryClassGuid JOIN Item i ON rus.ResourceGuid=i.Guid WHERE dc.Name='AeX OS Operating System'
And the best yet for many reasons, such as Disaster Recovery, or building an identical NS or a Test NS and you want to ensure you have the same solutions and their versions on each NS:
SELECT scu.name as [Installed Solutions], substring ( i.state, charindex('', i.state) + 12, (charindex('',i.state) - charindex('',i.state) - 12)) AS [Product Version] FROM item i INNER JOIN solutioncenterupdate scu ON i.guid = scu.guid WHERE scu.installed = 1 AND i.state like '%%' ORDER BY [Installed Solutions]