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

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

    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>

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

      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

       

       

       

       

      • lpoulin's avatar
        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? 

Resources