Assistance With AV & Bitlocker Report

[CyberCitizen]

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.

[rrobledo]

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]

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]

Thank you, I would not have got there.

[CyberCitizen]

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.

[Andy_Sismey · 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

[CyberCitizen]

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]

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

[Andy_Sismey]

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]

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