ACS Internals – Part 1


I am going to use this post ramble about a couple of the internals of ACS.


The ACS DB is primarily made up of daily partition tables.... we create a new one every day during the nightly maintenance, which defaults to 1AM.  We create a new partition, then close the previous one.  Then, we kick off a reindex of the previous day's table for reporting performance.

To view all this.... first, lets have a look at the dtconfig table:

Select * from dtconfig


The only thing we would change in this table is the "number of partitions".  This value is essentially the number of partitions to keep, or number of days worth of data we will retain in the ACS DB.  The default is 14, and you need to adjust this based on your retention requirements and DB sizing capability.

Next, lets check out the dtpartition table:

select * from dtpartition order by partitionstarttime


Essentially.... these are your daily partitions.  At any given time... you should have one partition with a status of "0" and the rest should be status of "2".  "2" means they are ready to be groomed.   Pre-SP1, if the online indexing of a closed partition failed during the nightly maintenance, we would leave them in a status of "1".  This is bad, because they would never groom, and fill the database if this kept up.  If you have any that are left in a status = 1, then just run this in SQL:

Use OperationsManagerAC

UPDATE dtPartition

SET Status = 2

WHERE Status = 1

This will fix the grooming issue, and the tables will groom at the next maintenance interval.  This is a very common issue prior to SP1.



UPDATE 6/3/08 

Even in SP1 - occasionally people have reported issues where some partitions are left in a status of "1" and these partitions never groom.  If left unchecked, this can eventually fill the database/database volume.  Microsoft has released an internal hotfix that you can request from PSS if you feel you are impacted by this.  I have seen this happen in many large environments.  Request hotfix/KB 949969



Ok - enough on grooming.  On to bigger and better things.

Audit Collector

The audit collector really does all the work in ACS.  It keeps track of the forwarders (agents), maintains the queue, filters the data, and then writes to the ACS DB.

Lets first talk about the basics.  The audit collector runs a service, "Adtserver" which is running Adtserver.exe from the %systemroot%\system32\security\Adtserver directory.

Speaking of that directory - there is a lot of cool stuff in there!  Also present, are the .SQL files... which are called during maintenance.....  the primary ones to look at are DbCreatepartition.sql, DbClosepartition.sql, and DbDeletePartition.sql.  Pretty self explanatory.... these run to create new partition tables, close and reindex the previous day's table, and then to delete old tables that are ready to be groomed out.  These are called from the audit collector to the ACS database, and should not be run manually.

Also present in this directory is a little gem of a file, by the name of AcsConfig.XML.  This file has a list of ALL the audit forwarders ever known to the collector, and their last contact time, and sequence number of the last event they have sent to the collector.  You can copy this out - open it with Excel, and see all the data in a very readable format.  This data is kept in memory on the collector, and updates the file every 5 minutes.

Probably the biggest problem I see in an ACS environment, is just lack of proper sizing.  The Perf and Scale guide has really good guidance here for ACS, and should be followed:

One of the best things you can do is to apply a proper filter on the collector.  By default, ACS will collect and store every single event in the security event logs from forwarders.  This is good and bad.  Good - because you are getting everything.  Bad - because "everything" doesn't help you.  A large amount of the events logged in the security logs, are not very useful... depending on how draconian your audit policy is.  You really want to just collect the security events that are needed to meet your audit and security compliance requirements.  A couple good resources:

Here is a good, basic filter, to remove a lot of what most consider "not good info":

SELECT * FROM AdtsEvent WHERE NOT (((EventId=528 AND String01='5') OR (EventId=576 AND (String01='SeChangeNotifyPrivilege' OR HeaderDomain='NT Authority')) OR (EventId=538 OR EventId=566 OR EventId=672 OR EventId=680)))

How do you apply a filter???  Well, I am glad you asked!  We will run adtadmin.  Here is a link to all the parameters:

To examine the current filter, open a command prompt on the collector... and lets run a command in the %systemroot%\system32\security\Adtserver directory:    adtadmin -getquery

That will show you what you are currently filtering.  The default is "select * from AdtsEvent"  which is no filtering.  To use the filter posted above.... run the following:

adtadmin /setquery /collector:"collectorname" /query:"SELECT * FROM AdtsEvent WHERE NOT (((EventId=528 AND String01='5') OR (EventId=576 AND (String01='SeChangeNotifyPrivilege' OR HeaderDomain='NT Authority')) OR (EventId=538 OR EventId=566 OR EventId=672 OR EventId=680)))"

Comments (18)

  1. Kevin Holman says:

    That was for 2003 at the time of release.  I am sure there are better basic filters out now.

  2. Kevin Holman says:

    Filtering is ALWAYS done on the collector – by design.  This is critical – to keep any possible tampering with a filter on the forwarder from local administrators.

    So to be clear – on the forwarder – no filtering.  We send ALL events in the security log.

    On the collector – filtering occurs, we drop events from a queue before insertion into the database.

  3. Anonymous says:

    I ran the adtadmin script below:

    adtadmin /setquery /collector:"myservername" /query:"SELECT * FROM AdtsEvent WHERE NOT (((EventId=528 AND String01=’5′) OR (EventId=576 AND (String01=’SeChangeNotifyPrivilege’ OR HeaderDomain=’NT Authority’)) OR (EventId=538 OR EventId=566 OR EventId=672 OR EventId=680)))"


    Error 0x000006D9 occured:

    There are no more endpoints available from the endpoint mapper.

    What do you suppose is wrong?

    Nap Agonor

  4. TJ M says:


    Your ACS Collector is probably not configured correctly.  Confirm the OperationsManagerAC database is created and that your Collector is inserting data into it.  If not, rerun the ACS Setup.


  5. REN4 says:

    Thanks for this post.

    I have a question related to the ACS Filtering.

    When you apply a Filtering rule for ACS, where the filtering rule getting applied. At the Forwarder Level or Collector Level.

    Implementation of Rule


    Auditing Agents (Forwarders) will get the data and at the collection server level, it will discard the data according to the filtering rule.


    As soon as you apply a Filtering rule, agents (Forwarders) will get the rule and will send the data to the server according this rule…

    Can you please tell me how does that work?

  6. REN4 says:

    Thanks for your reply.

    That clarifies my question.

    So Filteration is happening at the Collector level. Will this create any N/w Bandwidth issue if you have more than 250+ DCs, with one collector configured.

  7. carl says:

    Hi, I tried to run the query mentioned to filter event IDs (SELECT * FROM AdtsEvent WHERE NOT… ), but received a error when running against the OperationsManagerAC Database in SCOM R2.

    "Msg 208, Level 16, State 1, Line 1

    Invalid object name ‘AdtsEvent’."

    any thoughts?

    Thanks in advance.

  8. Liat Bilic says:

    Secure Vantage Audit Collection Archiver Export (OperationsManagerAC) is failing again and again – what cause it ?

  9. martit01 says:

    Hi Kevin,

    Does your basic filter apply for Windows Server 2008 R2 also?  Or, is this only for Server 2003?



  10. Adrian Liechti says:

    Hi Kevin,

    at a customer of mine there was a server with a misconfiguration, so the server send a lot of acs spam to the acs collector. no the problem is, there are about 160GB Trahs in the ACS Database… do you know, is there any way to clean all events for a specific computer oder eventid?

    thanks for your help


  11. Anonymous says:

    Here are some more links from my private collections. This links are very usefull to administrate, configure

  12. HI Kevin, says:

    Waiting for your speech on 28th about SCOM MP Authoring. Please help me below.

    Here I have installed ACS 2012 on QA environment MG. but challenging part is to filter all the ACS alerts and enable the event ID’s based on the customer requirement. I knew that Adtadmin is command to filter the noise. Any command to filter all the ACS events
    and enable one by one. Please let me know. Thanks a lot
    AdtAdmin.exe /SetQuery [/Collector:CollectorName] /Query:QuerySyntax

  13. Kevin Holman says:

    Sure – our default filter is a WHERE NOT. Just use a WHERE. Example:

    SELECT * FROM AdtsEvent WHERE (EventId=538 OR EventId=566 OR EventId=672 OR EventId=680) I have not tested it, but I don’t see why that wont work.

  14. Thanks for this, i have modified the filter a bit as it wasn’t recognizing the command in SCOM 2012 R2, the / was replaced with – e.g. /setquery vs -setquery

    adtadmin.exe -setquery -collector:”servername” -query:”SELECT * FROM AdtsEvent WHERE NOT (((EventId=528 AND String01=’5′) OR (EventId=576 AND (String01=’SeChangeNotifyPrivilege’ OR HeaderDomain=’NT Authority’)) OR (EventId=538 OR EventId=566 OR EventId=672 OR EventId=680)))”

  15. Roman says:

    Hi Kevin,

    We faced with some issues related to nightly maintenance tasks.
    Partitions are being incorrectly closed and we receive the next event every night:
    Event ID: 4620
    Database partition closed:
    Guid: f9fd4e38_59ae_4f7d_8fef_f24e4dd7ff90
    Status: 0x00000005

    New partition is created properly but the previous partition tables are not indexed and as a result we have a very poor performance while running reports despite the fact that events filtering query is configured properly and database size is quite small.

    Also we regularly receive the next events even if there are no any network issues between ACS and SQL server:
    Event ID: 4618
    Error occured on database connection:
    Status: 0x00040000
    ODBC Error: 10054
    ODBC State: 01000
    Message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()).
    Database: SqlWriter
    Connection: dtPrincipal
    Statement: spInsertPrincipal

    Event ID: 4618
    Error occured on database connection:
    Status: 0x0021C001
    ODBC Error: 11
    ODBC State: 08S01
    Message: [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.
    Database: SqlWriter
    Connection: dtPrincipal
    Statement: spInsertPrincipal

    Have you ever faced with such kind of issues?

    Hope for your help and thank you a lot in advance!

  16. Rob Callan says:

    I have tried everything to apply the following ACS filter query using adtadmin.exe:
    adtadmin -setquery -collector:”collectorname” -query:”SELECT * FROM AdtsEvent WHERE NOT (((EventId=4624 AND String01=’5′) OR (EventId=4672 AND (String01=’SeChangeNotifyPrivilege’ OR HeaderDomain=’NT Authority’)) OR (EventId=4634 OR EventId=4662 OR EventId=4768 OR EventId=4776)))”
    really not sure where I’m going wrong, any tips greatfully received, I have opened the command line as admin and standard user. We have SCOM 2012 R2 and SQL 2012 in a virtual environment

  17. Ilian Ninov says:

    Hello Kevin,

    Another great source of information you got there, thank you!

    We are trying to set the Cross Platform Audit forward, and for some reason it doesn’t work.

    You’ve mentioned that ALL the audit forwarders should be listed in AcsConfig.XML , is this valid for the ACS Cross platform forwarding for Linux/unix?


  18. Cosmin says:

    Hi Kevin,

    How do I know when the DbDeletePartition sql file is triggered by the AdtServer? Is there a log from the AdtServer where it outputs the sqls that have been run?

    We had 2 occurrences in the past where the drive got full due to the fact that the partitions where not deleted and I had to manually run the SQL queries.


Skip to main content