Forum Discussion

TS-noodlemctwoodle's avatar
TS-noodlemctwoodle
Brass Contributor
Jun 04, 2020
Solved

Parsing XML in Azure Sentinel

CliveWatson I wonder if you can give me some pointers for how to parse XML syslog information in Azure Sentinel?   Here is an sample of the redacted syslog message formatted into XML   05:19.0Z...
data collection
  • TS-noodlemctwoodle's avatar
    TS-noodlemctwoodle
    Jun 08, 2020

    CliveWatsonThank you very much with your help on this, your a legend.

     

    Here is the working solution based upon your suggestion :cool:

     

     

     

     

     

    print syslogmsg = '05:19.0Z Some-Server-Name Events - EventFwd [agentInfo@3401 tenantId="0" bpsId="0" tenantGUID="{00000000-0000-0000-0000-000000000000}" tenantNodePath="1\2"] <?xml version="1.0" encoding="utf-8"?><UpdateEvents><MachineInfo><AgentGUID>{00000000-0000-0000-0000-000000000000}</AgentGUID><MachineName>Some-Machine</MachineName><RawMACAddress>112233445566</RawMACAddress><IPAddress>1.1.2.3</IPAddress><AgentVersion>1.2.3.123</AgentVersion><OSName>Windows 41</OSName><TimeZoneBias>-10</TimeZoneBias><UserName>myName</UserName></MachineInfo><BrandCommonUpdater ProductName="Brand Agent" ProductVersion="1.0.0" ProductFamily="AVP"><UpdateEvent><EventID>1234</EventID><Severity>0</Severity><GMTTime>2020-00-00T06:41:02</GMTTime><ProductID>SomeName1999</ProductID><Locale>0001</Locale><Error>0</Error><Type>SomeCore</Type><Version>1234.0</Version><InitiatorID>SOMEAGENT3000</InitiatorID><InitiatorType>OnDemand</InitiatorType><SiteName>Some-Server-Name</SiteName><Description>N/A</Description></UpdateEvent></BrandCommonUpdater></UpdateEvents>'
| parse syslogmsg with * " tenantNodePath" * " " xml 
| extend xml=parse_xml(xml)
| extend MachineName =  xml.UpdateEvents.MachineInfo.MachineName
| extend IPAddress =  xml.UpdateEvents.MachineInfo.IPAddress
| where isnotempty(MachineName)
| project 
    MachineName,
    IPAddress

     

     

    Edit: Just to clean up the query I have made an adjustment to the solution as suggested by CliveWatson and Ofer :smile:

     

TS-noodlemctwoodle's avatar
TS-noodlemctwoodle
Brass Contributor
Jun 04, 2020
The raw string looks like this:
 
05:19.0Z Some-Server-Name Events - EventFwd [agentInfo@3401 tenantId="0" bpsId="0" tenantGUID="{00000000-0000-0000-0000-000000000000}" tenantNodePath="1\2"] <?xml version="1.0" encoding="utf-8"?><UpdateEvents><MachineInfo><AgentGUID>{00000000-0000-0000-0000-000000000000}</AgentGUID><MachineName>Some-Machine</MachineName><RawMACAddress>112233445566</RawMACAddress><IPAddress>1.1.2.3</IPAddress><AgentVersion>1.2.3.123</AgentVersion><OSName>Windows 41</OSName><TimeZoneBias>-10</TimeZoneBias><UserName>myName</UserName></MachineInfo><BrandCommonUpdater ProductName="Brand Agent" ProductVersion="1.0.0" ProductFamily="AVP"><UpdateEvent><EventID>1234</EventID><Severity>0</Severity><GMTTime>2020-00-00T06:41:02</GMTTime><ProductID>SomeName1999</ProductID><Locale>0001</Locale><Error>0</Error><Type>SomeCore</Type><Version>1234.0</Version><InitiatorID>SOMEAGENT3000</InitiatorID><InitiatorType>OnDemand</InitiatorType><SiteName>Some-Server-Name</SiteName><Description>N/A</Description></UpdateEvent></BrandCommonUpdater></UpdateEvents>
 
I have this KQL so far to at leastquery the computer and create a data table of just the Syslog message
 
Syslog
| where Computer contains "Some-Server-Name"
| project SyslogMessage
| extend NewField=parse_xml(SyslogMessage)
 
  • CliveWatson's avatar
    CliveWatson
    Former Employee
    Jun 05, 2020

    TS-noodlemctwoodle 

     

    SecurityEvent
| project EventData
| extend NewField=parse_xml(EventData)
| extend value=NewField.UserData
| where isnotempty(value)
| project value.RuleAndFileData.FilePath

     

    I don't have a Syslog example, but this works  

    • TS-noodlemctwoodle's avatar
      TS-noodlemctwoodle
      Brass Contributor
      Jun 05, 2020

      CliveWatson 

       

      Would you be able to assist how I might format your example for SecurityEvent into Syslog using the message example?

       

      GaryBushey 

      I looked at this documentation, although I dont fully understand the examples provided 😐

       

      I also looked at this post https://www.systemcenterautomation.com/2020/01/extracting-nested-fields-kusto/ but i haven't been able to replicate the output with the data I have

      • CliveWatson's avatar
        CliveWatson
        Former Employee
        Jun 05, 2020

        TS-noodlemctwoodle 

         

        One way maybe, if you just need a few fields would be to parse i.e.

         

        print syslogmsg = '05:19.0Z Some-Server-Name Events - EventFwd [agentInfo@3401 tenantId="0" bpsId="0" tenantGUID="{00000000-0000-0000-0000-000000000000}" tenantNodePath="1\2"] <?xml version="1.0" encoding="utf-8"?><UpdateEvents><MachineInfo><AgentGUID>{00000000-0000-0000-0000-000000000000}</AgentGUID><MachineName>Some-Machine</MachineName><RawMACAddress>112233445566</RawMACAddress><IPAddress>1.1.2.3</IPAddress><AgentVersion>1.2.3.123</AgentVersion><OSName>Windows 41</OSName><TimeZoneBias>-10</TimeZoneBias><UserName>myName</UserName></MachineInfo><BrandCommonUpdater ProductName="Brand Agent" ProductVersion="1.0.0" ProductFamily="AVP"><UpdateEvent><EventID>1234</EventID><Severity>0</Severity><GMTTime>2020-00-00T06:41:02</GMTTime><ProductID>SomeName1999</ProductID><Locale>0001</Locale><Error>0</Error><Type>SomeCore</Type><Version>1234.0</Version><InitiatorID>SOMEAGENT3000</InitiatorID><InitiatorType>OnDemand</InitiatorType><SiteName>Some-Server-Name</SiteName><Description>N/A</Description></UpdateEvent></BrandCommonUpdater></UpdateEvents>'
| parse syslogmsg with *" EventFwd [" str " tenantId="*
| project str

         

        Go to Log Analytics and run query

        str
        agentInfo@3401

         

        Is that whole string syslogmessge like in the above Print statement?

Resources