→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
‎07-22-2021 11:02 PM
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
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
‎08-12-2021 10:39 PM
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
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]
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now