List Computers With File/Registry "Version" Match

[lizaoreo]

I've set up a deployment of Dell BIOS settings. The BIOS utility creates a file when it runs and then in order to get Lansweeper to know if it's the latest "version" of the settings we're rolling out I had the deployment add a registry setting with a version. Since you can't list assets missing a registry variable, I look to see if the file is there, if it is, I look to see the "version" listed in the registry.

Unfortunately, the different version part works fine, that is, if they have the file and the registry entry is different. But if they're missing the file I'm getting duplicates of most of the computers because it pics up and displays the registry entries for IE and Firefox. Is there a way to have it ignore those fields in the second part of the query.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssetCustom.State,
tblFileVersions.Found
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Where (tblAssetCustom.State = 1 And tblFileVersions.Found = 1 And
tblFileVersions.FilePathfull = 'C:\program files\dell\dcct.log' And
tblRegistry.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\Dell' And
tblRegistry.Valuename = 'DCCTVer' And tblRegistry.Value <> '1.0' And
tblAssetCustom.Manufacturer Like '%Dell%' And tblADComputers.OU Like
'%OU=Workstations,OU=Computer Accounts,DC=Mariettaeye,DC=com%' And
(tblSystemEnclosure.ChassisTypes <> 8 Or tblSystemEnclosure.ChassisTypes <> 9
Or tblSystemEnclosure.ChassisTypes <> 10)) Or
(tblAssetCustom.State = 1 And tblFileVersions.Found = 0 And
tblFileVersions.FilePathfull = 'C:\program files\dell\dcct.log' And
tblAssetCustom.Manufacturer Like '%Dell%' And tblADComputers.OU Like
'%OU=Workstations,OU=Computer Accounts,DC=Mariettaeye,DC=com%' And
(tblSystemEnclosure.ChassisTypes <> 8 Or tblSystemEnclosure.ChassisTypes <> 9
Or tblSystemEnclosure.ChassisTypes <> 10))

[Susan_A · Accepted solution]

I've pasted a modified version of your report below.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssetCustom.State,
  tblFileVersions.Found,
  SubQuery.Value As RegistryValue
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
  Inner Join tblSystemEnclosure
    On tblAssets.AssetID = tblSystemEnclosure.AssetID
  Left Join (Select Top 1000000 tblRegistry.AssetID,
    tblRegistry.Value
  From tblRegistry
  Where tblRegistry.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\Dell' And
    tblRegistry.Valuename = 'DCCTVer') SubQuery On SubQuery.AssetID =
    tblAssets.AssetID
Where tblAssetCustom.State = 1 And (SubQuery.Value <> '1.0' Or
    SubQuery.Value = '' Or SubQuery.Value Is Null) And
  tblFileVersions.FilePathfull = 'C:\program files\dell\dcct.log' And
  tblAssetCustom.Manufacturer Like '%Dell%' And tblADComputers.OU Like
  '%OU=Workstations,OU=Computer Accounts,DC=Mariettaeye,DC=com%' And
  tblSystemEnclosure.ChassisTypes Not In (8, 9, 10)

I changed your chassis filters, as they didn't make sense with the OR operators. Filters like the ones below will still return machines with chassis type 8, 9 or 10, as the OR operator specifies that only one of the criteria needs to be met in order for the machine to be listed.

tblSystemEnclosure.ChassisTypes <> 8 Or tblSystemEnclosure.ChassisTypes <> 9
Or tblSystemEnclosure.ChassisTypes <> 10

Keep in mind as well that listing machines without a specific registry value is perfectly possible. I've pasted a small sample query for this below.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tsysOS.Image As icon
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssets.AssetID Not In (Select Top 1000000 tblRegistry.AssetID
  From tblRegistry
  Where tblRegistry.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\Dell' And
    tblRegistry.Valuename = 'DCCTVer') And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName