SOLVED

ALA XML Query

Copper Contributor

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. 

12 Replies

@lpoulin 

 

How did you import the custom log, with a timestamp or with 'new line'?  
Annotation 2019-05-10 111840.png

 

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

 

 

@CliveWatson 

 

Thank you for the reply. 

 

I imported the data with new line: 

 

RAWDATA.PNGrawdata2.PNG

 

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:

 

limit1.PNG

@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

Annotation 2019-05-13 181632.png

 

Run this from here

@CliveWatson Thank you for the reply. The issue is the <serialnumber> appears multiple times within the file so when I run that query I'm getting more results then I would like.

 

It won't let me attach the file so here are the contents of one sample:

 

<?xml version="1.0" ?>
<PCInformation>
<SiteName>0566019</SiteName>
<DeviceName>BT9000</DeviceName>
<BT9000_Version>060108</BT9000_Version>
<TerminalNum>056601901</TerminalNum>
<HardwareReport>
<General>
<HardwareRecord>
<HardwareInfo>
<HardwareType>Pumps</HardwareType>
<BoardType>NORMAL PC SERIAL PORT</BoardType>
<PortNumber>4</PortNumber>
<Manufacturer>GILBARCO</Manufacturer>
</HardwareInfo>
<HardwareDetails>
<SerialNumber></SerialNumber>
<ModelNumber></ModelNumber>
<RevisionNumber></RevisionNumber>
<FirmwareVersion></FirmwareVersion>
</HardwareDetails>
</HardwareRecord>
<HardwareRecord>
<HardwareInfo>
<HardwareType>Scanner</HardwareType>
<BoardType>NORMAL PC SERIAL PORT</BoardType>
<PortNumber>11</PortNumber>
<Manufacturer>DS9208</Manufacturer>
</HardwareInfo>
<HardwareDetails>
<SerialNumber></SerialNumber>
<ModelNumber></ModelNumber>
<RevisionNumber></RevisionNumber>
<FirmwareVersion></FirmwareVersion>
</HardwareDetails>
</HardwareRecord>
<HardwareRecord>
<HardwareInfo>
<HardwareType>Price Sign</HardwareType>
<BoardType>NORMAL PC SERIAL PORT</BoardType>
<PortNumber>9</PortNumber>
<Manufacturer>NOVYC</Manufacturer>
</HardwareInfo>
<HardwareDetails>
<SerialNumber></SerialNumber>
<ModelNumber></ModelNumber>
<RevisionNumber></RevisionNumber>
<FirmwareVersion></FirmwareVersion>
</HardwareDetails>
</HardwareRecord>
<HardwareRecord>
<HardwareInfo>
<HardwareType>Debit device/Pinpad</HardwareType>
<BoardType>NORMAL PC SERIAL PORT</BoardType>
<PortNumber>8</PortNumber>
<Manufacturer>INGENICO</Manufacturer>
</HardwareInfo>
<HardwareDetails>
<SerialNumber>00000000/62000029/ING10000</SerialNumber>
<ModelNumber>LANE3000</ModelNumber>
<RevisionNumber> </RevisionNumber>
<FirmwareVersion>30.01</FirmwareVersion>
</HardwareDetails>
</HardwareRecord>
<HardwareRecord>
<HardwareInfo>
<HardwareType>CRINDs</HardwareType>
<BoardType>NORMAL PC SERIAL PORT</BoardType>
<PortNumber>6</PortNumber>
<Manufacturer>GILBARCO EMV</Manufacturer>
</HardwareInfo>
<HardwareDetails>
<SerialNumber></SerialNumber>
<ModelNumber></ModelNumber>
<RevisionNumber></RevisionNumber>
<FirmwareVersion></FirmwareVersion>
</HardwareDetails>
</HardwareRecord>
</General>
<Printer>
<HardwareType>Receipt Printer</HardwareType>
<BoardType>NORMAL PC SERIAL PORT</BoardType>
<PortNumber>7</PortNumber>
<Manufacturer>TMT20</Manufacturer>
</Printer>
<PinPad>
<HardwareType>Pinpad </HardwareType>
<BoardType>NORMAL PC SERIAL PORT</BoardType>
<PortNumber>8</PortNumber>
<Manufacturer>INGENICO</Manufacturer>
<SerialNumber>00000000/62000029/ING10000</SerialNumber>
<ModelNumber>LANE3000</ModelNumber>
<RevisionNumber> </RevisionNumber>
<FirmwareVersion>30.01</FirmwareVersion>
</PinPad>
<Pumps>
<PumpRecord>
<PumpNumber>1</PumpNumber>
<PumpSerialNumber>2830-030304040001</PumpSerialNumber>
<PumpModelNumber>40</PumpModelNumber>
<PumpRevisionNumber>000000</PumpRevisionNumber>
</PumpRecord>
<PumpRecord>
<PumpNumber>2</PumpNumber>
<PumpSerialNumber>2830-030304040001</PumpSerialNumber>
<PumpModelNumber>40</PumpModelNumber>
<PumpRevisionNumber>000000</PumpRevisionNumber>
</PumpRecord>
<PumpRecord>
<PumpNumber>3</PumpNumber>
<PumpSerialNumber>3040030304040001</PumpSerialNumber>
<PumpModelNumber>40</PumpModelNumber>
<PumpRevisionNumber>072814</PumpRevisionNumber>
</PumpRecord>
</Pumps>
<AFDs>
<AFDRecord>
<AFDNumber>1</AFDNumber>
<AFDSerialNumber>0002061101704345</AFDSerialNumber>
<AFDModelNumber>SPOT M3</AFDModelNumber>
<AFDRevisionNumber>01.07</AFDRevisionNumber>
</AFDRecord>
<AFDRecord>
<AFDNumber>2</AFDNumber>
<AFDSerialNumber>0002061101704837</AFDSerialNumber>
<AFDModelNumber>SPOT M3</AFDModelNumber>
<AFDRevisionNumber>1.07</AFDRevisionNumber>
</AFDRecord>
<AFDRecord>
<AFDNumber>3</AFDNumber>
<AFDSerialNumber></AFDSerialNumber>
<AFDModelNumber></AFDModelNumber>
<AFDRevisionNumber></AFDRevisionNumber>
</AFDRecord>
</AFDs>
</HardwareReport>
<SystemReport>
<General>
<PCModel>HP RP9 G1 AiO Retail System, </PCModel>
<SerialNumber>2UA8212H0N</SerialNumber>
</General>
<Processors>
<NumberOfProcessors>4</NumberOfProcessors>
<ProcessorType>Intel Pentium II</ProcessorType>
<ProcessorRevision>40457</ProcessorRevision>
</Processors>
<OperatingSystem>
<Platform>Windows 8</Platform>
<BuildNumber>9200</BuildNumber>
<ServicePack>0.0</ServicePack>
</OperatingSystem>
<Memory>
<PageSize>4096 Bytes</PageSize>
<TotalPhysical>16263 MB</TotalPhysical>
<AvailablePhysical>14154 MB</AvailablePhysical>
<TotalVirtual>2047 MB</TotalVirtual>
<AvailableVirtual>1513 MB</AvailableVirtual>
</Memory>
<Disk>
<TotalDiskSpace>115486 MB</TotalDiskSpace>
<FreeDiskSpace>13158 MB</FreeDiskSpace>
<UserDiskSpace>13158 MB</UserDiskSpace>
</Disk>
<NetworkAdapter>
<ActiveLANName>Intel(R) Ethernet Connection </ActiveLANName>
</NetworkAdapter>
</SystemReport>
<TimeZone>
<TimeZone>(UTC-05:00) Eastern Time (US &amp; Canada)</TimeZone>
<Day>2</Day>
<Month>3</Month>
<Bias>-60</Bias>
<DSTDisable>TRUE</DSTDisable>
</TimeZone>
<LastBoot>2019-05-07 09:38:13</LastBoot>
</PCInformation>

