Report to display missing applications on Windows PCs by department

[Tron]

Hello,

I would like help writing a report that will be run querying the department field and return results of missing software from a windows computer. My goal is to be able to check all users in a department and that their assigned computers have all the needed applications. I would then write a report for each department and audit it against their own application list

For example, I have engineers working in a department called Civil Structural Department. I run a report against all the users in the Civil Structural Department and the report would look at each windows computer assigned to a Civil Structural user and return results for missing applications. An example of a list of missing applications for this department would include Autodesk AutoCAD, Aveva PDMS, & Google Sketchup. Of 15 PCs, one PC assigned to a Civil Structural Engineer is missing Autodesk AutoCAD. I would expect the report to show the AssetName, the user’s display name, the user’s login, the user’s department, and the missing application(s), in the case AutoCAD. From this information I can proactively keep windows PCs up to date for each discipline.

I wrote a query that displays an asset and the relationship it has to a user. The report displays the AssetName, the user’s display name, the user’s login, and the user’s department. What I would like to do next, and am having troubles with, are select a department and filter out only assets that are missing a SoftwareName audited against a predefined list. Items that show up on the report would also report back what SoftwareName it is missing .

Please see below the SQL query I have started out with.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblADusers.Displayname,
tblADusers.Department,
tblAssets.Username As Login
From tblAssets
Inner Join tblADusers On tblAssets.Username = tblADusers.Username
Inner Join tblAssetUserRelations On tblAssets.AssetID =
tblAssetUserRelations.AssetID And tblADusers.Username =
tblAssetUserRelations.Username And tblADusers.Userdomain =
tblAssetUserRelations.Userdomain

Thanks for the help

[Hemoco]

Use the report down here and change the green word with the name of the software version:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblADusers.Displayname,
tblADusers.Department,
tblAssets.Username As Login,
Case When tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Sketchup%') Then 'not installed'
Else 'installed' End As sketchup,
Case When tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%autocad%' And
tblSoftware.softwareVersion = '18.2.51.0') Then 'not installed'
Else 'installed' End As autocad
From tblAssets
Inner Join tblAssetUserRelations On tblAssets.AssetID =
tblAssetUserRelations.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssetUserRelations.Username
And tblADusers.Userdomain = tblAssetUserRelations.Userdomain
Where ((tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Sketchup%')) Or
(tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%AutoCAD%' And
tblSoftware.softwareVersion = '18.2.51.0'))) And tblADusers.Department =
'name'

[Tron]

Thank you very much. This report runs and it will allow me to begin writing reports for each department.

I have been successful in adding more applications using the example you have provided as a template. Would be it also be possible to check against not only the software name but also the version too? For example AutoCAD 2012 - English and the version being 18.2.51.0?

[Hemoco · Accepted solution]

Can you please try again with the following query:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblADusers.Displayname,
tblADusers.Department,
tblAssets.Username As Login,
Case When tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Sketchup%') Then 'not installed'
Else 'installed' End As sketchup,
Case When tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%autocad%') Then 'not installed'
Else 'installed' End As autocad
From tblAssets
Inner Join tblAssetUserRelations On tblAssets.AssetID =
tblAssetUserRelations.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssetUserRelations.Username
And tblADusers.Userdomain = tblAssetUserRelations.Userdomain
Where ((tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Sketchup%')) Or
(tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%AutoCAD%'))) And
tblADusers.Department = 'name'

[Tron]

Thank you for the quick response admin. I copied the SQL script into the report editor and when I attempt to run the report I receive this error.

There was an error parsing the query. [ Token line number = 1,Token line offset = 388,Token in error = sketchup ]

When I remove the code referencing Sketchup the error then moves to AutoCAD.

There was an error parsing the query. [ Token line number = 1,Token line offset = 387,Token in error = autocad ]


Thank you for your time in this matter.

[Hemoco]

Please use the following report. Please change the green word with the name of the department that you want to filter on.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblADusers.Displayname,
tblADusers.Department,
tblAssets.Username As Login,
Case When tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Sketchup%') Then 'not installed'
Else 'installed' End As 'sketchup',
Case When tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%autocad%') Then 'not installed'
Else 'installed' End As 'autocad'
From tblAssets
Inner Join tblAssetUserRelations On tblAssets.AssetID =
tblAssetUserRelations.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssetUserRelations.Username
And tblADusers.Userdomain = tblAssetUserRelations.Userdomain
Where ((tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Sketchup%')) Or
(tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%AutoCAD%'))) And
tblADusers.Department = 'name'