→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Tron
Engaged Sweeper
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
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
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'

View solution in original post

5 REPLIES 5
Hemoco
Lansweeper Alumni
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
Engaged Sweeper
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
Lansweeper Alumni
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
Engaged Sweeper
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
Lansweeper Alumni
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'