If you have workstation asset scanning in place within ServiceDesk Plus, then you have a wealth of information available to you about what software has been installed where across your organisation. We install have the AssetExplorer agent installed on all our PCs and laptops, set to scan on startup. In a global organisation with network links of varying capacity, we found this the best way of making sure that we got regular information back from workstations. (A network or domain scan would have to account for time zone differences, and I was concerned about the server reaching out across the global WAN for machines that weren’t actually there).
There are options within SDP to discard old scan history, but we have left those switched off, meaning that we can show a full history of the machine from when it was first seen on our network.
Today, a series of example queries around installed software. We’ll use Internet Explorer 9 as our example, but clearly you could use any software of your choosing. Do make sure you spell the software name exactly as it appears in SDP. On the Assets tab, choose Scanned Software from the sidebar on the left.
These queries are designed to be run against Microsoft SQL Server in SQL Management Studio. They’re use the date functions I described in an earlier post – you’ll need to add those to your installation before trying these. However, as these queries all execute as a single statement, they could easily be modified to run as a Custom Query on the SDP Reports tab.
First of all, a simple one: who has this software installed.
SELECT workstationname as [Workstation], Model, loggeduser as [Logged on user], dbo.my_BIGINT_TO_DATE(ah.audittime) AS [Last Scanned], u.first_name as [Owner], sdu.jobtitle as [Job title], dd.deptname as [Department], sdo.name as [Site] FROM systeminfo AS si left outer join resourceowner AS ro ON si.workstationid = ro.resourceid left outer join aaauser AS u ON ro.userid = u.user_id left outer join sduser AS sdu ON u.user_id = sdu.userid left outer join userdepartment AS ud ON sdu.userid = ud.userid left outer join departmentdefinition AS dd ON ud.deptid = dd.deptid left outer join sdorganization AS sdo ON sdo.org_id = dd.siteid left outer join audithistory AS ah ON ah.workstationid = si.workstationid WHERE ah.auditid = (SELECT MAX(ah2.auditid) FROM audithistory AS ah2 WHERE ah2.workstationid = si.workstationid) AND si.workstationid IN (SELECT workstationid FROM softwareinfo AS soi INNER JOIN softwarelist AS sol ON soi.softwareid = sol.softwareid WHERE sol.softwarename = 'Windows Internet Explorer 9') ORDER BY ah.audittime DESC
I tend to include the scan time in here to indicate how up-to-date the information is. Also, in this example, I’ve assumed that you’re assigning workstations to Requesters and show that information also.
You can modify the query to show machines that do NOT have the software installed by editing line 21 to say ‘NOT IN’ instead of just ‘IN’.
If you’re trying to track the spread of a piece of software across your organisation, this script will show you the individual installs and uninstalls. This is great for tracking the progress of an automated software deployment. Given that we scan workstations on start up, this works especially well for us since many pieces of software ask for a reboot after installing.
SELECT dbo.my_BIGINT_TO_DATE(ah.audittime) AS [Operation date], si.loggeduser AS [Logged ON user], u.first_name AS [Name], sdu.jobtitle AS [Job title], dd.deptname AS [Department], sdo.name AS [Site], si.workstationname AS [Workstation], CASE ao.operationstring WHEN 'Delete' THEN 'Uninstalled' ELSE 'Installed' END AS [Opertation] FROM swaudithistory AS swah INNER JOIN softwarelist AS sl ON swah.softwareid = sl.softwareid INNER JOIN audithistory AS ah ON swah.auditid = ah.auditid INNER JOIN systeminfo AS si ON ah.workstationid = si.workstationid INNER JOIN auditoperation AS ao ON swah.operation = ao.operation LEFT OUTER JOIN resourceowner AS ro ON si.workstationid = ro.resourceid LEFT OUTER JOIN sduser AS sdu ON ro.userid = sdu.userid LEFT OUTER JOIN aaauser AS u ON ro.userid = u.user_id LEFT OUTER JOIN aaalogin AS l ON l.user_id = ro.userid LEFT OUTER JOIN departmentdefinition AS dd ON ro.deptid = dd.deptid LEFT OUTER JOIN sdorganization AS sdo ON sdo.org_id = dd.siteid WHERE sl.softwarename = 'Windows Internet Explorer 9' ORDER BY ah.audittime ASC
In this case, I’ve shown the user logged on to the PC, their department and site rather than the assigned owner within SDP. This was useful for my needs because it indicates who was responsible for installing/uninstalling the software. Well, sort of. Since there’s a lag between when the software is installed and when the machine is scanned, it may not be the right user, but it’s the best guess we’ve got.
Finally, a query that shows the different builds/versions of the software deployed. Just a summary in this case, although the detail is available.
select soi.fileversion AS [Version], COUNT(*) AS [Installs] FROM softwarelist AS sl INNER JOIN softwareinfo AS soi ON soi.softwareid = sl.softwareid WHERE softwarename = 'Windows Internet Explorer 9' GROUP BY soi.fileversion