Symantec Endpoint Protection Dashboard

This endpoint protection dashboard gives an overview of the status of the various endpoint protection systems. It is used by the IT team to keep on top of device security, and by the service desk to escalate appropriately.

Ruben Zimmermann, (@Ruben8Z)

How do I get it?

Challenge

Endpoint protection is one of the key components in the overall security posture of any company. An easy way to retrieve real time information for a broad audience without giving direct permission on the Endpoint Management Systems was required.

Solution

Through the use of the native PowerShell tile in SquaredUp, it is possible to connect to the various Endpoint Management Systems with PowerShell scripts. The scripts produce custom aggregations and custom metrics that are otherwise not available out of the box from the various Endpoint Management Systems. The PowerShell tile on the SquaredUp dashboard is able to execute and display the results of the script with compelling visualisations, giving us a clear endpoint protection dashboard.

Dashboard walk-through

Here is a rundown of the different tiles in the Symantec endpoint protection dashboard:

AV Definitions – shows the AV definition age that is applied on the endpoints.
Last Server Check-in – shows when the last contact to the management server was made
Last Full Scan – a count of devices showing the month in which they received their last full scan
App Version – a count of devices across various versions of the AV

Infections – gives an overview of the number of infections over the last 30 days and which sites had the most infections
Malware – summarises the most detected malware in the last 7 days
Computer Infections – allows the service desk to get in touch with computer owners to perform further actions.

How do I import and configure this dashboard?

Download pack for SCOM Dashboard Server

Ensure you're using SquaredUp DS v5.4 or above.
Already a SquaredUp customer?
Get the latest version of SquaredUp DS for SCOM
New to SquaredUp?
Try it for free
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.

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.

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

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

Download and import this dashboard pack.
Download pack for SCOM Dashboard Server
1. Download the dashboard pack zip file for the dashboard pack you wish to import.
  There may be additional steps before or after the import to get the dashboard working in your environment.
2. In DS for SCOM go to the top right hand menu ☰ > Import Dashboard Packs and drag the zip file into the Manual Import box.
3. The dashboard pack is imported and if the pack contains top-level dashboards, these will automatically appear in the navigation bar, usually in a folder called 'Community Dashboards' for dashboard packs from the SquaredUp Community. If the dashboard pack also contains perspectives (see Working with perspectives), then you'll see these when you drilldown to the relevant object.
4. Carry out any additional steps required to make the dashboard work in your environment. These steps will be described on the download page for each dashboard. For example, you may need to create the correctly named Web API provider, create a PowerShell profile, or edit tile collections.
5. Edit the imported dashboard as required.
Publish the dashboard.
A newly created dashboard will not be visible to others until it is published.
Only admins can publish dashboards, unless you have been given author permissions to a Team Folder see Team Folders
If you made changes to an existing dashboard, the changes will only be visible to others after you published the dashboard again.
You can identify a dashboard that has not been published yet or has unpublished changes by the unpublished button at the top:
When you click on the unpublished button, you'll have two options:
- Publish will make the dashboard or changes visible to others.
  Note: A newly created dashboard will appear on the menu where you created it. To move the dashboard to a different place on the navigation bar see How to edit the Navigation Bar.
- Discard will delete your draft dashboard if it has never been published or, if you made changes to an already published dashboard, discard the changes.
Publishing dashboards to different audiences
Find out how to publish dashboards to a subset of users using Team Folders or visible to anyone even unlicensed users with Open Access (Sharing Dashboards with anyone - Open Access).

GET STARTED TODAY

Zero to dashboard hero in 60 seconds

Start now with hundreds of customizable dashboards.