Hello @lpoulin

 

Is it this line? <SerialNumber>00000000/62000029/ING10000</SerialNumber>  - if its not what does it look like?.

Is the serial number always the same format, or occurs so many lines after a pinpad entry?

 

If it is then the Parse line could be changed to: 

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>000000" intSerialNumber "</SerialNumber>" *
| project Date, Computer, SerialNumber = intSerialNumber, Type

What I'm trying to find is something unique in the line or a pattern, that doesn't occur for any of the other serial numbers.  So if they always started with "<serialnumber>000000" we could find that, and add them back to the output with this amended final line

| project Date, Computer, SerialNumber = strcat("000000","",intSerialNumber), Type

 

 

 

 

@CliveWatson 

 

Thank you again for the help. I've looked into it and I can't guarantee the serial number will always start the same and the row number in the file is dependent on the amount of equipment connected which can vary.

 

However the row number of the serial number I want will always be 5 rows below the <PinPad> row. Is it possible to generate a query based off this? 

best response confirmed by lpoulin (Copper Contributor)
Solution

@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

@CliveWatson 

 

Thank you so much for seeing this through and thinking of different ways to tackle the problem. I wasn't aware of the next function but that is exactly what I needed. Your code works perfect once I accounted for the case sensitivity and changed <serialnumber> to <SerialNumber>

