locked
Event Log Advanced XML Query with % RRS feed

  • Question

  • Hi,

    I'm trying to filter the creation and deletion of an AD OU object

    This is the XML code of the event:

    - <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
    - <System>
      <Provider Name="Microsoft-Windows-Security-Auditing" Guid="{54849625-5478-4994-A5BA-3E3B0328C30D}" /> 
      <EventID>4662</EventID> 
      <Version>0</Version> 
      <Level>0</Level> 
      <Task>14080</Task> 
      <Opcode>0</Opcode> 
      <Keywords>0x8020000000000000</Keywords> 
      <TimeCreated SystemTime="2012-03-30T17:00:12.466551000Z" /> 
      <EventRecordID>924274805</EventRecordID> 
      <Correlation /> 
      <Execution ProcessID="564" ThreadID="3272" /> 
      <Channel>Security</Channel> 
      <Computer>WSDC12.MYDOMAIN.local</Computer> 
      <Security /> 
      </System>
    - <EventData>
      <Data Name="SubjectUserSid">S-1-5-21-1987142990-190398333-4244032772-14478</Data> 
      <Data Name="SubjectUserName">adminuser</Data> 
      <Data Name="SubjectDomainName">MYDOMAIN</Data> 
      <Data Name="SubjectLogonId">0x2b8e408d</Data> 
      <Data Name="ObjectServer">DS</Data> 
      <Data Name="ObjectType">%{bf967aa5-0de6-11d0-a285-00aa003049e2}</Data> 
      <Data Name="ObjectName">%{976c46f5-2d50-40f7-a435-4a2a27ab0f50}</Data> 
      <Data Name="OperationType">Object Access</Data> 
      <Data Name="HandleId">0x0</Data> 
      <Data Name="AccessList">%%7680</Data> 
      <Data Name="AccessMask">0x1</Data> 
      <Data Name="Properties">%%7680 {bf967aa5-0de6-11d0-a285-00aa003049e2}</Data> 
      <Data Name="AdditionalInfo">ou=TTTTTTTTTT,ou=QQQQQQQQQQ,OU=XTESTX,OU=test,DC=mydomain,DC=local</Data> 
      <Data Name="AdditionalInfo2">%{58650b59-da2e-425f-b667-7085185b522d}</Data> 
      </EventData>
      </Event>

    ---------------------------

    My filter is

    <QueryList>
      <Query Id="0" Path="Security">
        <Select Path="Security">
    *[System[(EventID='4662')]] 
    and
    *[EventData[Data[@Name ='ObjectType'] and (Data='%{bf967aa5-0de6-11d0-a285-00aa003049e2}')]] 
    and
    *[EventData[Data[@Name ='AccessList'] and (Data='%%7680')]]
    </Select>
      </Query>
    </QueryList>

    ---------------------------

    It should be pretty straight forward, but unfortunately  "and *[EventData[Data[@Name ='AccessList'] and (Data='%%7680')]]" does NOT work, and does not return anything; if I remove it, I got almost what I want but I really would like to filter only %%7680

    I believe the %% is causing issue, but cannot find the fix

    bf967aa5-0de6-11d0-a285-00aa003049e2 is the Schema-GUID for an organizational unit
    %%7680 translate to "Create Child"

    TIA


    Cyreli


    • Edited by Cyreli Sunday, April 1, 2012 1:55 AM
    Saturday, March 31, 2012 4:44 AM

Answers

