{"id":1080,"date":"2014-11-25T14:16:34","date_gmt":"2014-11-25T12:16:34","guid":{"rendered":"http:\/\/marwin.e-blog.cz\/?p=1080"},"modified":"2015-02-13T12:47:27","modified_gmt":"2015-02-13T10:47:27","slug":"ms-scom-audit-reports","status":"publish","type":"post","link":"https:\/\/svobodma.cz\/?p=1080","title":{"rendered":"MS SCOM 2007 R2 – Audit Reports"},"content":{"rendered":"

\"scom2007r2\"<\/a><\/p>\n

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 \u00a0and better \u00a0to know about PL\/SQL.<\/p>\n

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 <\/strong>located\u00a0in OperationManagerAC database.<\/p>\n

 <\/p>\n

Table join for EventID\u00a0<\/strong>on Server 2008 and newer<\/strong><\/p>\n

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

FROM<\/strong>
\n (SELECT * FROM AdtServer.dvAll WHERE EventId = 4624) AS li LEFT OUTER JOIN<\/strong>
\n (SELECT * FROM AdtServer.dvAll WHERE EventId = 4634) AS lo <\/strong>
\n on li.String01 = lo.String01<\/strong><\/p>\n

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

 <\/p>\n

\"SCOMMSSQLREPORT2008\"<\/a><\/p>\n

This description of strings could you little help, if your ACS agent are running on Windows Server 2008 and newer.:<\/strong><\/p>\n

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

—————————————<\/p>\n

, String14 as String14
\n, String15 as String15
\n, String16 as String16
\n, String17 as String17
\n, String18 as String18
\n, String19 as String19
\n, String20 as String20
\n, String21 as String21
\n, String22 as String22<\/p>\n

= \u00a0is default \u00a0-> n\/a<\/p>\n

 <\/p>\n

Table join for EventID on Server 2003<\/strong><\/p>\n

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

FROM <\/strong>
\n (SELECT * FROM AdtServer.dvAll WHERE EventId = 528) AS li LEFT OUTER JOIN<\/strong>
\n (SELECT * FROM AdtServer.dvAll WHERE EventId = 538) AS lo ON<\/strong>
\n li.PrimaryLogonId = lo.ClientLogonId<\/strong><\/p>\n

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

\"SCOMMSSQLREPORT\"<\/a><\/p>\n

This description of Strings could you little help, if your agent are running on Windows Server 2003 and older:<\/strong><\/p>\n

, String01 as LogonType
\n, String02 as\u00a0LogOnFrom
\n, String03 as\u00a0String03
\n, String04 as\u00a0TargetServerNetBiosName \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 , String05 as\u00a0LogonGuid \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 , String06 as CallerProcessID<\/p>\n

, String07 as –
\n, String08 as\u00a0LogonProcess
\n, String09 as\u00a0AuthPackage<\/p>\n

, String10 as String10
\n, String11 as String11
\n, String12 as String12
\n, String13 as String13
\n, String14 as String14
\n, String15 as String15
\n, String16 as String16
\n, String17 as String17
\n, String18 as String18
\n, String19 as String19
\n, String20 as String20
\n, String21 as String21
\n, String22 as String22<\/p>\n","protected":false},"excerpt":{"rendered":"

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 \u00a0and better \u00a0to know about PL\/SQL. … Cel\u00fd p\u0159\u00edsp\u011bvek →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-1080","post","type-post","status-publish","format-standard","hentry","category-system-center"],"_links":{"self":[{"href":"https:\/\/svobodma.cz\/index.php?rest_route=\/wp\/v2\/posts\/1080","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/svobodma.cz\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/svobodma.cz\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/svobodma.cz\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/svobodma.cz\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1080"}],"version-history":[{"count":14,"href":"https:\/\/svobodma.cz\/index.php?rest_route=\/wp\/v2\/posts\/1080\/revisions"}],"predecessor-version":[{"id":1083,"href":"https:\/\/svobodma.cz\/index.php?rest_route=\/wp\/v2\/posts\/1080\/revisions\/1083"}],"wp:attachment":[{"href":"https:\/\/svobodma.cz\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1080"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/svobodma.cz\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1080"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/svobodma.cz\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1080"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}