Forum Discussion

lpoulin's avatar
lpoulin
Copper Contributor
May 09, 2019
Solved

ALA XML Query

Hello,

I've added a custom log to ALA and I need some assistance to write a query for it. 

The file is in XML format. Here is a sample of my PCINFO.XML file:

<PCInformation>

<HardwareReport>

<PinPad>

<Manufacturer>INGENICO</Manufacturer>

<SerialNumber>12345</SerialNumber>

<ModelNumber>LANE3000</ModelNumber>

<FirmwareVersion>1.00</FirmwareVersion>

</PinPad>

<Printer>

<Manufacturer>EPSON</Manufacturer>

<SerialNumber>54321</SerialNumber>

<ModelNumber>TMT20</ModelNumber>

<FirmwareVersion>2.00</FirmwareVersion>

</PinPad>

</HardwareReport>

</PCInformation>

 

Let's say I am looking for the serial number of the PinPad, how do I call that specific value? I can't use | where RawData has "SerialNumber" because this string exist in multiple places. It looks like I could potentially use parse_xml() however I've looked through the help file and have been unsuccessful https://docs.microsoft.com/en-us/azure/kusto/query/parse-xmlfunction

 

Thank you for taking the time to read my post. 

azure monitor
Query Language
  • CliveWatson's avatar
    CliveWatson
    May 15, 2019

    lpoulin 

     

    How about this?

    Link

     

    let dummyData = datatable(Date:datetime, Computer:string, RawData:string, Type:string)
[
    datetime(now) , "Server0001" ,"<PinPad>","PCinfo_CL",
    datetime(now) , "Server0001" ,"11111","PCinfo_CL",
    datetime(now) , "Server0001" ,"22222</SerialNumber>","PCinfo_CL",
    datetime(now) , "Server0001" ,"33333</SerialNumber>","PCinfo_CL",
    datetime(now) , "Server0001" ,"44444</SerialNumber>","PCinfo_CL",
    datetime(now) , "Server0001" ,"<serialnumber>00000/1111111/3333333</SerialNumber>","PCinfo_CL",
    datetime(now) , "Server0001" ,"55555</SerialNumber>","PCinfo_CL",
    datetime(now) , "Server0002" ,"<PinPad>","PCinfo_CL",
    datetime(now) , "Server0002" ,"11111","PCinfo_CL",
    datetime(now) , "Server0002" ,"22222</SerialNumber>","PCinfo_CL",
    datetime(now) , "Server0002" ,"33333</SerialNumber>","PCinfo_CL",
    datetime(now) , "Server0002" ,"44444</SerialNumber>","PCinfo_CL",
    datetime(now) , "Server0002" ,"<serialnumber>22222/1111111/3333333</SerialNumber>","PCinfo_CL",
    datetime(now) , "Server0002" ,"44444</SerialNumber>","PCinfo_CL",
    datetime(now) , "Server0003" ,"<PinPad>","PCinfo_CL",
    datetime(now) , "Server0003" ,"11111","PCinfo_CL",
    datetime(now) , "Server0003" ,"222222</SerialNumber>","PCinfo_CL",
    datetime(now) , "Server0003" ,"333333</SerialNumber>","PCinfo_CL",
    datetime(now) , "Server0003" ,"44444</SerialNumber>","PCinfo_CL",
    datetime(now) , "Server0003" ,"<serialnumber>33333/1111111/3333333</SerialNumber>","PCinfo_CL",
];
dummyData
| serialize 
| extend theSerialRow = next(RawData,5)
| parse theSerialRow with * "<serialnumber>" intSerialNumber "</SerialNumber>" *
| extend hit = iif(RawData has "<PinPad>", strcat(RawData,"",intSerialNumber),"false")
| where hit !="false"
| summarize by hit, Computer

    Which looks like, this on your system (I think)

    PCINFO_CL
| serialize 
| extend theSerialRow = next(RawData,5)
| parse theSerialRow with * "<serialnumber>" intSerialNumber "</SerialNumber>" *
| extend hit = iif(RawData has "<PinPad>", strcat(RawData,"",intSerialNumber),"false")
| where hit !="false"
| summarize by hit, Computer

     

    Output is like this:

     
    <PinPad>00000/1111111/3333333 Server0001  
    <PinPad>22222/1111111/3333333 Server0002  
    <PinPad>33333/1111111/3333333 Server0003

12 Replies

  • CliveWatson's avatar
    CliveWatson
    Former Employee
    May 10, 2019

    lpoulin 

     

    How did you import the custom log, with a timestamp or with 'new line'?  

     

    I don't see any timestamp mentioned, so I assumed newline was used?  If so wont you have a lot of records with each line of XMl in it's own Rawdata field?

     

    What does the Rawdata field look like?

    <insert your log name>_CL
| project RawData

    Also what does the whole schema look like?

     

    <insert your log name>_CL
| limit 1

     

     

    • lpoulin's avatar
      lpoulin
      Copper Contributor
      May 13, 2019

      CliveWatson 

       

      Thank you for the reply. 

       

      I imported the data with new line: 

       

       

      As you can see there are numerous counts of serial number within the custom log. I'm looking specifically for the PinPad information. PCInformation.HardwareReport.Pinpad but I don't know how to call that value

       

      Here's the limit 1:

       

      • CliveWatson's avatar
        CliveWatson
        Former Employee
        May 13, 2019

        lpoulin 

         

        How about?  I've recreated your record in a datatable, and the used PARSE to find the serial number value, was that the right one?

         

        let dummyData = datatable(Date:datetime, Computer:string, RawData:string, Type:string)
[
    datetime(now) , "Server0001" ,"<serialnumber>000000/1111111/2222222</SerialNumber>","PCinfo_CL"
];
dummyData
// look for serial number between two unique strings in the rawdata row
| parse RawData with * "<serialnumber>" intSerialNumber "</SerialNumber>" *
| project Date, Computer, SerialNumber = intSerialNumber, Type

        So in your Log Analytics it would be

         

        PCINFO_CL
// look for serial number between two unique strings in the rawdata row
| parse RawData with * "<serialnumber>" intSerialNumber "</SerialNumber>" *
| project Date, Computer, SerialNumber = intSerialNumber, Type

         

        Run this from here

Resources