HELP - Add Registry Key Values to Asset Report

[polingkyle]

Good day. Let me preface this by saying that I know very little about SQL queries. That said, I'm trying to add some registry key values to an existing asset report. The registry keys are already being scanned and exist in tblRegistry. My asset report has multiple operating systems so not all of the assets will have the registry keys. I would like all assets to be on the report even if they don't have registry keys. There are 8 possible registry keys per asset. They are:

HKEY_LOCAL_MACHINE\SOFTWARE\Inventory\ApplicationName
HKEY_LOCAL_MACHINE\SOFTWARE\Inventory\ApplicationOwner
HKEY_LOCAL_MACHINE\SOFTWARE\Inventory\DeptName
HKEY_LOCAL_MACHINE\SOFTWARE\Inventory\Location
HKEY_LOCAL_MACHINE\SOFTWARE\Inventory\Monitor_1
HKEY_LOCAL_MACHINE\SOFTWARE\Inventory\Monitor_2
HKEY_LOCAL_MACHINE\SOFTWARE\Inventory\Monitor_3
HKEY_LOCAL_MACHINE\SOFTWARE\Inventory\Purpose

Here is the report I am running currently:

Select Top 1000000 tblAssets.AssetID,
 tsysAssetTypes.AssetTypeIcon10 As icon,
 tblstate.Statename As [Asset state],
 tsysAssetTypes.AssetTypename As [Asset type],
 Coalesce(tblOperatingsystem.Caption, tblLinuxSystem.OSRelease,
 tblMacOSInfo.SystemVersion, 'other/none') As OS,
 tblAssetCustom.Manufacturer,
 tblAssetCustom.Model,
 tblAssetCustom.Serialnumber,
 tblAssetCustom.Warrantydate As [Warranty expiration],
 tblAssets.AssetName,
 tblAssets.Lastseen,
 tblAssets.Domain,
 tblADComputers.OU,
 tblAssets.IPAddress,
 tblAssets.FQDN,
 tblADComputers.Description,
 tblAssets.Memory,
 tblAssets.NrProcessors,
 tblAssets.Processor
From tblAssets
 Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
 Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
 Left Join (Select tblAssetUserRelations.AssetID,
   tblAssetUserRelations.Username,
   tblAssetUserRelations.Userdomain
 From tblAssetUserRelations
 Where tblAssetUserRelations.Type = 1) tOwner On tblAssets.AssetID =
   tOwner.AssetID
 Inner Join tblstate On tblstate.State = tblAssetCustom.State
 Left Join tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.AssetID
 Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
 Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
 Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblAssetCustom.State = 1

Any help on this would be greatly appreciated. Thank you in advance.

Kyle Poling

[polingkyle]

That was exactly what I needed! Thank you so much!

[Daniel_B · Accepted solution]

If you would like to list all assets, even if they don't have data stored for this registry value, you need to use a subquery and make Left Join to it. If additionally you want to put all the values into one row of your report, you could use multiple subqueries, one for each value. Please find a modified report below which lists 3 of the values.

Select Top 1000000 tblAssets.AssetID,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblState.Statename As [Asset state],
  tsysAssetTypes.AssetTypename As [Asset type],
  Coalesce(tblOperatingsystem.Caption, tblLinuxSystem.OSRelease,
  tblMacOSInfo.SystemVersion, 'other/none') As OS,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.Warrantydate As [Warranty expiration],
  tblAssets.AssetName,
  tblAssets.Lastseen,
  tblAssets.Domain,
  tblADComputers.OU,
  tblAssets.IPAddress,
  tblAssets.FQDN,
  tblADComputers.Description,
  tblAssets.Memory,
  tblAssets.NrProcessors,
  tblAssets.Processor,
  tRegistry1.Value As ApplicationName,
  tRegistry2.Value As ApplicationOwner,
  tRegistry3.Value As DeptName
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join (Select tblAssetUserRelations.AssetID,
    tblAssetUserRelations.Username,
    tblAssetUserRelations.Userdomain
  From tblAssetUserRelations
  Where tblAssetUserRelations.Type = 1) tOwner On tblAssets.AssetID =
    tOwner.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.AssetID
  Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
  Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join (Select tblRegistry.AssetID,
    tblRegistry.Value
  From tblRegistry
  Where tblRegistry.Regkey Like 'HKEY_LOCAL_MACHINE\SOFTWARE\Inventory' And
    tblRegistry.Valuename = 'ApplicationName') tRegistry1 On tblAssets.AssetID =
    tRegistry1.AssetID
  Left Join (Select tblRegistry.AssetID,
    tblRegistry.Value
  From tblRegistry
  Where tblRegistry.Regkey Like 'HKEY_LOCAL_MACHINE\SOFTWARE\Inventory' And
    tblRegistry.Valuename = 'ApplicationOwner') tRegistry2
    On tblAssets.AssetID = tRegistry2.AssetID
  Left Join (Select tblRegistry.AssetID,
    tblRegistry.Value
  From tblRegistry
  Where tblRegistry.Regkey Like 'HKEY_LOCAL_MACHINE\SOFTWARE\Inventory' And
    tblRegistry.Valuename = 'DeptName') tRegistry3 On tblAssets.AssetID =
    tRegistry3.AssetID
Where tblAssetCustom.State = 1

