MS SCOM 2007 R2 – Audit Reports

scom2007r2

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.

 

Table join for EventID on Server 2008 and newer

SELECT distinct
li.TargetDomain
,li.TargetUser
,li.PrimaryUser
,li.String06 GuiDID_li
,lo.String06 GuiDID_lo
,li.String01 as LogonType1
, li.EventMachine
, li.Source
, li.String13 as AuthPackage
, li.String12 as LogonProcess
, li.String03 as LogOnFrom
–, cast(CAST(li.CreationTime as time) as DATEtime) as LoginDateTime
–, dateadd(D,DATEDIFF(D,li.CreationTime, lo.CreationTime), cast(CAST(lo.CreationTime as time) as DATEtime)) as LogoutDateTime
–, CAST(li.CreationTime as time) as LoginTime
, li.CreationTime as LoginDate
, lo.CreationTime as LogoutDate
–, DATEDIFF(SECOND,li.CreationTime, lo.CreationTime) as diff
,li.String02 as LogonType2

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‘

 

SCOMMSSQLREPORT2008

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

, String01 as TargetLogonId
, String02 as LogonType
, String03 as LogOnFrom
, String04 as ipPort
, String05 as TargetServerNetBiosName
, String06 as LogonGuid
, String07 as String07
, String08 as String08
, String09 as KeyLength
, String10 as Process_ID
, String11 as ProcessName
, String12 as LogonProcess
, String13 as AuthPackage
, String14 as String14
, String15 as String15
, String16 as String16
, String17 as String17
, String18 as String18
, String19 as String19
, String20 as String20
, String21 as String21
, String22 as String22

—————————————

, String14 as String14
, String15 as String15
, String16 as String16
, String17 as String17
, String18 as String18
, String19 as String19
, String20 as String20
, String21 as String21
, String22 as String22

=  is default  -> n/a

 

Table join for EventID on Server 2003

SELECT distinct
, li.TargetDomain
, li.PrimaryUser
, li.EventMachine
, li.Source
, li.String09 as AuthPackage
, li.String02 as LogOnFrom
, cast(CAST(li.CreationTime as time) as DATEtime) as LoginDateTime
, dateadd(D,DATEDIFF(D,li.CreationTime, lo.CreationTime), cast(CAST(lo.CreationTime as time) as DATEtime)) as LogoutDateTime
, CAST(li.CreationTime as time) as LoginTime
, li.CreationTime as LoginDate
, lo.CreationTime as LogoutDate
, DATEDIFF(SECOND,li.CreationTime, lo.CreationTime) as diff
,li.String01 as LogonType

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‘

SCOMMSSQLREPORT

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

, String01 as LogonType
, String02 as LogOnFrom
, String03 as String03
, String04 as TargetServerNetBiosName                                                                                                                                                                                                                                                                                                                 , String05 as LogonGuid                                                                                                                                                                                                                                                                                                                                               , String06 as CallerProcessID

, String07 as –
, String08 as LogonProcess
, String09 as AuthPackage

, String10 as String10
, String11 as String11
, String12 as String12
, String13 as String13
, String14 as String14
, String15 as String15
, String16 as String16
, String17 as String17
, String18 as String18
, String19 as String19
, String20 as String20
, String21 as String21
, String22 as String22

SCOM – ports

Operations Manager 2007 SP1 Component A Port Number and Direction Operations Manager 2007 SP1 Component B Configurable Note
root management server 1433 —> Operations Manager database Yes (Setup)
management server 1433 —> Operations Manager database Yes (Setup)
management 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.
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.
connected root management server (Local) 5724 —> connected root management server (Connected) No
Agent installed using MOMAgent.msi 5723 —> root management server Yes (Setup)
Agent installed using MOMAgent.msi 5723 —> management server Yes (Setup)
Agent installed using MOMAgent.msi 5723 —> gateway server Yes (Setup)
gateway server 5723 —> management server Yes (Setup)
Agent (Audit Collection Services forwarder) 51909 —> management server Audit Collection Services collector Yes (Registry)
Agentless Exception Monitoring data from client 51906 —> management server Agentless Exception Monitoring file share Yes (Client Monitoring Wizard)
Customer Experience Improvement Program data from client 51907 —> management server (Customer Experience Improvement Program End) Point Yes (Client Monitoring Wizard)
Operations console (reports) 80 —> SQL Reporting Services No The Operations console uses Port 80 to connect to the SQL Reporting Services Web site.
Reporting server 1433 —> Reporting data warehouse Yes
management server (Audit Collection Services collector) 1433 —> Audit Collection Services database Yes

SCOM nastavení přeposílání SNMP trapů

 

Set SCOM 2007 to forward SNMP traps

Trapgen.exe : Used to generate the traps.

download trapgen.exe  -http://trapgen.trapreceiver.com/ or you can find it in my OneDrive (accessible from this page ), folder tools (https://onedrive.live.com/?cid=A2DAD70E062C0112&id=A2DAD70E062C0112%2112099&authkey=%21ANIbXFTLL1i0y70)

