cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

HELP - Add Registry Key Values to Asset Report

polingkyle
Engaged Sweeper II
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
1 ACCEPTED SOLUTION

Daniel_B
Lansweeper Former Employee
Lansweeper Former Employee
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

View solution in original post

3 REPLIES 3

polingkyle
Engaged Sweeper II
That was exactly what I needed! Thank you so much!

Daniel_B
Lansweeper Former Employee
Lansweeper Former Employee
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

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