Well, we eventually installed the hotfix available for KB 954948 from http://support.microsoft.com/kb/954948
Now in order for us to know what causes this problem, we need to do some study on how the ACS Database structure works.
As many of us already know, what ACS does is to collect security events from monitored servers and store them back into the ACS Database. These collected data will be stored in what we called a partition table, a table dynamically generated/created daily by ACS to store events data that was collected on that particular day. To let you have a clearer view of what I meant, take the following as an example.
I would like to ensure that the events collected on the 17th April 2009 is successfully stored in my ACS DB so how do I view those data? Like what I shared with you earlier, all the collected events are stored into partition tables and in order for us to identify which partition table was created to store the events for 17th April 2009, we need to select from the ACS Database and the SQL syntax to query the dtPartition table to list all the partition tables and sorting them by Date. This can be achieved using the following SQL statement:
SELECT * FROM dtPartition ORDER BY partition time
The returned resultset will list all the partition tables and which one stores the data for which date.
In my case ( as depicted in the following screenshot), I have already 258 partition tables created for the events that was collected for the past 258 days. I can also see that the partition table that stored the events that was collected on 17th April 2009 by referring to the PartitionId column which is 5705ab39_f297_4555_ad04_57f72603b941 and also PartitionStarTime and PartitionCloseTime as the date indicators.
Once we have the partition id identified, we shall validate the data that was stored is indeed collected on the 17th of April 2009. We will now attempt to select from the view that was created from the partition table and to do that simpy use the following syntax:
In our scenario the SQL syntax will look like this,
Now we have been able to understand a bit more on how ACS database structure works, the next step is to understand how reports are retrieved? Well to start with, ACS reports are retrieved from a Data Source which refers to the dvAll5 view. The interesting part on how this view work is that it is dynamically built to retrieve from all the views of the partition tables.
The returned resultset will list all the partition tables and which one stores the data for which date.
In my case ( as depicted in the following screenshot), I have already 258 partition tables created for the events that was collected for the past 258 days. I can also see that the partition table that stored the events that was collected on 17th April 2009 by referring to the PartitionId column which is 5705ab39_f297_4555_ad04_57f72603b941 and also PartitionStarTime and PartitionCloseTime as the date indicators.
Once we have the partition id identified, we shall validate the data that was stored is indeed collected on the 17th of April 2009. We will now attempt to select from the view that was created from the partition table and to do that simpy use the following syntax:
SELECT * FROM db0.dvALL5_
In our scenario the SQL syntax will look like this,
SELECT * FROM dbo.dvALL5_5705ab39_f297_4555_ad04_57f72603b941
We should be able to see the event data that was collected. As for my screenshot, what we have done is a bit extra where we tried to verify the events collected in the partition table is indeed those on 17th April 2009
Yes, this means if you have 20 days of events collected this generally means that you have 20 partition tables which also means that you have 20 views created and to top it all, the dvAll5 view will select from all the 20 partition views ! I personally have some reservation on this approach as this would lead to performance issue if especially if your data size is huge and if your retention period is exceptionally long. (Guess this is why the default retention period is only 14days)
So how does dvAll5 is being created then? Well, after we studied the syntax that builds the view the SQL syntax that builds it actually does a UNION on all the partition views ... and it looks something like this ~gulp~
create view [AdtServer].[dvAll5] as select * from dvAll5_8fe6b4cb_aab5_43ef_b3a7_4ae601e0be53 UNION all select * from dvAll5_0ff135f5_b14f_474c_a94f_c5a8249b3b82 UNION all select * from dvAll5_76c46145_961f_492e_a015_fad4c0c41046 UNION all select * from dvAll5_d9d39a78_66d1_48ee_8da3_c4685c6d9c59 UNION all select * from dvAll5_add3b5d7_48ee_4cce_a12e_8fa6f96577da UNION all select * from dvAll5_66975973_55ee_4db2_9c45_f655a2c03779 UNION ....... all select * from dvAll5_72e2f5be_bbdd_49af_bc79_8efba125bf78
Yes, if you have 20 paritions tables this means your dvAll5 view will really include all of them.
Now this is when things gets interesting. Remember that my friend needs to have a data retention for more than 2 years ! ... and how his ACS dies after the 256th day ?
The reason is because, SQL Server has a limitation that you cannot be SELECTING for more than 256 tables in ONE view hence the error the he faced occurs.
After we have installed the patch, ACS will be a bit clever. If it detects that it has more than 256 partition tables, dvAll5 will only retrieve the latest 256th tables hence the earlier table will be discarded from reporting.
Now for my friend who is the administrator and needs to ensure that ACS can report data up to 2 years ... that simply freaked him out in the first place ! But after we studied the structure carefully and through some creative thinking we managed to find a workaround for this ... which I will share on my next post.
