Use the following query to identify whether an older version of an application is present within the Snow License Manager (SLM) application overview on a computer, based on the data provided by a connector (such as SCCM or Intune).

 
--Step 1  Create a Temporary Table: Begin by creating a temporary table that contains all the connector application data for each computer. You can further refine this by adding filters to target specific sources (e.g., %SCCM%).

SELECT * INTO #TempResults FROM
(
select Distinct ca.ComputerID,ca.ApplicationID,csp.Source from SnowLicenseManager.dbo.tblComputerApplications as CA
inner  join SnowLicenseManager.dbo.tblComputerSoftwareProduct csp
on csp.ComputerId = ca.ComputerID
inner join SnowLicenseManager.inv.tblComputerInvSlmMap map on map.ComputerID = csp.ComputerID
inner join SnowLicenseManager.dbo.tblSoftwareProductApplicationMap as spam
on spam.SoftwareHash = csp.SoftwareHash
inner join SnowLicenseManager.dbo.tblApplication as a
on ca.ApplicationID = a.ApplicationID
where csp.Source like '%%'and isos like '0' and spam.softwarehash is not NULL and spam.ApplicationID not in (
select b2.ApplicationID FROM SnowInventory.inv.DataSoftware as a2
INNER JOIN SnowInventory.inv.DataApplication c2
ON c2.ApplicationId = a2.AppId
inner join SnowLicenseManager.dbo.[tblSoftwareApplicationMap] as D2
on c2.Checksum = d2.SoftwareCheckSum
inner join SnowLicenseManager.dbo.tblApplication as b2
on D2.ApplicationId = b2.ApplicationID )GROUP BY ca.ComputerID,ca.ApplicationID,csp.Source
) AS T;

-- Step 2: Display All Application Data from the Temporary Table: Show all application data that has been added to the temporary table. You can further enhance this by adjusting the WHERE clause in the highlighted section to apply additional filters.

Select [Name],a.ApplicationID,b.ComputerID,c.HostName from SnowLicenseManager.dbo.tblApplication as a
left join #TempResults as b
on a.ApplicationID = b.ApplicationID
left join SnowLicenseManager.dbo.tblComputer as c
on b.ComputerID = c.ComputerID
where a.ApplicationID in (SELECT applicationid FROM #TempResults) and a.name like '%%'

--Step 3: Remove the Temporary Table and Clean Up the Temporary Data: Delete the temporary table and ensure that any temporary data associated with it is properly cleaned up.

DROP TABLE #TempResults;