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