Trying to run report for assets without particular service running

[TylerVice]

I am trying to find assets without our antivirus by scanning their services running, as our AV does not show up on the installed software list and is not being picked up through the usual WMI (for some reason, windows 10 machines only having this problem). I am currently running the following SQL, but it brings back several lines of services that aren't the one I'm looking for... I really just need a single line per asset that doesn't have that service running.

Please pardon my lack of SQL skill ahead of time...

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblServicesUni.Caption
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype,
tblServicesUni
Where tblServicesUni.Caption != 'VIPRE Edge Protection'

[Esben_D]

No problem.

What Not In basically does is run a small report within the main report, AKA a subquery (in this case a subquery which gives back all the assets IDs of asset which do have the service running), then it compares the results with the results of the main report, when it finds a matching asset ID it excludes it from the result it displays.

[TylerVice]

Thanks Charles! That worked perfectly.

I did have a suspicion that I should be using Not In, but my attempts weren't working... I will have to keep reading through this SQL tutorial to get a better understanding of the Not In string you did there and how the Ons and Inners work there. My guess is that essentially we are saying not in the data we were retrieving before.

I appreciate your time and assistance.

Tyler

[Esben_D]

You are correct, my mistake.

It is better to use a Not In subquery. The result of using this subquery is that only assets with which are not in the subquery are displayed. Since the subquery only has assets which do have the survice running, the result should be that only assets which do not have the service running

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblServices On tblAssets.AssetID = tblServices.AssetID
  Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
    tblServices.ServiceuniqueID
Where tblAssets.AssetID Not In (Select Top 1000000 tblAssets.AssetID
  From tblAssets Inner Join tblServices On tblAssets.AssetID =
      tblServices.AssetID Inner Join tblServicesUni
      On tblServicesUni.ServiceuniqueID = tblServices.ServiceuniqueID
  Where tblServicesUni.Caption = 'VIPRE Edge Protection')

[TylerVice]

To see what was happening, I rechecked the display box and sure enough it just returning a line for every single service other than Vipre Edge Protection, including lines from assets that do actually have it on the list.

Is there a way to reduce it to identify assets with the service missing from the list?

[TylerVice]

Impressive response time--no wonder this customer service team gets so much praise.

Your instructions makes sense to me, and the code makes sense to me...and it does bring back 1 line per asset. My only problem is that it is now showing assets that do actually have that service running... When I take the first result and open up its Services list, it is right there on the list. Perhaps this is grabbing all assets that have other services running? I am looking to report assets that just don't have Vipre anywhere on the list.

Thank you so much for your assistance!

[Esben_D]

First off, good effort for trying, it's a good way of learning SQL.

You will have to make sure all your tables are linked in your query. I noticed that tblServicesUni wasn't linked to anything. Therefore I added tblServices which is how you can link the name of the service to the ID of the service and as a result the asset it is running on.

Since your current criteria shows all services but "VIPRE Edge Protection", you will receive a row for every service on the asset which doesn't have that caption. You can add the DISTINCT function at the top of the query. Distinct will only show unique rows, since you still have a row for each service caption, if you have a lot of duplicate rows, the DISTINCT function will make sure you only have 1 row per asset.

Lastly, If you are interested in building or modifying reports, I do also recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial: https://www.w3schools.com/sql/
  
• Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here: https://www.lansweeper.com/Forum/yaf_postst9870_Lansweeper-database-dictionary.aspx#post38296

Revised report from below:

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblServices On tblAssets.AssetID = tblServices.AssetID
  Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
    tblServices.ServiceuniqueID
Where tblAssets.AssetID Not In (Select Top 1000000 tblAssets.AssetID
  From tblAssets Inner Join tblServices On tblAssets.AssetID =
      tblServices.AssetID Inner Join tblServicesUni
      On tblServicesUni.ServiceuniqueID = tblServices.ServiceuniqueID
  Where tblServicesUni.Caption = 'VIPRE Edge Protection')