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
CliveWatson's avatar
CliveWatson
Former Employee
May 10, 2019

lpoulin 

 

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

 

 

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

Resources