Hi All,
I have been given a task to create an alert that fire when a condition is meet. I have created a custom template using sql experience monitor for the alert and now need to get the info below into and email alert.
So can anyone suggest or show me how this can be done.
Add-PSSnapin sqlservercmdletsnapin100
$sqlquery = @"
SELECT
vTI.ThreatTypeName,
vTI.ThreatSubTypeName,
vTI.ThreatName,
vTE.EventTime,
vTI.FirstDetectedAt,
vTI.FullFilePath,
vC.Name AS ComputerName
FROM [SOPHOS521].[Sophos Reporting Interface].[vThreatEventData] AS vTE
JOIN [SOPHOS521].[Sophos Reporting Interface].[vThreatInstances] AS vTI
ON vTE.ThreatID = vTI.ThreatID
JOIN [SOPHOS521].[dbo].[ComputerHosts] AS vC
ON vTI.ComputerID = vC.ID
WHERE convert(datetime,vTE.EventTime) > DATEADD(DAY,-5,GETDATE())
ORDER BY vTE.EventTime
$sqlresult = Invoke-sqlcmd -ServerInstance "XXXXXX" -Database "XXXXX" -Query $sqlquery
$sqlresult
$report = "<style>"
$report = $report + "BODY{background-color:white;}"
$report = $report + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$report = $report + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:lightgrey}"
$report = $report + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:white}"
$report = $report + "</style>"
$htmlreport = $sqlresult | Select-object -property ThreatTypeName,ThreatSubTypeName,ThreatName,EventTime,FirstDetectedAt,FullFilePath,ComputerName |
ConvertTo-HTML -head $report -body "<H2>Threat Alert Information - Last 5 Minutes</H2>"
#$htmlreport|
#Out-File C:\_it\Test.htm
#Invoke-Expression C:\_it\Test.htm
Thanks,
Pat
