Ingesting database queries as logs in Seq using Seq.Input.MSSQL!

Among a bunch of stars in the Seq ecosystem, Seq.Input.MSSQL has to be one of the most ambitious and coolest. This Seq input app allows you to turn just about anything with a timestamp in a SQL database into Seq logs - which in turn can power your monitoring and alerting, exposing data and events that can't be acquired any other way.

I've spent a fair bit of time with this app, and honestly- it underscores a huge proportion of critical SLAs for us. Early on, we worked out a scheme to allow SQL Agent jobs to be ingested to Seq using the MSSQL input, and that means we can alert on scheduled job failures from some of our busiest processing nodes. It did mean that I needed to roll up my sleeves and amend the code to allow handling timestamps that didn't include milliseconds, along with addressing a small bug for new instances of the app. This was actually the first Seq app from another author that I contributed to, so it holds a special place in my heart for that reason too.

My recent efforts in fostering interoperability in the Seq ecosystem put Seq.Input.MSSQL into a particularly unique position. This was an app that was responsible for drawing data from a SQL database, and turning it into logs. That provided the opportunity for the MSSQL input to become an absolute powerhouse for mapping properties like Tags, Priority, Responder, and Jira-compatible tags like Project Key, Initial Estimate, Remaining Estimate, and Due Date. There was also an open issue/enhancement request for mapping the Seq event level to database values!

The opportunity that I talk about is that we can draw these properties from the database, and explicitly map the "expected values" to property values that can work with other applications. For example, I've integrated the Microsoft Endpoint Protection (previously known as System Center Endpoint Protection) database into Seq using the MSSQL input, and this gives me the opportunity to use the 'Severity' column in my view as a mapping for both the Event Level in Seq, and the Priority value for Jira ... and if I wanted, I could extend this to simultaneously map OpsGenie compatible priorities for a multi-purpose input!

While I've been using an unofficial build of this for some time, v1.2.0 of Seq.Input.MSSQL has now been released with all the new goodies - many thanks to Michael Hinni for the collaboration! Sadly, we did find that integrated authentication is a Windows-only feature, so we've found that (for now at least) the app can only run on a Windows Seq instance. One possibility is a separate package for Docker instances that omits the incompatible feature - but integrated authentication (where available) is a great way to limit privileges for the Seq instance, without using SQL authentication.

So for a start, let's share how I integrated the Endpoint Protection database to Seq using Seq.Input.MSSQL. It's a good illustration of how powerful this app is, and the opportunities that it opens up. This allows us to see all Windows Defender / Endpoint Protection detections of malware in real time, for an infrastructure piece that absolutely has no ready way to otherwise integrate with Seq.

Firstly, I create a view in my Microsoft Endpoint Manager (System Center Configuration Manager, or SCCM) database. This isn't unusual for the integration - it allows you some greater control over how you produce a result, and it's often the case that I want to pull in multiple sources of data to allow the logs to be well structured and meaningful. 

