May 09 2019
06:23 AM
- last edited on
Apr 07 2022
05:46 PM
by
TechCommunityAP
May 09 2019
06:23 AM
- last edited on
Apr 07 2022
05:46 PM
by
TechCommunityAP
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.
May 10 2019 03:28 AM
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
May 13 2019 08:58 AM
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:
May 13 2019 10:17 AM
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
May 13 2019 10:54 AM
@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 & Canada)</TimeZone>
<Day>2</Day>
<Month>3</Month>
<Bias>-60</Bias>
<DSTDisable>TRUE</DSTDisable>
</TimeZone>
<LastBoot>2019-05-07 09:38:13</LastBoot>
</PCInformation>
May 13 2019 12:20 PM
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
May 14 2019 12:28 PM
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?
May 15 2019 04:45 AM
Solution
How about this?
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 |
May 15 2019 08:56 AM
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>
May 15 2019 09:00 AM
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
May 22 2019 08:32 AM
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?
May 23 2019 05:50 AM
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?
May 23 2019 07:55 AM - edited May 23 2019 08:17 AM
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.
May 15 2019 04:45 AM
Solution
How about this?
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 |