Need the inverse of this working report

[andrew_exley]

The report below works to identify all assets that HAVE the service "DrAService" present on the asset. The DrAService is "C:\Program Files\Trend Micro\Full Disk Encryption\DrAService.exe".


I am attempting to identify the list of assets that DO NOT have the "DrAService.exe" file present in the location "C:\Program Files\Trend Micro\Full Disk Encryption\".


Your help and assistance are appreciated.



Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysOS.OSname As OS,
tblAssets.OScode + '.' + tblAssets.BuildNumber As Build,
tblAssets.Version As [OS Version],
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssets.Assettype,
tblServiceState.State
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 tblServiceState On tblServiceState.StateID = tblServices.StateID
Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
tblServices.ServiceuniqueID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblServiceState tblServiceState1 On tblServiceState1.StateID =
tblServices.StateID
Where (tblServicesUni.Name Like '%DrAService%' And tblAssetCustom.State = 1) Or
(tblAssetCustom.State = -1)

[JacobH]

Here's in case you want to find the whole thing (missing, Found, Not Scanned (i.e. error) -might need a bit of cleaning up...

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.IPAddress,
  tsysOS.OSname,
  tblAssets.Domain,
  tsysIPLocations.IPLocation,
  Case
    When filelookup.Found = 1 Then 'Yes'
    When filelookup.Found = 0 Then 'Not Found'
    Else 'Not scanned'
  End As [Has DrAService.exe],
  tblAssets.Lastseen
From tblAssets
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblComputersystem On tblComputersystem.AssetID = tblAssets.AssetID
  Left Join (Select tblFileVersions.AssetID,
        tblFileVersions.Found,
        tblFileVersions.FilePathfull,
        tblFileVersions.FileVersion,
        tblFileVersions.CompanyName,
        tblFileVersions.Filesize,
        tblFileVersions.Lastchanged
      From tblFileVersions
      Where tblFileVersions.FilePathfull Like '%Full Disk Encryption\DrAService.exe%') filelookup On
    tblAssets.AssetID = filelookup.AssetID
  Left Join tsysIPLocations On tsysIPLocations.StartIP <= tblAssets.IPNumeric
    And tsysIPLocations.EndIP >= tblAssets.IPNumeric
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tsysAssetTypes.AssetTypename =
  'windows' And tblAssetCustom.State = 1 
Order By tblAssets.AssetName

[andrew_exley]

thank you!

[RCorbeil]

Exactly the same approach:

Where
  tblAssets.AssetID Not In (SELECT
                              tblServices.AssetID
                            FROM
                              tblProcesses
                            WHERE
                              tblProcesses.Caption Like '%DrAService.exe%')

Pull a list of machines where tblProcesses.Caption contains "DrAService.exe", then produce a list of machines that aren't in that list.

[andrew_exley]

Thank you... that seems to work as requested.

One additional tweak...?

Instead of looking for 'DrAService' in tblServicesUni.Name, if we wanted to look for 'DrAService.exe' in tblProcesses.Caption?

Continued thanks.

[RCorbeil]

Try this:

Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname As OS,
  tblAssets.OScode + '.' + tblAssets.BuildNumber As Build,
  tblAssets.Version As [OS Version],
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.Assettype
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where
  tblAssets.AssetID Not In (SELECT
                              tblServices.AssetID
                            FROM
                              tblServices
                              Inner Join tblServiceState On tblServiceState.StateID = tblServices.StateID
                              Inner Join tblServicesUni  On tblServicesUni.ServiceuniqueID = tblServices.ServiceuniqueID
                            WHERE
                              tblServicesUni.Name Like '%DrAService%')

Basically, you're saying "pull me a list of all machines that have the service (the SELECT in the WHERE), then produce a list of machines that aren't in that list".