All replies

  • YOu need to use an XPath expression to match a field in the Data.

    It would be someting like //Data[@Name='AccessList') and text()='%%7680')]


    ¯\_(ツ)_/¯

    Saturday, March 31, 2012 12:58 PM
  • My Query looks like this now, but still doesn't work

    <QueryList>
      <Query Id="0" Path="Security">
        <Select Path="Security">
    			*[System[(EventID='4662')]]
    			and
    			*[EventData[Data[@Name ='ObjectType'] and (Data='%{bf967aa5-0de6-11d0-a285-00aa003049e2}')]]
    			and
    			*[EventData[Data[@Name ='SubjectUserName'] and (Data='admin')]]
    			and
    			*[EventData[Data[@Name='AccessList'] and (text='%%7680')]]
    		</Select>
      </Query>
    </QueryList>
    The query seems to be valid but I cannot find any reference to text= for eventlog filtering, it seems that the key word to use is Data=



    Cyreli


    • Edited by Cyreli Saturday, March 31, 2012 6:02 PM
    Saturday, March 31, 2012 3:23 PM
  • You won't find an yXPath in teh eventlog documents other thatn to say that we use XPAth queries that return a single value.

    It is not "text" it is an XPath function that returns the text node value whch you are trying to query for a match in value.

    It is text()='<some text>'

    You lost teh parens.


    ¯\_(ツ)_/¯

    Saturday, March 31, 2012 6:32 PM
  • Jrv, sorry I forgot to mention that text()='%%7680' errors out

    Cyreli

    Saturday, March 31, 2012 6:33 PM
  • Jrv, sorry I forgot to mention that text()='%%7680' errors out

    Cyreli

    Either they don't support that syntax or something else.

    This XPAth works on that XML.

    //EventData/Data[@Name='AccessList' and text()='%%7680']

    HOw to translate that into EventLog query syntaxx which is a shorthand.  Maybe it is an XQuery extension?

    Try this:

    /Event/EventData/Data[@Name='AccessList' and text()='%%7680']



    ¯\_(ツ)_/¯

    Saturday, March 31, 2012 7:12 PM
  • This is also possible

    *[EventData[Data='%%7680'


    ¯\_(ツ)_/¯

    Saturday, March 31, 2012 7:15 PM
  • JRV, I've tried everything you mentionned, and some other variant without success, but unfortunately I've read the XPATH implementation for Eventlog has limitation

    XPath 1.0 limitations

    http://msdn.microsoft.com/en-us/library/dd996910(VS.85).aspx#limitations

    I'm not sure about the issue here, it could be possible that Accesslist is not queryable, or the %% is causing issue.

    Is there an espace character in XPATH ? I have tried \ / and < without any success

    Thank for helping


    Cyreli

    Saturday, March 31, 2012 7:32 PM
  • I have i t now.

    This works for me. As you can see it is one off from what I was assuming.

    *[EventData[Data[@Name='AccessList'] and Data='%%7680']]

    We have two separate 'wherw' clauses that are grouped hierarchically.  First we want to say "give me the nodes that have the name 'AccessList' then search those nodes for the matching value.  Not really intuitive but it works.

    Yes we only get XPAth 1.0 with limitations and Microsoft's own variations on the syntax too. XPath common syntax would have been better but...

    The damn use of the colorizing HTML control is a pain in the neck.  If you copy from a web page it will retain the style and corrupt the XML.  MS must have sent the junior programming team from Tierra Del Fuego to build that one. 


    ¯\_(ツ)_/¯


    • Edited by jrv Saturday, March 31, 2012 8:00 PM
    Saturday, March 31, 2012 7:59 PM
  • I did a bit of testing. That is XPAth 1.0 syntax with no MS changes.

    Here is the full XPATH:

    $xml.SelectNodes("//*[EventData[Data[@Name='AccessList'] and Data='%%7680']]")

    I can exract the XML version of teh eevntlog and use the exact same query.  My earlier version would bemore efficient I believe but this works.

    WIth the EventLog we get the * but not the root specifier because that is assumed.  To use the query in PowerShell XML we just preface the query with the // for the 'start at the root' and process every node.

    Mine is //Event/EventData[...

    This say start at the root but onle process against nodes that are Event/EventData.  Using a keyed index would make this much faster.

    So the MS path says. Give me all nodes where the element is an EventData where the child element is a Data and the value is %%7680


    ¯\_(ツ)_/¯

    Saturday, March 31, 2012 8:12 PM
  • JRV

    I've tried both of the query below, the first one doesn't return anything, the second returns The specified query is invalid :-(

    <QueryList> <Query Id="0" Path="Security"> <Select Path="Security"> *[EventData[Data[@Name='AccessList'] and (Data='%%7680')]] </Select> </Query> </QueryList>

    and also

    <QueryList>
      <Query Id="0" Path="Security">
        <Select Path="Security">
    			//*[EventData[Data[@Name='AccessList'] and (Data='%%7680')]]
    		</Select>
      </Query>
    </QueryList>


    Cyreli

    Saturday, March 31, 2012 9:23 PM
  • JRV

    I've tried both of the query below, the first one doesn't return anything, the second returns The specified query is invalid :-(

    <QueryList> <Query Id="0" Path="Security"> <Select Path="Security"> *[EventData[Data[@Name='AccessList'] and (Data='%%7680')]] </Select> </Query> </QueryList>

    and also

    <QueryList>
      <Query Id="0" Path="Security">
        <Select Path="Security">
    			//*[EventData[Data[@Name='AccessList'] and (Data='%%7680')]]
    		</Select>
      </Query>
    </QueryList>


    Cyreli

    Sorry Cyreli - yu can't just hang it outin space like that or it will be irellevent.

    Try this:

    <QueryList>
      <Query Id="0" Path="Security">
        <Select Path="Security">
    	*[System[(EventID='4662')]]
    	and
    	*[EventData[Data[@Name ='ObjectType'] and (Data='%{bf967aa5-0de6-11d0-a285-00aa003049e2}')]]
    	and
    	*[EventData[Data[@Name ='SubjectUserName'] and (Data='admin')]]
    	and
    	*[EventData[Data[@Name='AccessList'] and  Data='%%7680']]
    </Select>
      </Query>
    </QueryList>

    That does not throw any syntax errors and will return the expected results.


    ¯\_(ツ)_/¯

    Saturday, March 31, 2012 9:46 PM
  • JRV,

    Just to make sure, what I'm trying to do is to create a Custom View in the Event Viewer of Windows 2008R2

    I have tried 

    *[EventData[Data[@Name='AccessList'] and Data='%%7680']]

    and also

    *[EventData[Data[@Name='AccessList'] and (Data='%%7680')]]

    it doesn't throw any error, but doesn't return any result either

    Would the %% be a issue ?


    Cyreli

    Saturday, March 31, 2012 11:17 PM
  • I pasted the following into the XML designer of the Event Log Custom View designer.

    <QueryList>
      <Query Id="0" Path="Security">
        <Select Path="Security">
    	*[System[(EventID='4662')]]
    	and
    	*[EventData[Data[@Name ='ObjectType'] and (Data='%{bf967aa5-0de6-11d0-a285-00aa003049e2}')]]
    	and
    	*[EventData[Data[@Name ='SubjectUserName'] and (Data='admin')]]
    	and
    	*[EventData[Data[@Name='AccessList'] and  Data='%%7680']]
    </Select>
      </Query>
    </QueryList>

    It works exactly as designed to work with no error.

    Just copy it and paste all of it into the XML designer window.

    If you get an error then you are pasting in something wrong.


    ¯\_(ツ)_/¯

    Sunday, April 1, 2012 12:01 AM
  • Still doesn't work. I do not know what to think !

    Here is the full XML code

    - <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
    - <System>
      <Provider Name="Microsoft-Windows-Security-Auditing" Guid="{54849625-5478-4994-A5BA-3E3B0328C30D}" /> 
      <EventID>4662</EventID> 
      <Version>0</Version> 
      <Level>0</Level> 
      <Task>14080</Task> 
      <Opcode>0</Opcode> 
      <Keywords>0x8020000000000000</Keywords> 
      <TimeCreated SystemTime="2012-03-31T02:09:57.389737600Z" /> 
      <EventRecordID>925388791</EventRecordID> 
      <Correlation /> 
      <Execution ProcessID="564" ThreadID="16444" /> 
      <Channel>Security</Channel> 
      <Computer>WSDC12.MYDOMAIN.local</Computer> 
      <Security /> 
      </System>
    - <EventData>
      <Data Name="SubjectUserSid">S-1-5-21-1987142990-190398333-4244032772-14478</Data> 
      <Data Name="SubjectUserName">admin</Data> 
      <Data Name="SubjectDomainName">MYDOMAIN</Data> 
      <Data Name="SubjectLogonId">0x2b8e408d</Data> 
      <Data Name="ObjectServer">DS</Data> 
      <Data Name="ObjectType">%{bf967aa5-0de6-11d0-a285-00aa003049e2}</Data> 
      <Data Name="ObjectName">%{f8df1460-7d6d-4149-b7cf-3ddeee5297ef}</Data> 
      <Data Name="OperationType">Object Access</Data> 
      <Data Name="HandleId">0x0</Data> 
      <Data Name="AccessList">%%7680</Data> 
      <Data Name="AccessMask">0x1</Data> 
      <Data Name="Properties">%%7680 {bf967aa5-0de6-11d0-a285-00aa003049e2}</Data> 
      <Data Name="AdditionalInfo">ou=kkkkkkkk,ou=YYYYYYYY,OU=test,DC=MYDOMAIN,DC=local</Data> 
      <Data Name="AdditionalInfo2">%{9dda7261-73d4-4e88-ad35-ed7275326afe}</Data> 
      </EventData>
      </Event>

    I have tried to filter with every arribute in the list above, they all work except AccessList and Properties, the only thing they have in common is the %%


    Cyreli

    Sunday, April 1, 2012 1:53 AM
  • That is not a query itis an event records XML.


    ¯\_(ツ)_/¯

    Sunday, April 1, 2012 2:28 AM
  • I know that, this is just to give you more info.

    Quoting myself " I have tried to filter with every attribute in the list above, they all work except AccessList and Properties, the only thing they have in common is the %% "


    Cyreli

    Sunday, April 1, 2012 2:31 AM
  • Try it this way - we will encode the percent signs in the query,

    <QueryList>
      <Query Id="0" Path="Security">
        <Select Path="Security">
    	*[System[(EventID='4662')]]
    	and
    	*[EventData[Data[@Name ='ObjectType'] and (Data='%{bf967aa5-0de6-11d0-a285-00aa003049e2}')]]
    	and
    	*[EventData[Data[@Name ='SubjectUserName'] and (Data='admin')]]
    	and
    	*[EventData[Data[@Name='AccessList'] and  Data='&#37;&#37;7680']]
    </Select>
      </Query>
    </QueryList>


    ¯\_(ツ)_/¯

    Sunday, April 1, 2012 2:46 AM
  • Well it all seems to work for me so I am lost as to what your issue is.


    ¯\_(ツ)_/¯

    Sunday, April 1, 2012 2:56 AM
  • I was very hopeful I was when I saw your last post with the "&#37;&#37;" I really thought that was it.

    So I copy/paste your query in the eventlog, and what a disappointment, when I ran the query, nothing was returned, and when I went back to the Query Editor and the "&#37;&#37;" where replaced by "%%" :-(

    Anyway thank you for not giving up on this one 


    Cyreli



    • Edited by Cyreli Sunday, April 1, 2012 7:22 AM
    Sunday, April 1, 2012 2:58 AM
  • This exact syntax works evenwith compound statements.  You have to be absolutely careful of the form.

    <QueryList>
      <Query Id="0" Path="Security">
        <Select Path="Security">
    	*[System[(EventID='5447')]]
    	and
    	*[EventData[Data[@Name='FilterType'] and Data='%%16388']]
    	and
    	*[EventData[Data[@Name='ChangeType'] and Data='%%16385']]        
    </Select>
      </Query>
    </QueryList>

    While other versions work with XML this is the only one that works consistently witk teh XPAth 1 .0 that is being used by the Eveent Log.

    A test can be made by just using one element:

    *[EventData[Data[@Name='ChangeType'] and Data='%%16385']]

    This will return all events with a ChangeType field that contains '%%16385' .  Substitute your value and try it to see that it works.

    Be careful to nte where the braces fall.  If they are not grouped correctly the filter will compile but it will return nothing.  A syntax error wil lindicate that you have copied this incorrectly.

    You cannot paste from a web page into the filter editor or you might end up with a corrupt filter.  Paste into notepad first then copy and paste into the filter editor.


    ¯\_(ツ)_/¯

    • Proposed as answer by jrv Sunday, April 1, 2012 9:20 AM
    Sunday, April 1, 2012 8:40 AM
  • The Eventlog XPah supports at least one alternate form:

    */EventData/Data[@Name='ChangeType']='%%16385'

    This drops the 'and' and the node specifier and just does a compound '='.  This form may be more readable,

    Just add 'and' and 'or' to chain the logic.


    ¯\_(ツ)_/¯

    Sunday, April 1, 2012 9:12 AM
  • A note about Eventlog extractions.  You can dump the eventlog to XML and then use XPath too query the file. This works very well in PowerShell and allows us to use full XPath 2.0 syntaxes. It also allows us to do the string substitution so we can query by name rather than substitution index (%%12345 is an index into the Provider help file).


    ¯\_(ツ)_/¯


    • Edited by jrv Sunday, April 1, 2012 9:21 AM
    Sunday, April 1, 2012 9:20 AM
  • I believe I found something very interesting. I've copy/paste your query, and modified it in Notepad. See below

    #WORKS <QueryList> <Query Id="0" Path="Security"> <Select Path="Security"> *[System[(EventID='5136')]] and *[EventData[Data[@Name='DSType'] and Data='%%14676']] </Select> </Query> </QueryList>

    ----------- # WORKS <QueryList> <Query Id="0" Path="Security"> <Select Path="Security"> *[System[(EventID='5136')]] and *[EventData[Data[@Name='OperationType'] and Data='%%14674']] </Select> </Query> </QueryList>

    --------------------- #DOES NOT WORK <QueryList> <Query Id="0" Path="Security"> <Select Path="Security"> *[System[(EventID='4662')]] and *[EventData[Data[@Name='AccessList'] and Data='%%7680']] </Select> </Query> </QueryList>



    Cyreli



    • Edited by Cyreli Sunday, April 1, 2012 4:06 PM
    Sunday, April 1, 2012 4:03 PM
  • Sorry - I doin't have any event log entries with those elements on my test system.

    I see no reaon for the one not working except that yu may have gotten garbage into the query editor somehow.

    Be sure to copy an dpaste only from notepad or illegal characters can screw up you query text.


    ¯\_(ツ)_/¯

    Sunday, April 1, 2012 4:58 PM
  • I've done this I believe now more than 50 times. As mentioned before I copied paste your query 3 times, in 3 custom filters, and just modified what was necessary. 2 out of 3 works ! So no typo possible.

    Seems to be a bug for me so far.

    If you want to test this on your system, you will need a Windows 2008R2 with audit enable, and create a OU, that will trigger an eventid 4662, with an AccessList = %%7680


    Cyreli

    Sunday, April 1, 2012 5:05 PM
  • Mo - you need to paste it into notepad first then copy it fro notepad and paste into the EL CML edit box.  If you don't do this illegal cahracters can be pasted from the web copy.  You willnot see them but they will be there.  They can alter any string. The%% is very likely to get decorated with invisible junk.  Notepad does not support these caharacters and will strip them.  The dit box is an HTML comtrol so it wil lmaintain the HTML.

    Try this.  Copy the foillowing line and paste into the edit box then paste it into notepad. (Yes bcakwards to show the error)

    Some text - BOLD TEXT - regular text.

    You should see that the text remains bolded in teh edit box but not in notepad.  The HTML that set teh bold styles can getr included in your query and it will not be visible.  The tags are not part of teh QueryList schema and will be invisible in the editor but can still be there.  Once the tags are included the query will behave oddly.  It took me many tries to realize that this was part of the probelem with teh queries.

    This thread should be moved to the Server Platform forum as it really has nothing to do with scripting.  I just pursued it because the same XML queries can be used in PowerShell and the XPath for this would be helpful to some scripters.

    If ytou have specific issues with a prticular query I recommend taking that one query to the System Management FOrum and letting the SM people try to reproduce you issue more completely.  You might finsd someone with a test system that has the events that you are having an issue with.

    http://social.technet.microsoft.com/Forums/en-US/winserverManagement/threads


    ¯\_(ツ)_/¯

    Sunday, April 1, 2012 5:32 PM
  • JRV, let's forget about the copy/paste issue. As said previously, I did the exact same action 3 times, it works 2 out of 3, and fails in every case when I tried to filter AccessList. I've done more testing and I'm always able to filter %% except when applied to AccessList.

    Thank you for your hard work trying to solve this issue. Unfortunately, this is not yet fixed


    Cyreli


    • Edited by Cyreli Sunday, April 1, 2012 5:49 PM
    Sunday, April 1, 2012 5:48 PM
    • Marked as answer by Cyreli Tuesday, April 3, 2012 2:53 AM
    Tuesday, April 3, 2012 2:53 AM
  • The answer is here:

    http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/thread/bd136cf0-fb9e-48a1-ae2f-3cd4290ab973


    Cyreli

    That is an interestin bug in the event records for that provider.  I would have never figured it out because I do not have those event records to test with.  Dumping the XML for th eevent would hav eshown that.  The techique used her ro analyze it is good to know for future similar issues.

    Good find.

    I hope you or someone has notified Microsoft about this.  It should be posted on Connect.

    Thanks for the update.


    ¯\_(ツ)_/¯


    • Edited by jrv Tuesday, April 3, 2012 3:19 AM
    Tuesday, April 3, 2012 3:19 AM