Updates not deployed ... even after 1 hr !!!!!

Was building a lab image for my training class next week and somehow things was not as smooth as I would fancy. :(

It was supposed to be a simple Software Update deployment lab which I am preparing and what seemed to be a very routine step (yes, did this hundreds of times and it works all the time) but somehow my client machines is still not getting the software update after 1 hour ! ( triggered countlessly on the Machine Policy and Software Update Deployment Cycle actions)

Well, I took a look at the client's updatesdeployment.log and noticed that the advertisement is not activated yet ! Chaos mode !

Then took a look at the Deployment Management for the update, and noticed that my Time/Settings is set to UTC which is by default.

So the lesson learned here is, unless you are great with mathematics calculations of timezone/date, better stick with the Client Local Time options !

Test Drive System Center Service Manager

Eager to try and see how the upcoming System Center Service Manager looks like?

Well then please go and have a try at the Hands On Lab for Service Manager

http://www.microsoftservicemanagertestdrive.com/

The scenarios in this lab will demonstrate an overview of a Microsoft System Center Service Manager installation and initial configuration, covering the following topics:

• Installing Service Manager
• Importing data from Active Directory,System Center Configuration Manager, and data and alerts from Operations Manager 2007 SP1 and above
• Configuring User Roles within Service Manager
• Manually adding users that were not imported from Active Directory
• Creating several templates, configuring initial parameters, creating queues, lists, and groups, and then creating a management pack to save any custom objects
• Installing Service Manager in a production environment in a scenario where Service Manager is installed on four computers

Enjoy !

Generating ACS Report is slow ... what I can do about it ?

I have a friend who set his ACS data retention for 2 years to comply to SOX standards which means he needs to set the data retention range to 2 years without an archiving solution at that time.

It has been a year since he has the ACS data collected and he contacted me recently and complaining about the Audit report takes ages to generate. Well it kinda makes sense for the reporting to be slow because of the sheer volume of the data that was collected.

So what's next? Well both of us have been toying around with the ACS DB and managed to find a workaround to do this.

Well as I have shared on my previous posts, the data source which the Audit Reports retrieved from actually points to the adtserver.dvAll5 view and this view will retrieve all the data collected for the past year.

We have 2 ways to do this:

Option 1: Modify the dvAll5 View
- Go to SQL Management Studio, modify the dvAll5 syntax to only include the views that you want to see.
- Execute the SQL syntax
- Note: Although this is simple and straight forward, you need to constantly go into SQL Management Studio to do this and your settings will eventually be overwritten everytime ACS creates a new Partition table because the DBCreatePartition.sql will rebuild the dvAll5 view


Option 2: Modify the DBCreatePartition.sql file
- This file resides in C:\WINDOWS\system32\Security\AdtServer
- Before you start playing with this, make a backup copy of this file.
- Edit this file and look for select top 42 PartitionId from dtPartition order by PartitionCloseTime desc
- Change the Top value to a lower value. For example, if I just want to generate report for the range of 2 weeks, I just need to modify the statement to select top 14 PartitionId from dtPartition order by PartitionCloseTime desc
- Save the SQL file
- What we did just now is to have ACS to only include the 14 latest Partition views to be included into the data source (which is dvAll5) which means the resultset is much smaller hence the performance will be increased.

What if you want to keep ACS data for more than 256 days ? - Part 3

In my previous posts, I have shared a little on the data structure of ACS database and what are the potential issues that we might faced if we do not adhere to best practice such as the 256 table limitation of SQL Server. That being said, many organizations has a requirement to keep their security events/logs up to a certain period of time to comply with a certain standards/compliance.

This is the dilemma which my friend is currently facing now realizing that he will potentially lose the ability to provide Security Audit reports for long term data (which is longer than 256 day). But after some study which we did, we managed to find a workaround for that though this requires alot of manual work and tweaking of the a couple of SQL scripts in ACS.

But before we go into details, just as a disclaimer you better make sure that you have properly and adequately backed up your entire OpsMgr infrastructure before you embark on this "perilous" adventure.

Well to start things with let us recap on the objective: We need to be able to generate Audit reports for up to 2 years worth of data.

1. So the first thing is to set the retention range for ACS from 14 days (by default) to the range that you need and let ACS do the rest in terms of collecting data and storing to the ACS database
2. To generate report for the range which is older than 256 day,go to SQL Server Management Studio and identify the partition tables IDs that stores the collected events/data for the date range from dtPartition table using the following SQL statement. SELECT * FROM dtPartition ORDER BY partition time
3. Now this is the interesting part. Once you have identified the Partition Table IDs, open the dvAll5 view and edit the syntax and include the partition views into the syntax. For example, if I have identified 2 partition tables with the id of 12345_abcde and 67890_fghij as mentioned in Step 2 the modified syntax for the dvAll5 view will look something like this: ALTER VIEW [AdtServer].[dvAll5] AS SELECT * FROM dvAll5_12345_abcde UNION ALL SELECT * FROM dvAll5_67890_fhgij
   
4. Execute the syntax and now the dvAll5 view which is the data source for reporting will retrieve the collected events/data that falls within your date range
   

Of course the steps above is rather tedious for some and quite complex for a few more.
Well, guess what? There is a tool out there that can do whatever I mentioned above easily and reliably.

The solution which can solve an administrator headache to manage long term ACS data is the Audit Collection Archiver brought to you by Secure Vantage.

This solution will archive your ACS data into a series of compressed binary files and provide you the ability to generate reports on the archived data.

If you would like to know more about this cool solution, have a look on this link
http://www.securevantage.com/Products/AuditCollectionArchiver/Default.aspx

What if you want to keep ACS data for more than 256 days ? - Part 2

In the last posting, I shared with you about a friend of mine who needs to keep his ACS data for 2 years, only to face with an issue when the collected data reached the 256th day and his ACS Collector service stops.

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:

SELECT * FROM db0.dvALL5_(your partition table id)

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

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.

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.