@lpoulin 

 

Whoops, sorry about the typo :) 

I'm really pleased this works, and it took me a while to remember that 'next' was an option.

 

Thanks Clive

@CliveWatson 

 

Hello,

 

I have one more question for you if you are still available. I'm looking to add a column to my query that does not exist inside the <PinPad>. I'm so close but just need them to display on the same row. It's the Bulloch_Version. How do I join them since I am getting false flags?

 

PCINFO_CL
| serialize
| extend theSerialRow = next(RawData,5)
| extend theModelNumber = next(RawData,6)
| extend thefirmwareRow = next(RawData,8)
| extend theBullochVersion = RawData
| parse theBullochVersion with * "<BT9000_Version>" intBullochVersion "</BT9000_Version>" *
| extend Bulloch_Version = strcat(substring(intBullochVersion,0,2),".",substring(intBullochVersion,2,2),".",substring(intBullochVersion,4,2))
| parse theSerialRow with * "<SerialNumber>" intSerialNumber "</SerialNumber>" *
| extend PinPad_SerialNumber = iif(RawData has "<PinPad>", strcat(intSerialNumber),"false")
| parse theModelNumber with * "<ModelNumber>" intModelNumber "</ModelNumber>" *
| extend PinPad_ModelNumber = iif(RawData has "<PinPad>", strcat(intModelNumber),"false")
| parse thefirmwareRow with * "<FirmwareVersion>" intFirmwareVersion "</FirmwareVersion>" *
| extend PinPad_FirmwareVersion = iif(RawData has "<PinPad>", strcat(intFirmwareVersion),"false")
| where PinPad_FirmwareVersion !="false" or PinPad_ModelNumber !="false" or PinPad_SerialNumber !="false" or RawData contains "<BT9000_Version>"
| summarize arg_max(Date_Stamp=TimeGenerated, Bulloch_Version) by Province=substring(Computer, 5, 2), Outlet=substring(Computer, 7, 5), Terminal=substring(Computer, 12, 2), PinPad_ModelNumber, PinPad_SerialNumber, PinPad_FirmwareVersion

hello @lpoulin 

 

I'm not sure I understand the ask "just need them to display on the same row. It's the Bulloch_Version. How do I join them" 

So you have successfully created a column called "Bulloch Version" and you want to join that data to what?  Can you use MS Paint or something to show me an example of what you need the row or column to look like?

@CliveWatson 

 

Sorry for the confusion. I would like my report to only have two rows. I added a new column for Bulloch_Version but it is displaying on its own row instead of joining with the PinPad information. I get ... for some reason. The information comes from the same pcinfo.xml file. I highlighted in yellow the values that I would I like to move. Please see pcinforeport1.png for the original and finalpcinfo.png for what I want it to look like. 

1 best response

Accepted Solutions
best response confirmed by lpoulin (Copper Contributor)
Solution

@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

View solution in original post