SNMPTrap.exe : Used to check out snmp traps.( for  this check , adjust destination  IP address on local IP of SCOM server )

Settings:

Download or copy both tools on your SCOM server. I copy them in C:\SNMP\ folder.

In the console go to the Administration pane then select Settings and open Notification properties.

Go to the Command tab and click on Add.

The command line used is : C:\SNMP\trapgen.exe

 

The command line parameters are :

Where IP 192.168.12.130 is destination IP address of next monitoring server (Tivoli IBM) 192.168.15.201 is the IP address of my SCOM server

The initial directory is : C:\SNMP\

When you are done , create a new Notification recipient ( can be local user of SCOM server or domain user )

Select your notification command channel like below (delivery  address is useless for us) and continue in N.D Wizard

So you have Recipient and  last step will be how create Subsciption.

Make up new name for subscription and add your exist notification recipients for SNMP traps

On User role filter tab press next and here you can choose groups which you want to cover.

On Classes tab press next and here choose all ( press on check all button ) , if you want and finish the wizard.

Now you can check if your monitoring system 3rd side is geting snmp traps from SCOM2007

I have used it on my SCOM server and „without problems“

good web page – http://blogs.technet.com/b/kevinholman/archive/2007/12/12/adding-custom-information-to-alert-descriptions-and-notifications.aspx

$Data/Context/DataItem/AlertId$ The AlertID GUID $Data/Context/DataItem/AlertName$ The Alert Name $Data/Context/DataItem/Category$ The Alert category (PerformanceHealth,PerformanceCollection,Operations,EventCollection,StateCollection,SoftwareAndUpdates,Alert,System,Custom,AvailabilityHealth,ConfigurationHealth,SecurityHealth,Discovery,NotificationCategory,Maintenance $Data/Context/DataItem/CreatedByMonitor$ True/False $Data/Context/DataItem/Custom1$ CustomField1 $Data/Context/DataItem/Custom2$ CustomField2 $Data/Context/DataItem/Custom3$ CustomField3 $Data/Context/DataItem/Custom4$ CustomField4 $Data/Context/DataItem/Custom5$ CustomField5 $Data/Context/DataItem/Custom6$ CustomField6 $Data/Context/DataItem/Custom7$ CustomField7 $Data/Context/DataItem/Custom8$ CustomField8 $Data/Context/DataItem/Custom9$ CustomField9 $Data/Context/DataItem/Custom10$ CustomField10 $Data/Context/DataItem/DataItemCreateTime$ UTC Date/Time of Dataitem created $Data/Context/DataItem/DataItemCreateTimeLocal$ LocalTime Date/Time of Dataitem created $Data/Context/DataItem/LastModified$ UTC Date/Time DataItem was modified $Data/Context/DataItem/LastModifiedLocal$ Local Date/Time DataItem was modified $Data/Context/DataItem/ManagedEntity$ ManagedEntity GUID $Data/Context/DataItem/ManagedEntityDisplayName$ ManagedEntity Display name $Data/Context/DataItem/ManagedEntityFullName$ ManagedEntity Full name $Data/Context/DataItem/ManagedEntityPath$ Managed Entity Path $Data/Context/DataItem/Priority$ The Alert Priority Number (High=1,Medium=2,Low=3) $Data/Context/DataItem/Owner$ The Alert Owner $Data/Context/DataItem/RepeatCount$ The Alert Repeat Count $Data/Context/DataItem/ResolutionState$ Resolution state ID (0=New, 255= Closed) $Data/Context/DataItem/ResolutionStateLastModified$ UTC Date/Time ResolutionState was last modified $Data/Context/DataItem/ResolutionStateLastModifiedLocal$ Local Date/Time ResolutionState was last modified $Data/Context/DataItem/ResolutionStateName$ The Resolution State Name (New, Closed) $Data/Context/DataItem/ResolvedBy$ Person resolving the alert $Data/Context/DataItem/Severity$ The Alert Severity ID $Data/Context/DataItem/TicketId$ The TicketID $Data/Context/DataItem/TimeAdded$ UTC Time Added $Data/Context/DataItem/TimeAddedLocal$ Local Time Added $Data/Context/DataItem/TimeRaised$ UTC Time Raised $Data/Context/DataItem/TimeRaisedLocal$ Local Time Raised $Data/Context/DataItem/TimeResolved$ UTC Date/Time the Alert was resolved $Data/Context/DataItem/WorkflowId$ The Workflow ID (GUID) $Target/Property[Type=”Notification!Microsoft.SystemCenter.AlertNotificationSubscriptionServer”/WebConsoleUrl$ The Web Console URL Target/Property[Type=”Notification!Microsoft.SystemCenter.AlertNotificationSubscriptionServer”/PrincipalName$ The principalname of the management server $Data/Recipients/To/Address/Address$ The name of the recipient (e.g. the Email alias to which the notification is addressed)