Symantec Endpoint Protection Dashboard

How do I import and configure this dashboard?

1. Make sure you’ve upgraded to SquaredUp 5.3 or above.

Already a SquaredUp customer?
Get the latest version

New to SquaredUp?
Try it for free

2. Download and deploy the free PowerShell module DBATools (https://dbatools.io/).

The module is used to send the queries to the SEP MSSQL database and return the PowerShell objects.

3. Create a scheduled task to run the Powershell script below (e.g. hourly).

The script runs SQL queries and stores the results in CSV files that are retrieved by the dashboard.

• Show code

  Copy code to clipboard

  					Import-Module dbatools
  
  [string]$userName = 'XXX'
  [string]$userPassword = 'vU'
  
  [securestring]$secStringPassword = ConvertTo-SecureString$userPassword -AsPlainText -Force
  [pscredential]$credObject = New-Object System.Management.Automation.PSCredential ($userName, $secStringPassword)
  
  $checkInQry = @"
  Use SEP12;
  SELECT SEM_COMPUTER.COMPUTER_NAME AS 'Computer name',
  PATTERN.Version AS 'Virus definition used',
  dateadd(second, SEM_AGENT.LAST_UPDATE_TIME/1000, '1970-01-01') AS 'Last check-in (GMT)', SEM_AGENT.AGENT_VERSION
  FROM SEM_COMPUTER
  INNER JOIN SEM_AGENT ON SEM_AGENT.COMPUTER_ID=SEM_COMPUTER.COMPUTER_ID
  INNER JOIN SEM_CONTENT ON SEM_CONTENT.AGENT_ID=SEM_AGENT.AGENT_ID
  INNER JOIN PATTERN ON PATTERN.PATTERN_IDX=SEM_AGENT.PATTERN_IDX
  INNER JOIN (
  SELECT SEM_COMPUTER.COMPUTER_NAME AS 'TempHostName',
  MAX(SEM_AGENT.LAST_UPDATE_TIME) AS 'TempMax'
  FROM SEM_COMPUTER
  INNER JOIN SEM_AGENT ON SEM_AGENT.COMPUTER_ID=SEM_COMPUTER.COMPUTER_ID
  GROUP BY COMPUTER_NAME)
  TestTable ON TestTable.TempHostName=SEM_COMPUTER.COMPUTER_NAME
  AND TestTable.TempMax=SEM_AGENT.LAST_UPDATE_TIME
  WHERE PATTERN.PATTERN_TYPE='VIRUS_DEFS'
  AND PATTERN.DELETED='0'
  AND SEM_CONTENT.DELETED='0'
  AND SEM_AGENT.DELETED='0'
  AND SEM_COMPUTER.DELETED='0'
  GROUP BY SEM_COMPUTER.COMPUTER_NAME, SEM_AGENT.LAST_UPDATE_TIME, PATTERN.Version, SEM_AGENT.AGENT_VERSION
  ORDER BY SEM_COMPUTER.COMPUTER_NAME ASC, SEM_AGENT.LAST_UPDATE_TIME DESC;
  "@
  
  $checkIn = Invoke-DbaQuery -SqlInstance SQLServer\InstanceName -SqlCredential $credObject -Query $checkInQry
   
  $rsl = $checkIn | Select-Object -Property 'Computer name','Virus definition used','Last check-in (GMT)',  @{Name = 'VirusDefDate'; Expression = { [datetime]::ParseExact(($_.'Virus definition used'.Substring(0,10)),"yyyy-MM-dd",$null) }} 
          
  
  $green = ($rsl | Where-Object {($_.VirusDefDate -lt (Get-Date)) -and ($_.VirusDefDate -gt (Get-Date).AddDays(-3)) } ).count
  $yellow = ($rsl | Where-Object {($_.VirusDefDate -lt (Get-Date).AddDays(-3)) -and ($_.VirusDefDate -gt (Get-Date).AddDays(-7)) } ).count
  $red = ($rsl | Where-Object {($_.VirusDefDate -lt (Get-Date).AddDays(-7))  } ).count
   
  #AV Definitions
  $rslArr = New-Object -TypeName System.Collections.ArrayList
  $rslArr.Add(@{ state = "healthy"; id = "1"; name = "Within 3 days"; metric = $green })      
  $rslArr.Add(@{ state = "warning"; id = "2"; name = "Between 3 days and 7 days"; metric = $yellow }) 
  $rslArr.Add(@{ state = "critical"; id = "3"; name = "Older than 7 days"; metric = $red })
  $rslArr | ConvertTo-Json | Out-File C:\Temp\SEP-SquaredUp-AvDevintions.json -Force
  
  
  #Last Server Checkin   
  $rslArr = New-Object -TypeName System.Collections.ArrayList
  $lastServerCheckin = $checkIn | Select-Object -Property 'Computer name','Virus definition used','Last check-in (GMT)', `
              @{Name = 'LastCheckinDay'; Expression = {($_.'Last check-in (GMT)')| Get-Date -Format "yyyy-MM"}}`
              | Group-Object -Property LastCheckinDay
      
              $lastServerCheckin | Where-Object {$_.Name } | ForEach-Object {              
                  $obj = [pscustomobject]@{
                      Count = $_.Count -as [double]
                      Name = $_.Name -as [string]
                      Computer = ($_.Group.'Computer name'  | Select-object -first 1) -join ','
                  }
                  $rslArr.Add($obj)
              }        
  
   $rslArr | Sort-Object -Property Count |  Select-Object -Last 5 | Export-Csv -LiteralPath C:\Temp\SEP-SquaredUp-LastServerCheckin.csv -NoTypeInformation -Force
  
  
  #Last Full Scan
   $checkInQry = @"
  Use SEP12;
  SELECT DISTINCT
      "SEM_CLIENT"."COMPUTER_NAME" "Computer Name"
    , "SEM_AGENT"."AGENT_VERSION" "SEP Version"
    ,    "SEM_COMPUTER"."OPERATION_SYSTEM" "Operation System"
    , "PATTERN"."VERSION" "AV Revision"
    , dateadd(s,convert(bigint,"SEM_AGENT"."CREATION_TIME")/1000,'01-01-1970 00:00:00') CREATION_DTTM
    , dateadd(s,convert(bigint,"SEM_AGENT"."LAST_UPDATE_TIME")/1000,'01-01-1970 00:00:00') "Last Update Time"
    , dateadd(s, convert(bigint,LAST_SCAN_TIME)/1000, '01-01-1970 00:00:00')"Last Scan Time"
    , "SEM_CLIENT"."USER_NAME" "User Name"
    , "IP_ADDR1_TEXT" "IP Address"
    , "IDENTITY_MAP"."NAME" "Group Name"
    , "SEM_AGENT"."DELETED" "Marked for deletion"
  FROM (((("SEM_AGENT" "SEM_AGENT" INNER JOIN "SEM_CLIENT" "SEM_CLIENT"
    ON (("SEM_AGENT"."COMPUTER_ID"="SEM_CLIENT"."COMPUTER_ID")
    AND ("SEM_AGENT"."DOMAIN_ID"="SEM_CLIENT"."DOMAIN_ID"))
    AND ("SEM_AGENT"."GROUP_ID"="SEM_CLIENT"."GROUP_ID")) INNER JOIN "SEM_COMPUTER" "SEM_COMPUTER"
    ON (("SEM_AGENT"."COMPUTER_ID"="SEM_COMPUTER"."COMPUTER_ID")
    AND ("SEM_AGENT"."DOMAIN_ID"="SEM_COMPUTER"."DOMAIN_ID"))
    AND ("SEM_AGENT"."DELETED"="SEM_COMPUTER"."DELETED")) INNER JOIN "PATTERN" "PATTERN"
    ON "SEM_AGENT"."PATTERN_IDX"="PATTERN"."PATTERN_IDX") INNER JOIN "IDENTITY_MAP" "IDENTITY_MAP"
    ON "SEM_CLIENT"."GROUP_ID"="IDENTITY_MAP"."ID") INNER JOIN "V_SEM_COMPUTER" "V_SEM_COMPUTER"
    ON "SEM_COMPUTER"."COMPUTER_ID"="V_SEM_COMPUTER"."COMPUTER_ID"
    AND "SEM_AGENT"."DELETED"=0
  ORDER BY "Computer Name"
  "@
  
  $lastScan = Invoke-DbaQuery -SqlInstance SQLServer\InstanceName -SqlCredential $credObject -Query $checkInQry
  $lastScanRsl = $lastScan | Select-Object -Property 'Computer Name',  @{Name = 'LastScanDate'; Expression = {($_.'Last Scan Time')| Get-Date -Format "yyyy-MM"}} | Where-Object {$_.LastScanDate -ne'1970-01'} | Group-Object LastScanDate 
  
  $rslArr = New-Object -TypeName System.Collections.ArrayList
  $lastScanRsl | Where-Object {$_.Name } | ForEach-Object {              
                  $obj = [pscustomobject]@{
                      Count = $_.Count -as [double]
                      Name = $_.Name -as [string]
                      Computer = ($_.Group.'Computer name'  | Select-object -first 1) -join ','
                  }
                  $rslArr.Add($obj)
              }        
  
   $rslArr | Sort-Object -Property Count |  Select-Object -Last 3 | ConvertTo-Json | Out-File C:\Temp\SEP-SquaredUp-LastFullScan.json -Force
  
  
  
  # App Version
   $checkInQry = @"
  Use SEP12;
  SELECT SEM_COMPUTER.COMPUTER_NAME AS 'Computer name',
  PATTERN.Version AS 'Virus definition used',
  dateadd(second, SEM_AGENT.LAST_UPDATE_TIME/1000, '1970-01-01') AS 'Last check-in (GMT)', SEM_AGENT.AGENT_VERSION
  FROM SEM_COMPUTER
  INNER JOIN SEM_AGENT ON SEM_AGENT.COMPUTER_ID=SEM_COMPUTER.COMPUTER_ID
  INNER JOIN SEM_CONTENT ON SEM_CONTENT.AGENT_ID=SEM_AGENT.AGENT_ID
  INNER JOIN PATTERN ON PATTERN.PATTERN_IDX=SEM_AGENT.PATTERN_IDX
  INNER JOIN (
  SELECT SEM_COMPUTER.COMPUTER_NAME AS 'TempHostName',
  MAX(SEM_AGENT.LAST_UPDATE_TIME) AS 'TempMax'
  FROM SEM_COMPUTER
  INNER JOIN SEM_AGENT ON SEM_AGENT.COMPUTER_ID=SEM_COMPUTER.COMPUTER_ID
  GROUP BY COMPUTER_NAME)
  TestTable ON TestTable.TempHostName=SEM_COMPUTER.COMPUTER_NAME
  AND TestTable.TempMax=SEM_AGENT.LAST_UPDATE_TIME
  WHERE PATTERN.PATTERN_TYPE='VIRUS_DEFS'
  AND PATTERN.DELETED='0'
  AND SEM_CONTENT.DELETED='0'
  AND SEM_AGENT.DELETED='0'
  AND SEM_COMPUTER.DELETED='0'
  GROUP BY SEM_COMPUTER.COMPUTER_NAME, SEM_AGENT.LAST_UPDATE_TIME, PATTERN.Version, SEM_AGENT.AGENT_VERSION
  ORDER BY SEM_COMPUTER.COMPUTER_NAME ASC, SEM_AGENT.LAST_UPDATE_TIME DESC;
  "@
  
  $checkIn = Invoke-DbaQuery -SqlInstance SQLServer\InstanceName -SqlCredential $credObject -Query $checkInQry
  $rslt = $checkIn | Group-Object -Property AGENT_VERSION -NoElement
  $rslt | Select-Object -First 5 -Property Count, Name | Sort-Object -Property Name -Descending  | Export-Csv -LiteralPath C:\Temp\SEP-SquaredUp-AppVersions.csv -NoTypeInformation -Force
  
  
  #Malware - Top 5 & Computer infections Top 5
  
  $checkInQry = @"
  Use SEP12;
  SELECT ALERTS.ALERTDATETIME,  ALERTS.ALERTINSERTTIME, ALERTS.ALERTENDDATETIME, USER_NAME, V_SEM_COMPUTER.COMPUTER_NAME, V_SEM_COMPUTER.IP_ADDR1_TEXT, VIRUS.VIRUSNAME, SOURCE, NOOFVIRUSES, FILEPATH, DESCRIPTION, A1.Actualaction,
  A2.Actualaction as Requestedaction, A3.Actualaction as Secondaryaction, SOURCE_COMPUTER_NAME, SOURCE_COMPUTER_IP
  FROM ALERTS
  INNER JOIN V_SEM_COMPUTER ON COMPUTER_IDX = COMPUTER_ID
  INNER JOIN VIRUS ON ALERTS.VIRUSNAME_IDX = VIRUS.VIRUSNAME_IDX
  INNER JOIN Actualaction A1 on ALERTS.Actualaction_idx = A1.Actualaction_idx
  INNER JOIN Actualaction A2 on ALERTS.Requestedaction_idx = A2.Actualaction_idx 
  INNER JOIN Actualaction A3 on ALERTS.Secondaryaction_Idx = A3.Actualaction_idx
  WHERE ALERTDATETIME >= DATEADD(day, -7, CURRENT_TIMESTAMP)
  order by ALERTDATETIME
  "@
  
  $sepAlerts = Invoke-DbaQuery -SqlInstance SQLServer\InstanceName -SqlCredential $credObject -Query $checkInQry
  $sepAlertsRsl = $sepAlerts | Where-Object {$_.VIRUSNAME -ne'WS.Reputation.1'} | Where-Object {$_.ALERTDATETIME -ge (Get-date).AddDays(-7) } | Group-Object -Property VIRUSNAME -NoElement | Sort-Object -Property Count -Descending | Select-Object -First 5 
  $sepAlertsRsl | ConvertTo-Json | Out-File C:\Temp\SEP-SquaredUp-MalWare-Top5.json -Force
  
  $sepAlertsRsl = $sepAlerts | Where-Object {$_.VIRUSNAME -ne'WS.Reputation.1'} | Where-Object {$_.ALERTDATETIME -ge (Get-date).AddDays(-7) } | Group-Object -Property COMPUTER_NAME -NoElement | Sort-Object -Property Count -Descending | Select-Object -First 5 
  $sepAlertsRsl | ConvertTo-Json | Out-File C:\Temp\SEP-SquaredUp-MalWareComputer-Top5.json -Force
  
  
  
  
  $checkInQry = @"
  Use SEP12;
  SELECT ALERTS.ALERTDATETIME,  ALERTS.ALERTINSERTTIME, ALERTS.ALERTENDDATETIME, USER_NAME, V_SEM_COMPUTER.COMPUTER_NAME, V_SEM_COMPUTER.IP_ADDR1_TEXT, VIRUS.VIRUSNAME, SOURCE, NOOFVIRUSES, FILEPATH, DESCRIPTION, A1.Actualaction,
  A2.Actualaction as Requestedaction, A3.Actualaction as Secondaryaction, SOURCE_COMPUTER_NAME, SOURCE_COMPUTER_IP
  FROM ALERTS
  INNER JOIN V_SEM_COMPUTER ON COMPUTER_IDX = COMPUTER_ID
  INNER JOIN VIRUS ON ALERTS.VIRUSNAME_IDX = VIRUS.VIRUSNAME_IDX
  INNER JOIN Actualaction A1 on ALERTS.Actualaction_idx = A1.Actualaction_idx
  INNER JOIN Actualaction A2 on ALERTS.Requestedaction_idx = A2.Actualaction_idx 
  INNER JOIN Actualaction A3 on ALERTS.Secondaryaction_Idx = A3.Actualaction_idx
  WHERE ALERTDATETIME >= DATEADD(day, -30, CURRENT_TIMESTAMP)
  order by ALERTDATETIME
  "@
  
  $sepAlerts = Invoke-DbaQuery -SqlInstance SQLServer\InstanceName -SqlCredential $credObject -Query $checkInQry
  $alertHistory = $sepAlerts | Where-Object {$_.VIRUSNAME -ne'WS.Reputation.1'} | Select-Object COMPUTER_NAME, @{Name = 'ALERTDATETIMED'; Expression = {($_.'ALERTDATETIME')| Get-Date -Format "yyyy-MM-dd"}}  | Group-Object ALERTDATETIMED
  $alertBySiteCode = $sepAlerts | Where-Object {$_.VIRUSNAME -ne'WS.Reputation.1'} | Select-Object COMPUTER_NAME, @{Name = 'ALERTDATETIMED'; Expression = {($_.'ALERTDATETIME')| Get-Date -Format "yyyy-MM-dd"}}, @{Name = 'SiteCode'; Expression =  {($_.'COMPUTER_NAME').Substring(0,5)}}  | Group-Object SiteCode  | Sort-Object -Property Count -Descending
  
  $rslArr = New-Object -TypeName System.Collections.ArrayList
  $alertHistory | Where-Object {$_.Name } | ForEach-Object {              
                  $obj = [pscustomobject]@{
                      Count = $_.Count -as [Int]
                      Name = $_.Name -as [DateTime]
                      Computer = ($_.Group.'COMPUTER_NAME'  | Select-object -first 1) -join ','
                  }
                  $rslArr.Add($obj)
              }        
  
  $rslArr | Export-csv -LiteralPath C:\Temp\SEP-SquaredUp-AlertHistory.csv -Force -NoTypeInformation
  
  
  $rslArr = New-Object -TypeName System.Collections.ArrayList
  $alertBySiteCode | Where-Object {$_.Name } | ForEach-Object {              
                  $obj = [pscustomobject]@{
                      Count = $_.Count -as [Int]
                      Name = $_.Name -as [String]
                      Computer = ($_.Group.'COMPUTER_NAME'  | Select-object -first 1) -join ','
                  }
                  $rslArr.Add($obj)
              }        
  
  $rslArr | Export-csv -LiteralPath C:\Temp\SEP-SquaredUp-AlertBySiteCode.csv -Force -NoTypeInformation
  				

4. Download and import this dashboard pack.

Download the zip file below and in the product go to the top right hand menu ☰ > Import Dashboard Packs and drag the zip file into the Manual Import box.

5. Publish the dashboard.

Need more detail with any steps?