Unclear how to create a software not installed report in Lansweeper Cloud

[athena]

Hello,

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?

Thanks!

[duster1]

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.

[athena]

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.

[Mercedes_O]

 @duster1 @brandon_jones any tips on these sorts of reports?