I tried building a report in Lansweeper Cloud which should report that certain software is not installed.
However, it seems impossible to me to build reports with negative results.
I already tried contacting support, but they say that they do not provide support for building custom reports in Lansweeper Cloud yet and referenced to the very basic Introduction to the Cloud report builder article.
Does anybody here have experience creating these custom reports with negative results in Lansweeper Cloud if that's possible at all?
And if so, do you have some tips and tricks to build such reports?
I'm by no means a pro at creating reports. It takes me alot of playing around and figuring it out.
The report below works by setting a scan for a file location, using the date modified.
If the date is greater than (>) it is green
If it is older than (<) it is red
If it does not exist, it is yellow, but says Out of date.
Select Top 1000000 tblAssets.AssetID, tblAssets.AssetName, tsysAssetTypes.AssetTypename, tsysAssetTypes.AssetTypeIcon10 As icon, tblAssets.IPAddress, tblAssets.Lastseen, tblAssets.Lasttried, tblFileVersions.Found, tblFileVersions.FilePathfull, Case When tblFileVersions.LastModified > '12/06/2022 00:00:00' Then 'Up to date' Else 'Out of date' End As [Patch Status], Case When tblFileVersions.LastModified < '12/06/2022 00:00:00' Then '#ff6666' When tblFileVersions.LastModified >= '12/06/2022 00:00:00' Then '#009900' When tblFileVersions.Found = 'false' Then '#ffff00' End As backgroundcolor, tblFileVersions.FileVersion, tblFileVersions.CreationDate, tblFileVersions.LastModified From tblAssets Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID Where tblFileVersions.FilePathfull = 'C:\file path to software' And tblAssetCustom.State = 1
Please note: I only have on premise - not cloud version of lansweeper. Our license is old enough it doesn't allow it.
You will need to set up a file & registry scanning scan.
If I had more time available, I'd re-write the report for using the installed software reports from scanning.
Thanks for your contribution, however, the Cloud and on-premises report builders use two diverse types of databases. Lansweeper Cloud uses MongoDB queries, and the on-premises uses SQL queries. Normally I would use a SQL query like this in the on-premises report builder:
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon, tblAssets.AssetID, tblAssets.AssetUnique, tblAssets.AssetName, tblAssets.Username, tsysIPLocations.IPLocation, tblADusers.City, tblAssets.Lastseen, tblAssetCustom.Model From tblAssets Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID Inner Join tblADusers On tblAssets.Username = tblADusers.Username Inner Join tblState On tblState.State = tblAssetCustom.State Where tblAssets.AssetID Not In (Select tblSoftware.AssetID From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID Where tblSoftwareUni.softwareName Like 'Adobe Reader') And tblAssets.Lastseen <> '' And tblAssets.Assettype = -1 And tblState.Statename != 'Stock' And tblState.Statename != 'Stolen' Order By tblAssets.AssetUnique
Unfortunately I cannot figure out how to exclude assets in the MongoDB query the same way as the inner join in the SQL query.