use CM_SC1
GO
CREATE VIEW MalwareLog AS select 
@@servername as ServerName,
ResourceID,
ComputerName, 
Status, 
Malware,
Category,
Severity,
Summary,
Cleaned,
Path,
Username,
dateadd(hh, datediff(hh, getutcdate(), getdate()), MIN(DetectionTime)) AS FirstDetection,
dateadd(hh, datediff(hh, getutcdate(), getdate()), MAX(DetectionTime)) AS LastDetection,
dateadd(hh, datediff(hh, getutcdate(), getdate()), MAX(LastMessageTime)) AS LastMessage,
COUNT(*) as InfectionCount,
EngineVersion,
dateadd(hh, datediff(hh, getutcdate(), getdate()), LastFullScanDateTimeStart) AS LastFullScanDateTimeStart,
dateadd(hh, datediff(hh, getutcdate(), getdate()), LastFullScanDateTimeEnd) AS LastFullScanDateTimeEnd,
dateadd(hh, datediff(hh, getutcdate(), getdate()), LastQuickScanDateTimeStart) AS LastQuickScanDateTimeStart,
dateadd(hh, datediff(hh, getutcdate(), getdate()), LastQuickScanDateTimeEnd) AS LastQuickScanDateTimeEnd,
'Malware Detected on {ComputerName}: Malware Name {Malware}, Category  {Category}, Severity {Severity}, Infections {InfectionCount}, Cleaned {Cleaned}' AS Message 
from
(
   select 
        t.ResourceID,
        ISNULL(s.Netbios_Name0 + '.' + s.Full_Domain_Name0, s.Netbios_Name0) as ComputerName,
  t.Path,
  t.UserName,
        CASE WHEN ai.ComputerStatus = 1 THEN 'None' WHEN ai.ComputerStatus = 2 THEN 'Cleaned' WHEN ai.ComputerStatus = 3 THEN 'Pending' WHEN ai.ComputerStatus = 4 THEN 'Failed' ELSE 'Unknown' END AS Status,
  tc.Name as Malware,
  tcat.Category,
  tsev.Severity,
  tsum.Summary,
  t.DetectionTime,
  CASE WHEN v.LastMessageTime IS NULL THEN t.DetectionTime ELSE v.LastMessageTime END as LastMessageTime,
  case when t.ActionSuccess=0 then 'Failed' when t.ActionSuccess=1 and t.PendingActions!=0 then 'Pending' when t.ActionSuccess=1 and t.PendingActions=0 then 'Cleaned' else 'Unknown' end as Cleaned,
        ah.AntivirusSignatureVersion EngineVersion, 
        ah.LastFullScanDateTimeStart LastFullScanDateTimeStart, 
        ah.LastFullScanDateTimeEnd LastFullScanDateTimeEnd, 
        ah.LastQuickScanDateTimeStart LastQuickScanDateTimeStart, 
        ah.LastQuickScanDateTimeEnd LastQuickScanDateTimeEnd 
   from v_GS_Threats t
   join v_R_System  s on t.ResourceID=s.ResourceID
   join v_FullCollectionMembership  c on t.ResourceID=c.ResourceID
   left join v_ThreatCatalog  tc on t.ThreatID=tc.ThreatID
   left join v_ThreatCategories tcat on t.CategoryID = tcat.CategoryID
   left join v_ThreatSeverities tsev on t.SeverityID = tsev.SeverityID
   left join v_ThreatSummary tsum on tc.SummaryID = tsum.SummaryID
   left join v_GS_AntimalwareHealthStatus ah on t.ResourceID = ah.ResourceID
   left join v_GS_AntimalwareInfectionStatus ai on t.ResourceID = ai.ResourceID  
   left join vEP_LastMalware v ON v.DetectionID = t.DetectionID
   where c.CollectionID='SMSDM003'
) as Infections
group by ResourceID, ComputerName, Malware, Category, Severity, Summary, Path, Username, Status,EngineVersion, LastFullScanDateTimeStart, LastFullScanDateTimeEnd, LastQuickScanDateTimeStart, LastQuickScanDateTimeEnd, Cleaned
order by LastDetection DESC OFFSET 0 ROWS
GO
CREATE LOGIN [DOMAIN\SEQSERVER$] FROM WINDOWS
GO
CREATE USER [DOMAIN\SEQSERVER$] FOR  LOGIN [DOMAIN\SEQSERVER$]
GO
GRANT SELECT ON MalwareLog to [DOMAIN\SEQSERVER$]
GO

This gives us a view with all the data that we need to produce nicely structured logs that can power alerts down the line.

Next, we configure an instance of Seq.Input.MSSQL with the following settings;

PropertyValue
TitleMalware Logs
Refresh every x seconds300
Server instance nameUCS1-S-SCFG01
Initial catalogCM_HQ1
Trusted ConnectionEnabled
Username 
Password 
Table or view nameMalwareLog
Column name of TimeStampLastMessage
Seconds delay1
Column name of MessageMessage
Include following columns as propertyServerName,ResourceID,ComputerName,Status,Malware,Category,Severity,Summary,Cleaned,
Path,Username,FirstDetection,LastDetection,LastMessage,InfectionCount,EngineVersion,
LastFullScanDateTimeStart,LastFullScanDateTimeEnd,LastQuickScanDateTimeStart,LastQuickScanDateTimeEnd,Message
Log application name as propertyMalwareLogs
Column name of Event LevelSeverity
Event Level MappingSevere=Error,High=Error,Medium=Warning,Low=Warning,Not Yet Classified=Warning
Serilog.Events.LogEventLevel4
TagsAntivirus,Malware,Infection,Seq
Column name of PrioritySeverity
Value mapping for PrioritySevere=Highest,High=High,Medium=Medium,Low=Low,Not Yet Classified=Low
Valid local time period 

