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;

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

 

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

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

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!