Report Client with regkey xy and not rekey xx

[Jln_S]

Hello,

I need some help.
I would like to create a report which shows me every client who has the regkey 'HKEY_LOCAL_MACHINE\SOFTWARE\MATRIX42' and not the regkey 'HKEY_LOCAL_MACHINE\SOFTWARE\MATRIX42\DHCPOptions'

I have created some scipts but nothing works for me.
Here is my start, I can check for one key which isn't set but I don't know how to implement the 2nd key which is needed.

Select Top 100 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried
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 100 tblRegistry.AssetID
  From tblRegistry
  Where tblRegistry.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\MATRIX42\DHCPOptions')
  And tblAssetCustom.State = 1
Order By tblAssets.AssetName

The regkeys are added to the Registry Scanning.

Best regards
Julien

[MikeMc · Accepted solution]

This query should work if your registry scanning settings are looking for the LastDomain and Template values.

Select Top 100000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssets.AssetID In (Select Top 100000 tblRegistry.AssetID
  From tblRegistry
  Where tblRegistry.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\MATRIX42\EmpInventory'
    And tblRegistry.Valuename = 'LastDomain') And
  tblAssets.AssetID Not In (Select Top 100000 tblRegistry.AssetID
  From tblRegistry
  Where tblRegistry.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\MATRIX42\DHCPOptions'
    And tblRegistry.Valuename = 'Template') And tblAssetCustom.State = 1
Order By tblAssets.AssetName

[Jln_S]

Hey,

The MATRIX42 Key doesn't have any values.

but we can scan like this: (sorry I don't know any better way to describe)

Every client has the regkey 'HKEY_LOCAL_MACHINE\SOFTWARE\MATRIX42\EmpInventory' with the REG_SZ 'LastDomain' Value: 'XX.dom' and not the regkey 'HKEY_LOCAL_MACHINE\SOFTWARE\MATRIX42\DHCPOptions' with the REG_SZ 'Template' Value: 'Options get from template ...'

After the template ... there are the date and other variables.

Thank you.

Best regards
Julien

[MikeMc]

I read up some more on registry scanning (http://www.lansweeper.com/kb/18/report-based-on-registry-keys.html) and the problem appears to be you are trying to scan based off of the existance of registry keys. The only way Lansweeper will up pick up the keys is if the default value has data.

Anyway, I think it would be best to change your registry scanning settings to include a value under MATRIX42 and MATRIX42\DHCPOptions. I can update my original query once you provide these values.

[Jln_S]

Hello,

thank you but this report doesn't work 😞

Any other ideas?

Best regards
Julien

[MikeMc]

Something like this?

Select Top 100 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried
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 100 tblRegistry.AssetID
  From tblRegistry
  Where tblRegistry.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\MATRIX42\DHCPOptions')
  And tblAssets.AssetID In (Select Top 100 tblRegistry.AssetID
  From tblRegistry
  Where tblRegistry.Regkey = 'HKEY_LOCAL_MACHINE\SOFTWARE\MATRIX42')
  And tblAssetCustom.State = 1
Order By tblAssets.AssetName