Then we just need an appropriate signal that alert apps can listen to:

 Seq signal for anti-malware alerts

and finally - because we want this to go to Jira - an instance of the Seq.Atlassian.Jira app that listens to our new signal.

ConfigValue
TitleAnti-Malware Alerts to Jira
Stream incoming eventsEnabled
SignalAnti-malware Alerts
Allow manual inputDisabled
Re-order input by timestampDisabled
Jira Urlhttps://jira.domain.com
Comma separated list of event levels 
Project Key Property 
Jira Project KeySD
Jira Project Components 
Jira Issue LabelsITKC7,ITKC38
Include event tagsEnabled
Event tag propertyTags
Seq Event Id custom field # from Jira 
Jira Issue TypeService Request
Priority PropertyPriority
Jira Priority or Priority MappingHighest=Highest,High=High,Medium=Medium,Low=Low,Lowest=Low
Default PriorityHighest
Assignee Property 
Assignee 
Jira Summary[MS Endpoint] Malware ({{Category}}) found on {{ComputerName}}: {{Malware}}
Jira Description*Malware was detected*
\n \n
||Computer|{{ComputerName}}||
||Username|{{Username}}||
||Category|{{Category}}||
||Malware|{{Malware}}\n_{{Summary}}_||
||Severity|{{Severity}}||
||Status|{{Status}}||
||Cleaned|{{Cleaned}}||
||Path|{{Path}}||
||Infection Count|{{InfectionCount}}||
||First Detection|{{FirstDetection}}||
||Last Message|{{LastMessage}}||
||Engine Version|{{EngineVersion}}||
||Seq Event:|[{{$Message}}|{{$EventUri}}]||
Full Details as DescriptionDisabled
Full Details as CommentDisabled
Properties As CommentDisabled
Initial Estimate Property 
Initial Estimate

 

Remaining Estimate Property 
Remaining Estimate 
Due Date Property 
Due Date1d
Jira Usernamejirauser
Jira Password[password]

and the end result (with a few items blanked out) when malware is detected:

Malware alert successfully sent to Jira!

This is a dynamic Jira ticket, with priority mapped to the severity of the infection. We haven't used all of the possible mappings here - I could have auto-assigned the ticket based on severity, for example - but we do utilise the ability to read the tags from Seq.Input.MSSQL and combine them with the tags defined in the Seq.App.Atlassian.Jira application, which will then pass through to the Jira issue.

It might escape notice, but the implementation for Seq.Input.MSSQL means that, with the right config and signals, I could use a single instance of the MSSQL input to raise an alert to Jira for lower priority malware alerts, and an OpsGenie alert for a severe malware infection. I don't currently need that - but it's certainly an option.

I have a few other cool implementations with Seq.Input.MSSQL, like the aforementioned SQL Agent Jobs, logging Remote Desktop connections and disconnections by monitoring the connection broker database, and even simple database queries that do a 1:1 map of columns to logs and properties. I'll look to share at least some of those in the future.

I hope this helps to illustrate the power of the MSSQL input. It's a game changer when it comes to exposing data that can be monitored and alerted - especially where it's an application with no other prospect of sending logs to Seq. Michael did an amazing job with the app, and I'm proud to have contributed to making it even more powerful!

 

 

Comments

You may also like:

Seq Reporter - Turn your structured logs into scheduled reports!

Uhh ... You want what? So, you have all your apps logging to Seq, perhaps you have monitoring and alerting using apps like the Seq OpsGenie client, and maybe you're even using Event Timeout to detect events that didn't happen in time. Things are going great, except ... Well,...

Using Handlebars templates with Seq.App.Atlassian.Jira and Seq.App.OpsGenie

One of the really useful things with Seq.App.Opsgenie was that Nick Blumhardt had integrated Handlebars templates to the app, using Handlebars.NET. I liked it so much that, when I contributed to Ali Özgür's excellent Seq.App.Atlassian.Jira project, I carried it over. There's a few oddities in the JIRA REST API, and...

Using Seq.Input.MSSQL to read SQL Job Agent tables as logs!

Following on from my last post about using Seq.Input.MSSQL to read Endpoint Protection table entries as Seq logs, I wanted to share something that has really made a huge difference for us - turning the SQL Agent job tables into Seq logs! This was actually the first full MSSQL Input...