Notification

Icon
Error

Merging 2 reports - I am trying to merge a Registry and software report to show together

Posted: Thursday, July 22, 2021 10:02:59 PM(UTC)
Apaulcolypse

Apaulcolypse

Member Original PosterPosts: 33
1
Like
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:

Code:
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.


Code:

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.
Apaulcolypse
#1Apaulcolypse Member Original PosterPosts: 33  
posted: 8/12/2021 9:39:02 PM(UTC)
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:
Code:
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.
Code:
 Inner Join lansweeperdb.dbo.tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join lansweeperdb.dbo.tblADusers On tblADusers.Username =
      tblAssets.Username

Quote:
The objects "lansweeperdb.dbo.tsysOS" and "tsysOS" in the FROM clause have the same exposed names. Use correlation names to distinguish them.

Active Discussions

Lansweeper Scan User Exclusion / Define User OU for active scanning
by  Almada   Go to last post Go to first unread
Last post: Yesterday at 6:43:34 PM(UTC)
Lansweeper sqlServerId is changing
by  Ciro Bizelli  
Go to last post Go to first unread
Last post: Yesterday at 3:40:34 PM(UTC)
Lansweeper Slow Loading Lansweeper
by  Maikel Vanroelen   Go to last post Go to first unread
Last post: Yesterday at 9:25:31 AM(UTC)
Lansweeper Sync information
by  Jay-IT  
Go to last post Go to first unread
Last post: 9/16/2021 9:20:11 PM(UTC)
Lansweeper "Unknown" exclusion does not exclude Unknown assets
by  Almada   Go to last post Go to first unread
Last post: 9/16/2021 7:18:43 PM(UTC)
Lansweeper multiple scanning servers with granular permission
by  FrankSc  
Go to last post Go to first unread
Last post: 9/16/2021 6:29:54 PM(UTC)
Lansweeper Separate helpdesk websites?
by  JCochran   Go to last post Go to first unread
Last post: 9/15/2021 2:51:03 PM(UTC)
Lansweeper Remove Ads
by  FrankSc  
Go to last post Go to first unread
Last post: 9/15/2021 12:30:18 PM(UTC)