Combining 2 ACS databases into the ACS reports

The ACS collector to ACS database is sad to say a one to one relationship. This means you can only have one ACS collector writing to a single ACS SQL database. Yes you can have each ACS database hosted in a seperate SQL instance on the same SQL server. The big question is how do you read the security event data from the different databases and display them in the SCOM console
The way I accomplished this was to create a database and then create a view for each of the ACS database servers in that custom database.
Query use to create the views
USE [CombinedSecurityEvents] – This is the database that i created
GO SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[scd5_dvall] AS – this is the view name for the one ACS database server
select *,’SCD0005′ as ‘COLDB’ from [operationsmanagerac].[adtserver].[dvall] –

The ‘SCD0005’ as ‘COLDB’ in the select statement is a reference for which database these events cames from. You can call this anything you want
I then created a SQL view that pulls all this information together and this view is then used in my ACS reports.

USE [CombinedSecurityEvents]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [dbo].[combined_dvall] AS – The view is called “combined_dvall”
select * from scd5_dvall union all select * from scd6_dvall

Viewing the database with in my case the three views

So in the select statement above I have to views called scd5_dvall pointing to the one ACS database server and a second view called scd6_dvall that points to the second ACS database server. The union statement “combines” the contents of these two views.

So if you then view the data in SQL management studio – The COLDB column is highlighted, this is just a control column for me to see that all events from both ACS databases is returned:

In my ACS reports i then use the following query structure

select count(*) as count, convert(varchar, [collectiontime],101) as date
from combined_dvall – this is the view that i created above.
where [collectiontime]>=@datefrom
group by convert(varchar, [collectiontime],101)
order by convert(varchar, [collectiontime],101) desc

The report that utilizes the query

Advertisements

2 thoughts on “Combining 2 ACS databases into the ACS reports

  1. I got this error:

    An error has occurred during report processing. (rsProcessingAborted)
    Query execution failed for dataset ‘dataSet’. (rsErrorExecutingCommand)
    Semantic query execution failed. Login failed for user ‘NT AUTHORITY\ANONYMOUS LOGON’. (rsSemanticQueryEngineError)

    can anyone help me ?!

  2. Excellent, what a website it is! This weblog gives helpful data to us,
    keep it up.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s