Notification

Icon
Error

Assistance With AV & Bitlocker Report

Posted: Wednesday, June 3, 2020 7:03:21 AM(UTC)
CyberCitizen

CyberCitizen

Member Original PosterPosts: 363
0
Like
This issue has been solved! Click here to view the solution
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.
Andy.S
#1Andy.S Member Posts: 22  
posted: 6/3/2020 12:57:22 PM(UTC)
Hi, I have written this quickly and you will probably have to change the software names but give it a go :

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


CyberCitizen
#2CyberCitizen Member Original PosterPosts: 363  
posted: 6/4/2020 1:06:08 AM(UTC)
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.

Code:
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.S
#3Andy.S Member Posts: 22  
posted: 6/4/2020 10:30:29 AM(UTC)
Ok , I have taken your report and added Bitlocker status, am I barking up the wrong tree Angel :

Code:
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
CyberCitizen
#4CyberCitizen Member Original PosterPosts: 363  
posted: 6/4/2020 10:36:25 AM(UTC)
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
#5CyberCitizen Member Original PosterPosts: 363  
posted: 6/5/2020 2:57:10 AM(UTC)
Hi Andy,

Can I please ask one more question?

Code:
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.S
#6Andy.S Member Posts: 22  
posted: 6/5/2020 12:50:47 PM(UTC)
I think this should do it so, a. Not Up To Date, b.Not Installed, c. Bitlocker Off :

Code:
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
#7CyberCitizen Member Original PosterPosts: 363  
posted: 6/9/2020 7:04:20 AM(UTC)
Thank you, I would not have got there.

Active Discussions

Lansweeper Lost Configuration tab (Admin rights)
by  kspap   Go to last post Go to first unread
Last post: Today at 10:30:12 AM(UTC)
Lansweeper Deployment with different user rights
by  Jupiter_IT  
Go to last post Go to first unread
Last post: Today at 9:39:20 AM(UTC)
Lansweeper cisco fuji device not linking with connected devices
by  char   Go to last post Go to first unread
Last post: Yesterday at 9:12:07 AM(UTC)
Lansweeper Microsoft CVE-2020-1425
by  Richard_B  
Go to last post Go to first unread
Last post: 7/3/2020 4:29:41 PM(UTC)
Lansweeper Wake on Lan in VLANs
by  FrankSc   Go to last post Go to first unread
Last post: 7/3/2020 1:56:46 PM(UTC)
Lansweeper Ticket Closed = email to all helpdesk desk agents
by  Laurent Maene  
Go to last post Go to first unread
Last post: 7/3/2020 1:23:49 PM(UTC)
Lansweeper Helpdek Call Re-Opened
by  pryan67   Go to last post Go to first unread
Last post: 7/3/2020 1:12:17 PM(UTC)
Lansweeper Office 365 32bit vs 64bit?
by  brodiemac-too  
Go to last post Go to first unread
Last post: 7/2/2020 10:35:19 PM(UTC)