Custom Microsoft System Center Endpoint Protection 2012 Reporting

This post I'm going to share how I setup a custom infection alerting for SCCM EP 2012 that is used to feed infection data in to Splunk. I recently developed this at a company that was switching over from Symantec EP and had already had some previous reporting capability with Splunk. The goal was to recreate the same infection reporting functionality they had previously from a ready made Symantec Endpoint Protection Splunk App.

First, you will need to get a little familar with the SCCM database as this is where all the EP infection data is housed.

The main table I was interested in for my solution was dbo.EP_Malware which was logging all the infections. Here is a screenshot showing all the columns available to this table:

The main requirement I had was to log the alerts of infections at a real-time frequency so I will break down the process of my solution here before presenting it:

1) A SQL “After Insert” Trigger is setup on dbo.EP_Malware table in the SCCM Database to collect the newly inserted infection entry

2) After a new addition to the EP_Malware SQL table it will copy this over to a custom made temporary table called dbo.SplunkEP with the Record ID of the infection entry and add a timestamp to it

3) A SQL Agent job is kicked off next that will run a query to examine this temporary table (dbo.SplunkEP) and collect the RecordID (Primary Key) of the newly inserted EP_Malware table

4) After obtaining the RecordID it will run a custom query against the dbo.EP_Malware table and other associated tables in the SCCM DB to collect all the required data for the alert

5) This custom data will be posted to the Event Viewer Log on the SCCM Server, and subsequently a Splunk Event forwarder agent on the machine will send the infection data up in to Splunk.

Example end result:

OK, enough process, let me get down to the solution!

1) First we need to create the trigger on dbo.EP_Malware, launch SQL Management Studio and connect to your SCCM db, be sure to edit the use command

USE [YOUR_SCCM_DB_GOES_HERE]

GO

/****** Object:  Trigger [dbo].[SplunkUpdate]    Script Date: 04/22/2014 15:40:50 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        Matt Shorrosh

-- Create date:

-- Description:  

-- =============================================

CREATE TRIGGER [dbo].[SplunkUpdate]

   ON  [dbo].[EP_Malware]

   AFTER INSERT

AS

BEGIN

      declare @temp as bigint

     

       select @temp=RecordID from inserted

       Insert Into dbo.SplunkEP values(@temp,GETDATE())

      

    EXEC msdb..sp_start_job

        @job_name = 'SplunkEPReporting';

END

GO

2) Create a temporary table called SplunkEP, be sure to edit the use command 

USE [YOUR DB]

GO

/****** Object: Table [dbo].[SplunkEP] Script Date: 03/13/2014 08:31:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[SplunkEP](

[ID] [bigint] NOT NULL,

[Time] [datetime] NOT NULL,

CONSTRAINT [PK_SplunkEP] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

3) Create SQL Agent Job



Setup a new SQL Agent Job called SplunkEPReporting and configure a .ps1 file with the SQL query prepared as show in Step 4.

 4) Create .ps1 file that includes the custom SQL Query, be sure to edit the use commands

Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
$data1 = invoke-sqlcmd -query "Use [YOUR_SCCM_DB_HERE];

select top 1 id from dbo.SplunkEP order by time desc"

$record = $data1.id

$data = invoke-sqlcmd -query "use [YOUR_SCCM_DB_HERE]; 

Select distinct top 1 v_R_System.Name0 as 'Computer Name', 
  dbo.EP_Malware.RecordID, 
  dbo.EP_Malware.LastMessageTime, 
  dbo.EP_Malware.DetectionTime, 
  dbo.EP_Malware.DetectionSource, 
  dbo.EP_Malware.ThreatName,
               dbo.EP_Malware.Path,
               dbo.EP_Malware.Process,
               dbo.EP_Malware.ExecutionStatus,
               dbo.EP_ThreatDefaultActions.DefaultAction,
               dbo.EP_Malware.ActionSuccess,
               v_R_System.Distinguished_Name0 as 'Distinguished Name',
               dbo.EP_Malware.PendingActions,
               dbo.Users.FullName as 'UserName',
               STUFF((SELECT '; ' + v_RA_System_IPAddresses.IP_Addresses0 
    FROM v_RA_System_IPAddresses 
    WHERE v_RA_System_IPAddresses.ResourceID = dbo.EP_Malware.MachineID
    FOR XML PATH('')), 1, 1, '') as 'IP Addresses'
                 
from dbo.EP_Malware inner join v_R_System on dbo.EP_Malware.MachineID = v_R_System.ResourceID 
inner join v_RA_System_IPAddresses on dbo.EP_Malware.MachineID = v_RA_System_IPAddresses.ResourceID                
inner join dbo.Users on dbo.EP_Malware.UserID = dbo.Users.UserID
inner join dbo.EP_ThreatDefaultActions on dbo.EP_Malware.CleaningAction = dbo.EP_ThreatDefaultActions.DefaultActionID


where RecordID=$record"

$delete = invoke-sqlcmd -query "use [YOUR_SCCM_DB_HERE]; delete from dbo.SplunkEP where id=$record"


Write-Eventlog -logname Application -source SplunkEPReporting -eventid 1111 -message ($data | Format-List | Out-String)

5) Create EventLog Entry for your Splunk EP Reports

Open up a Administrator Powershell and enter the following:

New-EventLog -Source SplunkEPReporting -Logname Application