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:
