→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
CyberCitizen
Honored Sweeper
Hey Guy's,

First off, thank you for the help I have a little urgent request that I am struggling with looking at some of the existing reports.

I need a report that shows the current BitLocker Status on C: Eg Off/On and also Antivirus Status. We are currently rolling out a new AV solution so we have Webroot and FortiClient. The report needs to list both with their status. I have the info on two reports, just trying to combine them I am struggling. Any help appericated.
1 ACCEPTED SOLUTION
I think this should do it so, a. Not Up To Date, b.Not Installed, c. Bitlocker Off :

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

View solution in original post

9 REPLIES 9
rrobledo
Engaged Sweeper
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
rader
Champion Sweeper III
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


Try just deleting the tblADComputers table in the report editor. That worked for me.
CyberCitizen
Honored Sweeper
Thank you, I would not have got there.
CyberCitizen
Honored Sweeper
Hi Andy,

Can I please ask one more question?

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


This is the report I am using. I now need to exclude items that match.

If Client has FortiClient Enabled And Bitlocker Status as On then hide.
I think this should do it so, a. Not Up To Date, b.Not Installed, c. Bitlocker Off :

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
CyberCitizen
Honored Sweeper
Andy,

Thank you that is exactly what I was after. Now I can pull it apart and understand it better.

Thank you for your help.
CyberCitizen
Honored Sweeper
Thank you, that is so close to what I need but I need it referencing the Antivirus Installed Details from the WMI lookups vs just software being installed.

Similar to this report and how its referencing it.

But this one is a little complete for me.

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
Ok , I have taken your report and added Bitlocker status, am I barking up the wrong tree :

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
Andy_Sismey
Champion Sweeper III
Hi, I have written this quickly and you will probably have to change the software names but give it a go :

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