Identify systems with software not in add/remove programs

[SouthySuper]

We use OpenDNS Umbrella client (both x86 and x64 versions)

This report seems to be partly accurate for telling me which laptop/tablets do not have the software present, but upon auditing a handful I've found that several do have the file(s) present

I have file scanning enabled for both:
%programfiles%\OpenDNS\Umbrella Roaming Client\ERCService.exe
%programfiles(x86)%\OpenDNS\Umbrella Roaming Client\ERCService.exe


Select Top 10000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblFileVersions.Found,
tblFileVersions.FilePathfull,
tblFileVersions.FileVersion,
tblFileVersions.CompanyName,
tblFileVersions.Filesize,
tblFileVersions.Lastchanged As [Files last changed],
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
TsysChassisTypes.ChassisName,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tsysOS.OSname As OS,
tblAssets.SP As SP,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssets
Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Where tblFileVersions.FilePathfull Not Like '%ercservice.exe%' And
tblAssets.Lastseen <> '' And (TsysChassisTypes.Chassistype = 8 Or
TsysChassisTypes.Chassistype = 9 Or TsysChassisTypes.Chassistype = 10) And
tblAssets.Assettype = -1
Order By tblAssets.AssetUnique

Attached is my output:

Any Suggestions?

[SouthySuper]

Well that's what I needed. Thank you.

[Tom_P · Accepted solution]

Hi,

The following report (based on the SQL you supplied) will give back all active laptops that don't have the mentioned value in the registry.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.Assettype,
  tblAssets.AssetUnique,
  tblAssets.IPAddress,
  tblAssetCustom.Location,
  tblAssets.Description,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  TsysChassisTypes.ChassisName,
  TsysChassisTypes.Chassistype,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblSystemEnclosure on tblSystemEnclosure.AssetID = tblAssets.AssetID
  Inner Join TsysChassisTypes on TsysChassisTypes.Chassistype = tblSystemEnclosure.ChassisTypes
  Inner Join (Select Top 1000000 tblRegistry.AssetID, tblRegistry.Regkey, tblRegistry.Valuename, tblRegistry.Value, tblRegistry.Lastchanged
				From tblRegistry
				Where tblRegistry.Regkey Like '%Umbrella Roaming Client' And tblRegistry.Valuename = 'DefaultRecoveryFolderPath') as registry on registry.AssetID = tblAssets.AssetID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblState.Statename = 'Active' 
	and (TsysChassisTypes.Chassistype = 8 Or TsysChassisTypes.Chassistype = 9 Or TsysChassisTypes.Chassistype = 10 Or TsysChassisTypes.Chassistype = 11)
Order By tblAssets.AssetName, tblAssets.Domain

[SouthySuper]

I found a better way (not absolute), but less of a completely bad report I guess.

Using the registry entry instead.


Here is my report:

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssets.IPAddress,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
TsysChassisTypes.ChassisName,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.SP As SP,
tblAssets.Lastseen,
SubQuery1.Regkey,
tblAssetCustom.State,
SubQuery1.Valuename,
TsysChassisTypes.Chassistype,
tblAssets.Assettype,
tblAssets.AssetUnique
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Left Join (Select Top 1000000 tblRegistry.AssetID,
tblRegistry.Regkey,
tblRegistry.Valuename,
tblRegistry.Value,
tblRegistry.Lastchanged
From tblRegistry
Where tblRegistry.Regkey Like '%Umbrella Roaming Client' And
tblRegistry.Valuename = 'DefaultRecoveryFolderPath') SubQuery1
On SubQuery1.AssetID = tblAssets.AssetID
Where tblAssets.Lastseen <> '' And tblAssetCustom.State = 1 And
(TsysChassisTypes.Chassistype = 8 Or TsysChassisTypes.Chassistype = 9 Or
TsysChassisTypes.Chassistype = 10 Or TsysChassisTypes.Chassistype = 11) And
tblAssets.Assettype = -1 And TsysWaittime.CFGname = 'registry'
Order By tblAssets.AssetUnique