[paulruvalcaba]

is there a way to add the Software and software version to this?
In addition to the registry keys, for my need I'm looking to also have the name of software and version of that software. I am able to have that as a separate report but would like them on the same one to easily distinguish the correct variables to use in the registry.

This is how I converted the report you placed.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tsysOS.OSname,
  tblADusers.Firstname,
  tblADusers.Lastname,
  tblAssets.Lastseen,
  tRegistry1.Value As JAVA_HOME,
  tRegistry2.Value As JAVA_TOOL_OPTIONS,
  tRegistry3.Value As PATH
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join (Select tblAssetUserRelations.AssetID,
      tblAssetUserRelations.Username,
      tblAssetUserRelations.Userdomain
    From tblAssetUserRelations
    Where tblAssetUserRelations.Type = 1) tOwner On tblAssets.AssetID =
      tOwner.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join (Select tblRegistry.AssetID,
      tblRegistry.Value
    From tblRegistry
    Where
      tblRegistry.Regkey Like
      'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment' And tblRegistry.Valuename = 'JAVA_HOME') tRegistry1 On tblAssets.AssetID = tRegistry1.AssetID
  Left Join (Select tblRegistry.AssetID,
      tblRegistry.Value
    From tblRegistry
    Where
      tblRegistry.Regkey Like
      'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment' And tblRegistry.Valuename = 'JAVA_TOOL_OPTIONS') tRegistry2 On tblAssets.AssetID = tRegistry2.AssetID
  Left Join (Select tblRegistry.AssetID,
      tblRegistry.Value
    From tblRegistry
    Where
      tblRegistry.Regkey Like
      'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Environment' And tblRegistry.Valuename = 'PATH') tRegistry3 On tblAssets.AssetID = tRegistry3.AssetID
  Inner Join lansweeperdb.dbo.tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join lansweeperdb.dbo.tblADusers On tblADusers.Username =
      tblAssets.Username
Where tsysAssetTypes.AssetTypename = 'Windows' And tblAssetCustom.State = 1

This is what I had for a report for the software that I want to merge into the report above:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tblADusers.Firstname,
  tblADusers.Lastname,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tsysOS.OSname As OS,
  Case
    When tblSoftware.softwareVersion >= '8.0.1810.13' Then 'Up to date'
    Else 'Out of date'
  End As [Patch Status],
  tblSoftware.softwareVersion As Version,
  tblSoftwareUni.softwareName As Software,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Case
    When tblSoftware.softwareVersion < '8.0.1810.13' Then '#ffadad'
    Else '#d4f4be'
  End As backgroundcolor
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 tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblSoftwareUni.softwareName Like '%Java%' And tblState.Statename =
  'Active'
Order By Version

Daniel.B wrote:
If you would like to list all assets, even if they don't have data stored for this registry value, you need to use a subquery and make Left Join to it. If additionally you want to put all the values into one row of your report, you could use multiple subqueries, one for each value. Please find a modified report below which lists 3 of the values.

Select Top 1000000 tblAssets.AssetID,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblState.Statename As [Asset state],
  tsysAssetTypes.AssetTypename As [Asset type],
  Coalesce(tblOperatingsystem.Caption, tblLinuxSystem.OSRelease,
  tblMacOSInfo.SystemVersion, 'other/none') As OS,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblAssetCustom.Warrantydate As [Warranty expiration],
  tblAssets.AssetName,
  tblAssets.Lastseen,
  tblAssets.Domain,
  tblADComputers.OU,
  tblAssets.IPAddress,
  tblAssets.FQDN,
  tblADComputers.Description,
  tblAssets.Memory,
  tblAssets.NrProcessors,
  tblAssets.Processor,
  tRegistry1.Value As ApplicationName,
  tRegistry2.Value As ApplicationOwner,
  tRegistry3.Value As DeptName
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join (Select tblAssetUserRelations.AssetID,
    tblAssetUserRelations.Username,
    tblAssetUserRelations.Userdomain
  From tblAssetUserRelations
  Where tblAssetUserRelations.Type = 1) tOwner On tblAssets.AssetID =
    tOwner.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.AssetID
  Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
  Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join (Select tblRegistry.AssetID,
    tblRegistry.Value
  From tblRegistry
  Where tblRegistry.Regkey Like 'HKEY_LOCAL_MACHINE\SOFTWARE\Inventory' And
    tblRegistry.Valuename = 'ApplicationName') tRegistry1 On tblAssets.AssetID =
    tRegistry1.AssetID
  Left Join (Select tblRegistry.AssetID,
    tblRegistry.Value
  From tblRegistry
  Where tblRegistry.Regkey Like 'HKEY_LOCAL_MACHINE\SOFTWARE\Inventory' And
    tblRegistry.Valuename = 'ApplicationOwner') tRegistry2
    On tblAssets.AssetID = tRegistry2.AssetID
  Left Join (Select tblRegistry.AssetID,
    tblRegistry.Value
  From tblRegistry
  Where tblRegistry.Regkey Like 'HKEY_LOCAL_MACHINE\SOFTWARE\Inventory' And
    tblRegistry.Valuename = 'DeptName') tRegistry3 On tblAssets.AssetID =
    tRegistry3.AssetID
Where tblAssetCustom.State = 1