Custom Report - Blacklisted applications with computer and user data

Document created by mark.potts Employee on Nov 3, 2017Last modified by mark.potts Employee on May 9, 2018
Version 2Show Document
  • View in full screen mode

This report returns all of the installed, blacklisted applications, it then pulls the most frequent user from the computer, and returns the last used date against the application (Although not specifically that user)


The purpose of the report is to allow us to identify the computers that we need to target for blacklisted software removal and get their primary user details so we know who to contact, also to prioritise those with actual usage.


You can load this into Snow License Manager using the post I provided earlier.


select distinct tca.ComputerID, [HostName] as 'Computer Name', MAX([LastScanDate]) as 'Last Scan Date', [Name] as 'Application Name', [MostFrequentUser] as 'Computer Primary User', max(TCAU.LastUsed) as 'Last Used', tca.IsInstalled

from tblComputerApplications tca

inner join tblApplicationBlacklist tab on tca.ApplicationID = tab.ApplicationID

left join tblApplication tap on tab.ApplicationID = tap.ApplicationID

left join rptComputers rco on tca.ComputerID = rco.ComputerID AND rco.SystemUSerID = 1

left join tblComputerApplicationUsers tcau on tca.applicationid = tcau.applicationid AND tca.computerid = tcau.computerid

group by tca.ComputerID, [HostName], [Name], [MostFrequentUser], tca.IsInstalled

17 people found this helpful