06-03-2020 08:03 AM
Solved! Go to Solution.
06-05-2020 01:50 PM
Select Top 1000000 unioned.assetid,
tblAssets.AssetName,
tblAssets.Username,
tsysOS.Image As icon,
unioned.software,
unioned.Enabled,
Case
When tblEncryptableVolume.ProtectionStatus = 1 Then 'On'
Else 'Off'
End As Bitlocker,
tblAssets.Lastseen,
tblADComputers.OU
From ((Select a.assetid As assetid,
a.software As software,
a.version As version,
'software comparison' As RetrievedFrom,
'' As Enabled,
'' As Uptodate
From (Select tblSoftware.AssetID As assetid,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID =
tblSoftwareUni.SoftID
Inner Join tsysantivirus On tblSoftwareUni.softwareName Like
tsysantivirus.Software) a)
Union
(Select tblAntivirus.AssetID As assetid,
tblAntivirus.DisplayName As software,
Null As version,
'WMI' As RetrievedFrom,
Case
When tblAntivirus.onAccessScanningEnabled = 1 Then 'Yes'
Else 'No'
End As Enabled,
Case
When tblAntivirus.productUpToDate = 1 Then 'Yes'
Else 'No'
End As Uptodate
From tblAntivirus)) unioned
Inner Join tblAssetCustom On unioned.assetid = tblAssetCustom.AssetID
Inner Join tblAssets On tblAssets.AssetID = unioned.assetid
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblEncryptableVolume On
tblAssets.AssetID = tblEncryptableVolume.AssetId
Where unioned.Enabled Not Like '' And ((unioned.Enabled Not Like '%Yes%' And
unioned.Uptodate Not Like '%Yes%') Or
tblEncryptableVolume.ProtectionStatus = 0) And
tblADComputers.OU Not Like '%Disabled%' And tblComputersystem.Domainrole <= 1
And tblAssetCustom.State = 1
Order By tblAssets.AssetName
08-30-2021 09:49 PM
11-12-2021 01:03 AM
rrobledo wrote:
Hello,
This query omits assets that are not on AD. Could someone provide a solution to editing this query? We install the LS agent on non-AD computers and i need to be sure they are included in the report.
-Richard
06-09-2020 08:04 AM
06-05-2020 03:57 AM
Select Top 1000000 unioned.assetid,
tblAssets.AssetName,
tblAssets.Username,
tsysOS.Image As icon,
unioned.software,
unioned.Enabled,
Case
When tblEncryptableVolume.ProtectionStatus = 1 Then 'On'
Else 'Off'
End As Bitlocker,
tblAssets.Lastseen,
tblADComputers.OU
From ((Select a.assetid As assetid,
a.software As software,
a.version As version,
'software comparison' As RetrievedFrom,
'' As Enabled,
'' As Uptodate
From (Select tblSoftware.AssetID As assetid,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID =
tblSoftwareUni.SoftID
Inner Join tsysantivirus On tblSoftwareUni.softwareName Like
tsysantivirus.Software) a)
Union
(Select tblAntivirus.AssetID As assetid,
tblAntivirus.DisplayName As software,
Null As version,
'WMI' As RetrievedFrom,
Case
When tblAntivirus.onAccessScanningEnabled = 1 Then 'Yes'
Else 'No'
End As Enabled,
Case
When tblAntivirus.productUpToDate = 1 Then 'Yes'
Else 'No'
End As Uptodate
From tblAntivirus)) unioned
Inner Join tblAssetCustom On unioned.assetid = tblAssetCustom.AssetID
Inner Join tblAssets On tblAssets.AssetID = unioned.assetid
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblEncryptableVolume On
tblAssets.AssetID = tblEncryptableVolume.AssetId
Where unioned.Enabled Not Like '' And tblADComputers.OU Not Like '%Disabled%'
And tblComputersystem.Domainrole <= 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName
06-05-2020 01:50 PM
Select Top 1000000 unioned.assetid,
tblAssets.AssetName,
tblAssets.Username,
tsysOS.Image As icon,
unioned.software,
unioned.Enabled,
Case
When tblEncryptableVolume.ProtectionStatus = 1 Then 'On'
Else 'Off'
End As Bitlocker,
tblAssets.Lastseen,
tblADComputers.OU
From ((Select a.assetid As assetid,
a.software As software,
a.version As version,
'software comparison' As RetrievedFrom,
'' As Enabled,
'' As Uptodate
From (Select tblSoftware.AssetID As assetid,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID =
tblSoftwareUni.SoftID
Inner Join tsysantivirus On tblSoftwareUni.softwareName Like
tsysantivirus.Software) a)
Union
(Select tblAntivirus.AssetID As assetid,
tblAntivirus.DisplayName As software,
Null As version,
'WMI' As RetrievedFrom,
Case
When tblAntivirus.onAccessScanningEnabled = 1 Then 'Yes'
Else 'No'
End As Enabled,
Case
When tblAntivirus.productUpToDate = 1 Then 'Yes'
Else 'No'
End As Uptodate
From tblAntivirus)) unioned
Inner Join tblAssetCustom On unioned.assetid = tblAssetCustom.AssetID
Inner Join tblAssets On tblAssets.AssetID = unioned.assetid
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblEncryptableVolume On
tblAssets.AssetID = tblEncryptableVolume.AssetId
Where unioned.Enabled Not Like '' And ((unioned.Enabled Not Like '%Yes%' And
unioned.Uptodate Not Like '%Yes%') Or
tblEncryptableVolume.ProtectionStatus = 0) And
tblADComputers.OU Not Like '%Disabled%' And tblComputersystem.Domainrole <= 1
And tblAssetCustom.State = 1
Order By tblAssets.AssetName
06-04-2020 11:36 AM
06-04-2020 02:06 AM
Select Top 1000000 unioned.assetid,
tblAssets.AssetName,
tsysOS.Image As icon,
unioned.software,
unioned.Enabled,
unioned.Uptodate,
tblADComputers.OU
From ((Select a.assetid As assetid,
a.software As software,
a.version As version,
'software comparison' As RetrievedFrom,
'' As Enabled,
'' As Uptodate
From (Select tblSoftware.AssetID As assetid,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID =
tblSoftwareUni.SoftID
Inner Join tsysantivirus On tblSoftwareUni.softwareName Like
tsysantivirus.Software) a)
Union
(Select tblAntivirus.AssetID As assetid,
tblAntivirus.DisplayName As software,
Null As version,
'WMI' As RetrievedFrom,
Case
When tblAntivirus.onAccessScanningEnabled = 1 Then 'Yes'
Else 'No'
End As Enabled,
Case
When tblAntivirus.productUpToDate = 1 Then 'Yes'
Else 'No'
End As Uptodate
From tblAntivirus)) unioned
Inner Join tblAssetCustom On unioned.assetid = tblAssetCustom.AssetID
Inner Join tblAssets On tblAssets.AssetID = unioned.assetid
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblADComputers.OU Not Like '%Disabled%' And tblComputersystem.Domainrole
<= 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName
06-04-2020 11:30 AM
Select Top 1000000 unioned.assetid,
tblAssets.AssetName,
tsysOS.Image As icon,
unioned.software,
unioned.Enabled,
unioned.Uptodate,
tblADComputers.OU,
Case
When tblEncryptableVolume.ProtectionStatus = 1 Then 'Encrypted'
Else 'Not Encrypted'
End As Bitlocker
From ((Select a.assetid As assetid,
a.software As software,
a.version As version,
'software comparison' As RetrievedFrom,
'' As Enabled,
'' As Uptodate
From (Select tblSoftware.AssetID As assetid,
tblSoftwareUni.softwareName As software,
tblSoftware.softwareVersion As version
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID =
tblSoftwareUni.SoftID
Inner Join tsysantivirus On tblSoftwareUni.softwareName Like
tsysantivirus.Software) a)
Union
(Select tblAntivirus.AssetID As assetid,
tblAntivirus.DisplayName As software,
Null As version,
'WMI' As RetrievedFrom,
Case
When tblAntivirus.onAccessScanningEnabled = 1 Then 'Yes'
Else 'No'
End As Enabled,
Case
When tblAntivirus.productUpToDate = 1 Then 'Yes'
Else 'No'
End As Uptodate
From tblAntivirus)) unioned
Inner Join tblAssetCustom On unioned.assetid = tblAssetCustom.AssetID
Inner Join tblAssets On tblAssets.AssetID = unioned.assetid
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblEncryptableVolume On
tblAssets.AssetID = tblEncryptableVolume.AssetId
Where tblADComputers.OU Not Like '%Disabled%' And tblComputersystem.Domainrole
<= 1 And tblAssetCustom.State = 1
Order By tblAssets.AssetName
06-03-2020 01:57 PM
Select Distinct Top 1000000 tblAssets.AssetName,
tsysOS.OSname,
Case
When soft01.AssetID Is Null Then 'NO'
Else 'YES'
End As FortiClient,
Case
When soft02.AssetID Is Null Then 'NO'
Else 'YES'
End As WebRoot,
Case
When tblEncryptableVolume.ProtectionStatus = 1 Then 'Encrypted'
Else 'Not Encrypted'
End As Bitlocker,
tblAssets.Firstseen,
tblAssets.Lastseen,
Case
When soft01.AssetID Is Null Or soft02.AssetID Is Null Or
tblEncryptableVolume.ProtectionStatus = 0 Then '#ffcccc'
Else '#BCED91'
End As backgroundcolor,
tblADComputers.OU,
tblAssets.AssetID,
tblEncryptableVolume.DriveLetter
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join (Select tblSoftware.AssetID
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%FortiClient%') As soft01 On
soft01.AssetID = tblAssets.AssetID
Left Join (Select tblSoftware.AssetID
From tblSoftware
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%Webroot%') As soft02 On
soft02.AssetID = tblAssets.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblEncryptableVolume On
tblAssets.AssetID = tblEncryptableVolume.AssetId
Where tblAssetCustom.State = 1
Order By WebRoot,
tblAssets.Firstseen Desc
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now