→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
paulruvalcaba
Engaged Sweeper III
I'm still fairly new to SQL. Through lots of trial and error I was able to come up with the following 2 reports. I'm trying to merge them but am having difficulty.


This is the Registry report I want to combine with a Software report:

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 the software report that I want to merge into the report above:
NOTE: I do not need the case that adds color to be included if its not possible, but it would be nice.



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


Any and all tips and help would be appreciated.
1 REPLY 1
paulruvalcaba
Engaged Sweeper III
SO I was able to SOMEWHAT figure it out. But I am having issues with a few duplicate workstations. I am not sure how to merge/join them

This was what I came up with:
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,
tRegistry1.Value As JAVA_HOME,
tRegistry2.Value As JAVA_HOME_User,
tRegistry3.Value As JAVA_TOOL_OPTIONS,
tRegistry4.Value As PATH

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
Left Join (Select tblAssetUserRelations.AssetID,
tblAssetUserRelations.Username,
tblAssetUserRelations.Userdomain
From tblAssetUserRelations
Where tblAssetUserRelations.Type = 1) tOwner On tblAssets.AssetID =
tOwner.AssetID
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

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_CURRENT_USER\Environment' And
tblRegistry.Valuename = 'JAVA_HOME') 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 = 'JAVA_TOOL_OPTIONS') tRegistry3 On tblAssets.AssetID = tRegistry3.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') tRegistry4 On tblAssets.AssetID = tRegistry4.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 and tblSoftwareUni.softwareName Like '%Java%' And tblState.Statename =
'Active'
Order By Version




I took these 2 lines out from the bottom because I was getting an error and didnt know how to fix it.
 Inner Join lansweeperdb.dbo.tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join lansweeperdb.dbo.tblADusers On tblADusers.Username =
tblAssets.Username

[size=6]The objects "lansweeperdb.dbo.tsysOS" and "tsysOS" in the FROM clause have the same exposed names. Use correlation names to distinguish them.[/size]

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now