Law Thirty-Six

You gotta go with what works

SDP logo

Reporting on software installations in ServiceDesk Plus


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],
	   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], 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
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
						 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], 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
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

Leave a Reply

%d bloggers like this: