Archiv rubriky: System Center

If you need to create own audit reports in MS SQL Report Builder for MS SCOM, you need to know how do it in Report Builder. You need also know little bit about SQL and better to know about PL/SQL.

Usual target of your interest will be audit reports from AD (User logins ….). If you need to create own report for user logins, the point of your interest will be MS SQL view – AdtServer.dvAll located in OperationManagerAC database.

FROM (SELECT * FROM AdtServer.dvAll WHERE EventId = 4624) AS li LEFT OUTER JOIN (SELECT * FROM AdtServer.dvAll WHERE EventId = 4634) AS lo on li.String01 = lo.String01

WHERE li.EventId = 4624
and li.CreationTime > dateadd(DAY,-3,GETDATE())
and DATEDIFF(SECOND,li.CreationTime, lo.CreationTime) > 0
and li.TargetUser not like ‚%$‘
–and li.TargetUser = lo.TargetUser
–and li.String13 not like ‚MICROSOFT_AUTHENTICATION_PACKAGE_V1_0‘
and li.String06 not in (‚{00000000-0000-0000-0000-000000000000}‘)
–and li.String02 in (‚2′,’10‘)
and li.String02 in (‚2′,’10‘)
and li.TargetUser not like ‚/_%‘ escape ‚/‘
and li.TargetUser = ‚user‘
–and li.EventMachine = ‚dc2008‘

This description of strings could you little help, if your ACS agent are running on Windows Server 2008 and newer.:

FROM (SELECT * FROM AdtServer.dvAll WHERE EventId = 528) AS li LEFT OUTER JOIN (SELECT * FROM AdtServer.dvAll WHERE EventId = 538) AS lo ON li.PrimaryLogonId = lo.ClientLogonId

where
li.String01 in (’10‘,’2′)
and li.CreationTime > dateadd(DAY,-6,GETDATE())
and DATEDIFF(SECOND,li.CreationTime, lo.CreationTime) > 0
and li.PrimaryUser not like ‚%$‘
–and li.PrimaryUser = lo.PrimaryUser
–and li.CreationTime > ‚12.12.2014‘
–and li.String13 not like ‚MICROSOFT_AUTHENTICATION_PACKAGE_V1_0‘
and li.String02 in (‚2′,’10‘)
and li.PrimaryUser not like ‚/_%‘ escape ‚/‘
and li.PrimaryUser = ‚username‘

This description of Strings could you little help, if your agent are running on Windows Server 2003 and older:

Port 5724 must be open to install this component and can be closed once this component has been installed.

gateway server

5723 —>

root management server

No

root management server

1433 —>

Reporting data warehouse

No

Reporting server

5723, 5724 —>

root management server

No

Port 5724 must be open to install this component and can be closed once this component has been installed.

Operations console

5724 —>

root management server

No

Connector framework source

51905 —>

root management server

No

Web console server

5724 —>

root management server

No

Web console browser

51908 —>

Web console server

Yes (IIS Admin)

Port 51908 is the default port used when selecting Windows Authentication. If you select Forms Authentication, you will need to install an SSL certificate and configure an available port for https functionality for the Operations Manager 2007 WebConsole Web site.