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‘
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‘
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
