Ryan Brennan's Blogspot

• Security Data should not be groomed or deleted if required by regulatory or legal needs to keep the security data
• Security Data should be archived or backed up to comply with regulatory or legal needs

That's all, just wanted to make sure I said that. 

Ok so i am providing 3 SQL scripts that do the following, a table to enter Event IDs into:

-----------------------------------

USE [OperationsManagerAC]
GO
/****** Object:  Table [dbo].[SVT_EventIDsToFilter]    Script Date: 09/24/2008 16:52:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SVT_EventIDsToFilter](
 [EventID] [int] NOT NULL
) ON [PRIMARY]

---------------------------------------

A sproc to groom to read the table and groom events from the OperationsManagerAC DB

---------------------------------------

USE [OperationsManagerAC]
GO
/****** Object:  StoredProcedure [dbo].[SVT_spFilterEventIDs]    Script Date: 09/24/2008 16:53:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  Ryan Brennan
-- Create date: 9/24/08
-- Description: Filter out EventIds from ACS History Database to help run optimal
--    Event IDs are defined in table SVT_EventIDToFilter
-- =============================================
CREATE PROCEDURE [dbo].[SVT_spFilterEventIDs]
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

DECLARE @TABLE NVARCHAR(512)
DECLARE @TABLE_STATS NVARCHAR(1024)
DECLARE @TABLE_DELETE_QUERY NVARCHAR(2048)
DECLARE @SCHEMA_ID INT
DECLARE @SCHEMA NVARCHAR(255)

DECLARE @TABLE_CURSOR CURSOR
 SET @TABLE_CURSOR = CURSOR FOR
select name,[schema_id] from sys.tables where name like '%dtEvent%'

OPEN @TABLE_CURSOR
 FETCH NEXT FROM @TABLE_CURSOR INTO @TABLE,@SCHEMA_ID

WHILE @@FETCH_STATUS =0
 BEGIN
  SET @SCHEMA = (select name from sys.schemas where schema_id = (select distinct(schema_id) from sys.tables where name = @TABLE))
  SET @TABLE = '[' + @SCHEMA + '].' + '[' + @TABLE + ']'
  SET @TABLE_STATS = 'SELECT COUNT(*) FROM ' + @TABLE + ' WHERE EventNo IN (SELECT EventID FROM SVT_EventIDsToFilter)'
  SET @TABLE_DELETE_QUERY = 'DELETE FROM ' + @TABLE + ' WHERE EventNo IN (SELECT EventID FROM SVT_EventIDsToFilter)'
  --PRINT @TABLE
  --PRINT @TABLE_DELETE_QUERY

  Print 'DELETING ROWS FROM  '  + @TABLE
  EXEC (@TABLE_STATS)
  EXEC (@TABLE_DELETE_QUERY)
  
FETCH NEXT
  FROm @TABLE_CURSOR INTO @TABLE,@SCHEMA_ID
 END
CLOSE @TABLE_CURSOR
DEALLOCATE @TABLE_CURSOR

END

----------------------------------------------

A SQL Job to schedule to run the the Sproc at regular intervals to remove the unwanted Events IDs for reporting purposes:

----------------------------------------------

USE [msdb]
GO
/****** Object:  Job [Secure Vantage Audit DB Filter Events]    Script Date: 09/24/2008 16:50:58 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 09/24/2008 16:50:58 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Secure Vantage Groom ACS Events',
  @enabled=1,
  @notify_level_eventlog=0,
  @notify_level_email=0,
  @notify_level_netsend=0,
  @notify_level_page=0,
  @delete_level=0,
  @description=N'Jobs Filter out EventIDs as defined in table SVT_EventIDsToFilter',
  @category_name=N'[Uncategorized (Local)]',
  @owner_login_name=N'CORP\momadmin', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Execute Filter]    Script Date: 09/24/2008 16:50:59 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Execute Filter',
  @step_id=1,
  @cmdexec_success_code=0,
  @on_success_action=1,
  @on_success_step_id=0,
  @on_fail_action=2,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'TSQL',
  @command=N'EXEC [SVT_spFilterEventIDs]

',
  @database_name=N'OperationsManagerAC',
  @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Filter Schedule',
  @enabled=1,
  @freq_type=4,
  @freq_interval=1,
  @freq_subday_type=1,
  @freq_subday_interval=0,
  @freq_relative_interval=0,
  @freq_recurrence_factor=0,
  @active_start_date=20080924,
  @active_end_date=99991231,
  @active_start_time=190000,
  @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

----------------------------------------------------

That's all. 

Just wanted to write about some ACS experiences and cool stuff we have been doing.  It's great to see ACS start taking off as a viable Enterprise solution for Security Event Log Management.  I have seen installations as large as 12000 servers running ACS on multiple ACS collectors working perfectly. Really nice to see such a robust Security Event Log Management tool that scales so easily and meets audit requirements.  First off for anyone not familiar with ACS or just looking for some general information should first read this article on technet. For common general ACS Administrative commands & tricks this is great ACS Admin guide.

Here are some design tips and tricks:

·         Planning Phase - Make sure you understand your audit requirements. The amount of data and storage that you need will be directly proportional to these core principals:

1.     Group Policy Audit policies configuration. Your Group Policy audit configuration is directly related to the amount of Security Auditing that is generated on both Domain Controllers and Windows Member Servers.  I would highly suggest that you read Microsoft best practices on Security Auditing.  This information covers your core needs.  However I would suggest if you’re not sure if you need the information to leave it off while trying to test in a lab environment to ensure a successful test.

2.     The number of Domain Controllers, Member Servers, Workstations, and users in your organization.  Remember that every endpoint is both an audit point and a vulnerability.  Carefully decide what you want to audit.  Common best practices are to start with Domain Controllers and File Servers - however it's really best to audit every end point and usually required from regulatory needs.

3.     Use this ACS planning worksheet to compute your audit load and storage requirements.

4.     Configure your ACS Noise filters and constantly tune your noise filters based on most common security events!  I can't be more adamant about this, I've seen so many unhealthily and failed ACS installations because of noise filtering. Windows creates a lot of Security Events that are just not needed for auditing.  A common conversation between an Auditor and IT person is that we want to audit everything. ok well great if we had unlimited storage capability.  The reality is that you need to prevent unwanted data entering your SQL Server database, which not only bloats it but adds performance and reporting constraints.   I highly recommend reading the Secure Vantage ACS noise filtering guide.  But just don't read it, make sure you understand it.  If you don't know why we suggest to filter something please due your diligence.  Randy's Ultimate Windows Security Encyclopedia website provide great guidance around this as well guide on Security Events Audit Reference List Secure Vantage website. Encyclopedia

·         Storage Requirements - Make sure you access your storage requirements.  Understand how long you need to keep the Security data for auditing.  For example PCI DSS Standard requires that you keep 1 year worth of security data retention and 90 days available Online. Please make sure to review with your auditors due your diligence with Regulatory standards you need to adhere to. 

Things to keep in mind while planning:

1.     How long do I need to store the data (Retention Period), this breaks into 2 or 3 different categories.  Online Storage - what your store in SQL database, Near-Online Storage - what is readily available to load into a database, and Offline Storage - what can be restored if needed to load into a database and report against.

2.     So how does this translate in ACS world.  By default when you install ACS it asks you how long you want to store your data in the ACS database - default is 14 days.  Most folks will just adjust this 90 days or even 365. Eek! highly not recommended to do this.  Almost every unhealthy ACS environment can be attributed to changing this or related to a bug with ACS closing partitions correctly, please make sure you are running ACS SP1 with the latest hotfixes.  But what this does it accumulate and store data that is not needed in your database causing performance issues and ACS to not functioning properly.  ACS is highly transactional system, it's best to let is function to just collect data and stick with the defaults or max of 30 days.  Ok now your thinking how do I leverage all of the other data ACS creates, maintain it and use for reporting or auditing needs.  Database backups are a possibility but this means you need to do full database backups and restores and have a full maintenance plan etc to ensure success.  Sorry for the product pitch here but the Secure Vantage Security Auditing product suite covers a lot of this.  Key concepts is it allows you to archive the data off into a daily compressed flat file, load up the data as needed, report audit against the data for audit scenarios, as well as ACS administration product to help with a lot of the things I have talked about herein.  This will allow you to only need to store 14 days or less in your ACS Collector database, knowing that you have daily backups archived and cataloged you can easily reload the data or have it automatically loaded to a historical report server.  If you have multiple ACS Collectors then it high advantageous to use this technology to load Security Audit data from multiple ACS databases into a single Historical database.  This will also attribute and satisfy your Online or Near-Online storage requirements, it's very easy to load and use the data per an audit requirement and have seen it used quite often.

3.     Taking into consideration you online and offline storage requirements and the tools that you use to satisfy those, you can decide on how much storage you need to hold your ACS database(s) and how much storage you need to store your backups no matter how your choose to do so.

Sample Matrix of Disk Space Needed without using Archiving or Backups:

·         Hardware & Server Requirements - We commonly hear this question of best practices on Server hardware and architecture designs.  I will break the ACS server roles and review some design considerations for ACS.  Every environment is unique but given these best practices and your internal knowledge of your environment should be able to devise a suitable plan.  Design considerations should involved # of Servers to Audit, roles of servers, and audit policies, filters, etc to come with a suitable hardware requirement and design.

Server Role Planning:

1.     ACS Collector Server Role - This role the ACS Collector Server and ACS Database reside on the same server.  Assumed this server is just collecting data and not reporting and using Secure Vantage Archived.   

2.     ACS Reporting Service Server - This role is SQL Server Reporting Services only recommended best practice from MS is to keep Reporting Service on its own Server.

3.     ACS Data warehouse Database Server - This role is the Historical Database that is used for reporting against the archived data off of the ACS Collector.

Sorry to leave everyone hanging this article got much to big as there is way too many things to point and details to mention.  There will be a continued blog with appropiate Server specifications,sample design specifications, maintenance tasks, tuning, etc - as I have been asked numerous times.

1. Open up Registry Editor
2. Navigate to HKEY_CURRENT_USER\Software\Microsoft\Microsoft Operations Manager\3.0\Console
3. Close the Console and Re-Open it

I delete everything but you may want to be cautious and only delete the views that are not behaving properly.

-ryan

• *********LICENSING For OpsMgr is a MUST ****************
• ACS Collector Server and Database installed
• Account has Domain Admins prvileges or rights on every server specified
• Download PSexec from Microsoft @ http://technet.microsoft.com/en-us/sysinternals/default.aspx
• Download ACSAgentDeploy script @ http://www.enigmatechnology.com/blogs/ACSStandAlone/DeployInstallACS.zip

Benefits

1. Deploy ACS for Security auditing without OpsMgr dependency
2. Speed up ACS deployment

How the script works:

1. The script takes two parameters <TestFileListofServerToDeployto.txt> <ACSCollectorNameList>
2. The script will iterate through each Server in the text file
3. Copy the ADtAgent.exe binary to the destination Computer C:\windows\system32\
4. Install ACS Agent using PSExec
5. Configure the ACS Agent registry and point to the ACS Collector specified
6. Restart the ACS Agent

Please check it out and let me know if you have any issues. It's a living script so I will gladly make changes. I've used it to deploy a few environments were they had OpsMgr licensed and wanted to deploy ACS without fully deploying OpsMgr.

1. Open up a command window and goto the path C:\Windows\System32\Security\AdtServer
2. Type AdtAdmin.exe -? which will show you how to Administer ACS Collector filters
3. To get your current filter type AdtAdmin - getquery
4. 
5. To define you filter query type AdtAdmin -setquery -query:"Select * from AdtsEvent where Not (EventID=529 and EventID=528)" - example security events filters
6. 

1. Enable SysLog or SNMP forwarding from your Non-Windows Devices to OpsMgr Management Server IP address/hostname
2. Setup a SysLog or SNMP rule to capture the messages sent from the Non-Windows Devices, This will give you basic collection of non windows events into OpsMgr. One more step required to get them into ACS
3. Add a response script to the rule created in step #2 to fire them into the Security Log, provided you have an ACS forwarder installed it will be sent to ACS DB.

Ok, so I was very trivial with my instructions and I will note this is not for the novice OpsMgr Administrator.. There are some moving parts and integration required to get this working.  My major point is that it can be done and unleash OpsMgr/ACS as a true Heterogenous platform for Security Event Collection (some assembly required ;).  Feel free to email if you care to understand more about it..

However the cool guys at Secure Vantage have released an ACS Hetero SDK which gets you 90% of the way there or more to integrating Unix, Linux, VMware, DB2, Oracle, or any other device you like into OpsMgr and ACS. pretty cool stuff!!

1. Copy AdtAgent.exe to c:\windows\system32\AdtAgent.exe
2. Run c:\windows\system32\AdtAgent.exe –install
3. Start the Service
4. Configure the Registry as follows:

HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\AdtAgent\Parameters

a.       Ensure DWORD key EventlogLoggingLevel is set to 2

b.      Add DWORD key LocalConfig and set to 2

c.       Add DWORD key NoCache and set to 2

d.      Add Multi-String Value key AdtServers and enter name of the collector to forward to.

5. Restart Service (Operations Manager Audit Forwarding Service)

PowerShell script to install to be released soon....