Forum Discussion

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> <Pi...

May 15, 2019

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

lpoulin

Copper Contributor

May 14, 2019

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?

CliveWatson

Former Employee

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

lpoulin
Copper Contributor
May 23, 2019
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.
CliveWatson
Former Employee
May 23, 2019
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?
lpoulin
Copper Contributor
May 22, 2019
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
CliveWatson
Former Employee
May 15, 2019
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
lpoulin
Copper Contributor
May 15, 2019
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>