SOLVED

ALA XML Query

%3CLINGO-SUB%20id%3D%22lingo-sub-552395%22%20slang%3D%22en-US%22%3EALA%20XML%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-552395%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI've%20added%20a%20custom%20log%20to%20ALA%20and%20I%20need%20some%20assistance%20to%20write%20a%20query%20for%20it.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20file%20is%20in%20XML%20format.%20Here%20is%20a%20sample%20of%20my%20PCINFO.XML%20file%3A%3C%2FP%3E%3CP%3E%3CPCINFORMATION%3E%3C%2FPCINFORMATION%3E%3C%2FP%3E%3CP%3E%3CHARDWAREREPORT%3E%3C%2FHARDWAREREPORT%3E%3C%2FP%3E%3CP%3E%3CPINPAD%3E%3C%2FPINPAD%3E%3C%2FP%3E%3CP%3E%3CMANUFACTURER%3EINGENICO%3C%2FMANUFACTURER%3E%3C%2FP%3E%3CP%3E%3CSERIALNUMBER%3E12345%3C%2FSERIALNUMBER%3E%3C%2FP%3E%3CP%3E%3CMODELNUMBER%3ELANE3000%3C%2FMODELNUMBER%3E%3C%2FP%3E%3CP%3E%3CFIRMWAREVERSION%3E1.00%3C%2FFIRMWAREVERSION%3E%3C%2FP%3E%3CP%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CP%3E%3C%2FP%3E%3CP%3E%3CPRINTER%3E%3C%2FPRINTER%3E%3C%2FP%3E%3CP%3E%3CMANUFACTURER%3EEPSON%3C%2FMANUFACTURER%3E%3C%2FP%3E%3CP%3E%3CSERIALNUMBER%3E54321%3C%2FSERIALNUMBER%3E%3C%2FP%3E%3CP%3E%3CMODELNUMBER%3ETMT20%3C%2FMODELNUMBER%3E%3C%2FP%3E%3CP%3E%3CFIRMWAREVERSION%3E2.00%3C%2FFIRMWAREVERSION%3E%3C%2FP%3E%3CP%3E%3C%2FP%3E%3CP%3E%3C%2FP%3E%3CP%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet's%20say%20I%20am%20looking%20for%20the%20serial%20number%20of%20the%20PinPad%2C%20how%20do%20I%20call%20that%20specific%20value%3F%20I%20can't%20use%20%7C%20where%20RawData%20has%20%22SerialNumber%22%20because%20this%20string%20exist%20in%20multiple%20places.%20It%20looks%20like%20I%20could%20potentially%20use%20parse_xml()%20however%20I've%20looked%20through%20the%20help%20file%20and%20have%20been%20unsuccessful%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fparse-xmlfunction%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fparse-xmlfunction%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20taking%20the%20time%20to%20read%20my%20post.%26nbsp%3B%3C%2FP%3E%3CLINGO-LABS%20id%3D%22lingo-labs-552395%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-557659%22%20slang%3D%22en-US%22%3ERe%3A%20ALA%20XML%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-557659%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F339112%22%20target%3D%22_blank%22%3E%40lpoulin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20did%20you%20import%20the%20custom%20log%2C%20with%20a%20timestamp%20or%20with%20'new%20line'%3F%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F112913iB5896C3236C3BC53%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Annotation%202019-05-10%20111840.png%22%20title%3D%22Annotation%202019-05-10%20111840.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20don't%20see%20any%20timestamp%20mentioned%2C%20so%20I%20assumed%20%3CSTRONG%3Enewline%3C%2FSTRONG%3E%20was%20used%3F%26nbsp%3B%20If%20so%20wont%20you%20have%20a%20lot%20of%20records%20with%20each%20line%20of%20XMl%20in%20it's%20own%20Rawdata%20field%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20does%20the%20Rawdata%20field%20look%20like%3F%3C%2FP%3E%0A%3CPRE%3E%26lt%3Binsert%20your%20log%20name%26gt%3B_CL%0A%7C%20project%20RawData%20%3C%2FPRE%3E%0A%3CP%3EAlso%20what%20does%20the%20whole%20schema%20look%20like%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%26lt%3Binsert%20your%20log%20name%26gt%3B_CL%0A%7C%20limit%201%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-564066%22%20slang%3D%22en-US%22%3ERe%3A%20ALA%20XML%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-564066%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20reply.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20imported%20the%20data%20with%20new%20line%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20471px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F113166i2118607916EBB9A2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22RAWDATA.PNG%22%20title%3D%22RAWDATA.PNG%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20641px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F113167i38EAA965279CFD63%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22rawdata2.PNG%22%20title%3D%22rawdata2.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20can%20see%20there%20are%20numerous%20counts%20of%20serial%20number%20within%20the%20custom%20log.%20I'm%20looking%20specifically%20for%20the%20PinPad%20information.%20PCInformation.HardwareReport.Pinpad%20but%20I%20don't%20know%20how%20to%20call%20that%20value%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20the%20limit%201%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20573px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F113168iB12D43AC0C8A9699%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22limit1.PNG%22%20title%3D%22limit1.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-564153%22%20slang%3D%22en-US%22%3ERe%3A%20ALA%20XML%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-564153%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F339112%22%20target%3D%22_blank%22%3E%40lpoulin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20about%3F%26nbsp%3B%20I've%20recreated%20your%20record%20in%20a%20%3CSTRONG%3Edatatable%3C%2FSTRONG%3E%2C%20and%20the%20used%20%3CSTRONG%3EPARSE%3C%2FSTRONG%3E%20to%20find%20the%20serial%20number%20value%2C%20was%20that%20the%20right%20one%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3Elet%20dummyData%20%3D%20datatable(Date%3Adatetime%2C%20Computer%3Astring%2C%20RawData%3Astring%2C%20Type%3Astring)%0A%5B%0A%20%20%20%20datetime(now)%20%2C%20%22Server0001%22%20%2C%22%26lt%3Bserialnumber%26gt%3B000000%2F1111111%2F2222222%26lt%3B%2FSerialNumber%26gt%3B%22%2C%22PCinfo_CL%22%0A%5D%3B%0AdummyData%0A%2F%2F%20look%20for%20serial%20number%20between%20two%20unique%20strings%20in%20the%20rawdata%20row%0A%7C%20parse%20RawData%20with%20*%20%22%26lt%3Bserialnumber%26gt%3B%22%20intSerialNumber%20%22%26lt%3B%2FSerialNumber%26gt%3B%22%20*%0A%7C%20project%20Date%2C%20Computer%2C%20SerialNumber%20%3D%20intSerialNumber%2C%20Type%3C%2FPRE%3E%0A%3CP%3ESo%20in%20your%20Log%20Analytics%20it%20would%20be%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EPCINFO_CL%0A%2F%2F%20look%20for%20serial%20number%20between%20two%20unique%20strings%20in%20the%20rawdata%20row%0A%7C%20parse%20RawData%20with%20*%20%22%26lt%3Bserialnumber%26gt%3B%22%20intSerialNumber%20%22%26lt%3B%2FSerialNumber%26gt%3B%22%20*%0A%7C%20project%20Date%2C%20Computer%2C%20SerialNumber%20%3D%20intSerialNumber%2C%20Type%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F113176i448BE6D4E584F9B0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Annotation%202019-05-13%20181632.png%22%20title%3D%22Annotation%202019-05-13%20181632.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERun%20this%20from%20%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAA11QQU7DMBC8W%252FIfRj61lSWnHEvLJRwRQpQbQsihW2pI7OA4WJV4PE5MC2Quq13NjGanpoBd3zTHax00NtilEXRV0ywdaJVWCqYhidI1bR%252FIr7rgjX2VuNdx0Jz3h2NLP8ucs0fOkHDSz6yLc0iILflP8kVRLAWkWHfkja5t31Tkr4oRapmhLjLWajuSbjNJSHFXGrt3z%252BWN4OzpkrNzfs6UQu3cO%252FbOI3sjm6OiEIksQnTorfnoCTlsB5OuB4LXcfge3kXOvtBq39HpS0QTDlhgklgkbfgbLxEmcbEYzbx7o5eAodTfLiX%252BaTdTt1zqNyfNJGGjAQAA%26amp%3Btimespan%3DP1D%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehere%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-564186%22%20slang%3D%22en-US%22%3ERe%3A%20ALA%20XML%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-564186%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3BThank%20you%20for%20the%20reply.%20The%20issue%20is%20the%20%3CSERIALNUMBER%3E%20appears%20multiple%20times%20within%20the%20file%20so%20when%20I%20run%20that%20query%20I'm%20getting%20more%20results%20then%20I%20would%20like.%3C%2FSERIALNUMBER%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20won't%20let%20me%20attach%20the%20file%20so%20here%20are%20the%20contents%20of%20one%20sample%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CPCINFORMATION%3E%3CBR%20%2F%3E%3CSITENAME%3E0566019%3C%2FSITENAME%3E%3CBR%20%2F%3E%3CDEVICENAME%3EBT9000%3C%2FDEVICENAME%3E%3CBR%20%2F%3E%3CBT9000_VERSION%3E060108%3C%2FBT9000_VERSION%3E%3CBR%20%2F%3E%3CTERMINALNUM%3E056601901%3C%2FTERMINALNUM%3E%3CBR%20%2F%3E%3CHARDWAREREPORT%3E%3CBR%20%2F%3E%3CGENERAL%3E%3CBR%20%2F%3E%3CHARDWARERECORD%3E%3CBR%20%2F%3E%3CHARDWAREINFO%3E%3CBR%20%2F%3E%3CHARDWARETYPE%3EPumps%3C%2FHARDWARETYPE%3E%3CBR%20%2F%3E%3CBOARDTYPE%3ENORMAL%20PC%20SERIAL%20PORT%3C%2FBOARDTYPE%3E%3CBR%20%2F%3E%3CPORTNUMBER%3E4%3C%2FPORTNUMBER%3E%3CBR%20%2F%3E%3CMANUFACTURER%3EGILBARCO%3C%2FMANUFACTURER%3E%3CBR%20%2F%3E%3C%2FHARDWAREINFO%3E%3CBR%20%2F%3E%3CHARDWAREDETAILS%3E%3CBR%20%2F%3E%3CSERIALNUMBER%3E%3C%2FSERIALNUMBER%3E%3CBR%20%2F%3E%3CMODELNUMBER%3E%3C%2FMODELNUMBER%3E%3CBR%20%2F%3E%3CREVISIONNUMBER%3E%3C%2FREVISIONNUMBER%3E%3CBR%20%2F%3E%3CFIRMWAREVERSION%3E%3C%2FFIRMWAREVERSION%3E%3CBR%20%2F%3E%3C%2FHARDWAREDETAILS%3E%3CBR%20%2F%3E%3C%2FHARDWARERECORD%3E%3CBR%20%2F%3E%3CHARDWARERECORD%3E%3CBR%20%2F%3E%3CHARDWAREINFO%3E%3CBR%20%2F%3E%3CHARDWARETYPE%3EScanner%3C%2FHARDWARETYPE%3E%3CBR%20%2F%3E%3CBOARDTYPE%3ENORMAL%20PC%20SERIAL%20PORT%3C%2FBOARDTYPE%3E%3CBR%20%2F%3E%3CPORTNUMBER%3E11%3C%2FPORTNUMBER%3E%3CBR%20%2F%3E%3CMANUFACTURER%3EDS9208%3C%2FMANUFACTURER%3E%3CBR%20%2F%3E%3C%2FHARDWAREINFO%3E%3CBR%20%2F%3E%3CHARDWAREDETAILS%3E%3CBR%20%2F%3E%3CSERIALNUMBER%3E%3C%2FSERIALNUMBER%3E%3CBR%20%2F%3E%3CMODELNUMBER%3E%3C%2FMODELNUMBER%3E%3CBR%20%2F%3E%3CREVISIONNUMBER%3E%3C%2FREVISIONNUMBER%3E%3CBR%20%2F%3E%3CFIRMWAREVERSION%3E%3C%2FFIRMWAREVERSION%3E%3CBR%20%2F%3E%3C%2FHARDWAREDETAILS%3E%3CBR%20%2F%3E%3C%2FHARDWARERECORD%3E%3CBR%20%2F%3E%3CHARDWARERECORD%3E%3CBR%20%2F%3E%3CHARDWAREINFO%3E%3CBR%20%2F%3E%3CHARDWARETYPE%3EPrice%20Sign%3C%2FHARDWARETYPE%3E%3CBR%20%2F%3E%3CBOARDTYPE%3ENORMAL%20PC%20SERIAL%20PORT%3C%2FBOARDTYPE%3E%3CBR%20%2F%3E%3CPORTNUMBER%3E9%3C%2FPORTNUMBER%3E%3CBR%20%2F%3E%3CMANUFACTURER%3ENOVYC%3C%2FMANUFACTURER%3E%3CBR%20%2F%3E%3C%2FHARDWAREINFO%3E%3CBR%20%2F%3E%3CHARDWAREDETAILS%3E%3CBR%20%2F%3E%3CSERIALNUMBER%3E%3C%2FSERIALNUMBER%3E%3CBR%20%2F%3E%3CMODELNUMBER%3E%3C%2FMODELNUMBER%3E%3CBR%20%2F%3E%3CREVISIONNUMBER%3E%3C%2FREVISIONNUMBER%3E%3CBR%20%2F%3E%3CFIRMWAREVERSION%3E%3C%2FFIRMWAREVERSION%3E%3CBR%20%2F%3E%3C%2FHARDWAREDETAILS%3E%3CBR%20%2F%3E%3C%2FHARDWARERECORD%3E%3CBR%20%2F%3E%3CHARDWARERECORD%3E%3CBR%20%2F%3E%3CHARDWAREINFO%3E%3CBR%20%2F%3E%3CHARDWARETYPE%3EDebit%20device%2FPinpad%3C%2FHARDWARETYPE%3E%3CBR%20%2F%3E%3CBOARDTYPE%3ENORMAL%20PC%20SERIAL%20PORT%3C%2FBOARDTYPE%3E%3CBR%20%2F%3E%3CPORTNUMBER%3E8%3C%2FPORTNUMBER%3E%3CBR%20%2F%3E%3CMANUFACTURER%3EINGENICO%3C%2FMANUFACTURER%3E%3CBR%20%2F%3E%3C%2FHARDWAREINFO%3E%3CBR%20%2F%3E%3CHARDWAREDETAILS%3E%3CBR%20%2F%3E%3CSERIALNUMBER%3E00000000%2F62000029%2FING10000%3C%2FSERIALNUMBER%3E%3CBR%20%2F%3E%3CMODELNUMBER%3ELANE3000%3C%2FMODELNUMBER%3E%3CBR%20%2F%3E%3CREVISIONNUMBER%3E%20%3C%2FREVISIONNUMBER%3E%3CBR%20%2F%3E%3CFIRMWAREVERSION%3E30.01%3C%2FFIRMWAREVERSION%3E%3CBR%20%2F%3E%3C%2FHARDWAREDETAILS%3E%3CBR%20%2F%3E%3C%2FHARDWARERECORD%3E%3CBR%20%2F%3E%3CHARDWARERECORD%3E%3CBR%20%2F%3E%3CHARDWAREINFO%3E%3CBR%20%2F%3E%3CHARDWARETYPE%3ECRINDs%3C%2FHARDWARETYPE%3E%3CBR%20%2F%3E%3CBOARDTYPE%3ENORMAL%20PC%20SERIAL%20PORT%3C%2FBOARDTYPE%3E%3CBR%20%2F%3E%3CPORTNUMBER%3E6%3C%2FPORTNUMBER%3E%3CBR%20%2F%3E%3CMANUFACTURER%3EGILBARCO%20EMV%3C%2FMANUFACTURER%3E%3CBR%20%2F%3E%3C%2FHARDWAREINFO%3E%3CBR%20%2F%3E%3CHARDWAREDETAILS%3E%3CBR%20%2F%3E%3CSERIALNUMBER%3E%3C%2FSERIALNUMBER%3E%3CBR%20%2F%3E%3CMODELNUMBER%3E%3C%2FMODELNUMBER%3E%3CBR%20%2F%3E%3CREVISIONNUMBER%3E%3C%2FREVISIONNUMBER%3E%3CBR%20%2F%3E%3CFIRMWAREVERSION%3E%3C%2FFIRMWAREVERSION%3E%3CBR%20%2F%3E%3C%2FHARDWAREDETAILS%3E%3CBR%20%2F%3E%3C%2FHARDWARERECORD%3E%3CBR%20%2F%3E%3C%2FGENERAL%3E%3CBR%20%2F%3E%3CPRINTER%3E%3CBR%20%2F%3E%3CHARDWARETYPE%3EReceipt%20Printer%3C%2FHARDWARETYPE%3E%3CBR%20%2F%3E%3CBOARDTYPE%3ENORMAL%20PC%20SERIAL%20PORT%3C%2FBOARDTYPE%3E%3CBR%20%2F%3E%3CPORTNUMBER%3E7%3C%2FPORTNUMBER%3E%3CBR%20%2F%3E%3CMANUFACTURER%3ETMT20%3C%2FMANUFACTURER%3E%3CBR%20%2F%3E%3C%2FPRINTER%3E%3CBR%20%2F%3E%3CPINPAD%3E%3CBR%20%2F%3E%3CHARDWARETYPE%3EPinpad%20%3C%2FHARDWARETYPE%3E%3CBR%20%2F%3E%3CBOARDTYPE%3ENORMAL%20PC%20SERIAL%20PORT%3C%2FBOARDTYPE%3E%3CBR%20%2F%3E%3CPORTNUMBER%3E8%3C%2FPORTNUMBER%3E%3CBR%20%2F%3E%3CMANUFACTURER%3EINGENICO%3C%2FMANUFACTURER%3E%3CBR%20%2F%3E%3CSERIALNUMBER%3E00000000%2F62000029%2FING10000%3C%2FSERIALNUMBER%3E%3CBR%20%2F%3E%3CMODELNUMBER%3ELANE3000%3C%2FMODELNUMBER%3E%3CBR%20%2F%3E%3CREVISIONNUMBER%3E%20%3C%2FREVISIONNUMBER%3E%3CBR%20%2F%3E%3CFIRMWAREVERSION%3E30.01%3C%2FFIRMWAREVERSION%3E%3CBR%20%2F%3E%3C%2FPINPAD%3E%3CBR%20%2F%3E%3CPUMPS%3E%3CBR%20%2F%3E%3CPUMPRECORD%3E%3CBR%20%2F%3E%3CPUMPNUMBER%3E1%3C%2FPUMPNUMBER%3E%3CBR%20%2F%3E%3CPUMPSERIALNUMBER%3E2830-030304040001%3C%2FPUMPSERIALNUMBER%3E%3CBR%20%2F%3E%3CPUMPMODELNUMBER%3E40%3C%2FPUMPMODELNUMBER%3E%3CBR%20%2F%3E%3CPUMPREVISIONNUMBER%3E000000%3C%2FPUMPREVISIONNUMBER%3E%3CBR%20%2F%3E%3C%2FPUMPRECORD%3E%3CBR%20%2F%3E%3CPUMPRECORD%3E%3CBR%20%2F%3E%3CPUMPNUMBER%3E2%3C%2FPUMPNUMBER%3E%3CBR%20%2F%3E%3CPUMPSERIALNUMBER%3E2830-030304040001%3C%2FPUMPSERIALNUMBER%3E%3CBR%20%2F%3E%3CPUMPMODELNUMBER%3E40%3C%2FPUMPMODELNUMBER%3E%3CBR%20%2F%3E%3CPUMPREVISIONNUMBER%3E000000%3C%2FPUMPREVISIONNUMBER%3E%3CBR%20%2F%3E%3C%2FPUMPRECORD%3E%3CBR%20%2F%3E%3CPUMPRECORD%3E%3CBR%20%2F%3E%3CPUMPNUMBER%3E3%3C%2FPUMPNUMBER%3E%3CBR%20%2F%3E%3CPUMPSERIALNUMBER%3E3040030304040001%3C%2FPUMPSERIALNUMBER%3E%3CBR%20%2F%3E%3CPUMPMODELNUMBER%3E40%3C%2FPUMPMODELNUMBER%3E%3CBR%20%2F%3E%3CPUMPREVISIONNUMBER%3E072814%3C%2FPUMPREVISIONNUMBER%3E%3CBR%20%2F%3E%3C%2FPUMPRECORD%3E%3CBR%20%2F%3E%3C%2FPUMPS%3E%3CBR%20%2F%3E%3CAFDS%3E%3CBR%20%2F%3E%3CAFDRECORD%3E%3CBR%20%2F%3E%3CAFDNUMBER%3E1%3C%2FAFDNUMBER%3E%3CBR%20%2F%3E%3CAFDSERIALNUMBER%3E0002061101704345%3C%2FAFDSERIALNUMBER%3E%3CBR%20%2F%3E%3CAFDMODELNUMBER%3ESPOT%20M3%3C%2FAFDMODELNUMBER%3E%3CBR%20%2F%3E%3CAFDREVISIONNUMBER%3E01.07%3C%2FAFDREVISIONNUMBER%3E%3CBR%20%2F%3E%3C%2FAFDRECORD%3E%3CBR%20%2F%3E%3CAFDRECORD%3E%3CBR%20%2F%3E%3CAFDNUMBER%3E2%3C%2FAFDNUMBER%3E%3CBR%20%2F%3E%3CAFDSERIALNUMBER%3E0002061101704837%3C%2FAFDSERIALNUMBER%3E%3CBR%20%2F%3E%3CAFDMODELNUMBER%3ESPOT%20M3%3C%2FAFDMODELNUMBER%3E%3CBR%20%2F%3E%3CAFDREVISIONNUMBER%3E1.07%3C%2FAFDREVISIONNUMBER%3E%3CBR%20%2F%3E%3C%2FAFDRECORD%3E%3CBR%20%2F%3E%3CAFDRECORD%3E%3CBR%20%2F%3E%3CAFDNUMBER%3E3%3C%2FAFDNUMBER%3E%3CBR%20%2F%3E%3CAFDSERIALNUMBER%3E%3C%2FAFDSERIALNUMBER%3E%3CBR%20%2F%3E%3CAFDMODELNUMBER%3E%3C%2FAFDMODELNUMBER%3E%3CBR%20%2F%3E%3CAFDREVISIONNUMBER%3E%3C%2FAFDREVISIONNUMBER%3E%3CBR%20%2F%3E%3C%2FAFDRECORD%3E%3CBR%20%2F%3E%3C%2FAFDS%3E%3CBR%20%2F%3E%3C%2FHARDWAREREPORT%3E%3CBR%20%2F%3E%3CSYSTEMREPORT%3E%3CBR%20%2F%3E%3CGENERAL%3E%3CBR%20%2F%3E%3CPCMODEL%3EHP%20RP9%20G1%20AiO%20Retail%20System%2C%20%3C%2FPCMODEL%3E%3CBR%20%2F%3E%3CSERIALNUMBER%3E2UA8212H0N%3C%2FSERIALNUMBER%3E%3CBR%20%2F%3E%3C%2FGENERAL%3E%3CBR%20%2F%3E%3CPROCESSORS%3E%3CBR%20%2F%3E%3CNUMBEROFPROCESSORS%3E4%3C%2FNUMBEROFPROCESSORS%3E%3CBR%20%2F%3E%3CPROCESSORTYPE%3EIntel%20Pentium%20II%3C%2FPROCESSORTYPE%3E%3CBR%20%2F%3E%3CPROCESSORREVISION%3E40457%3C%2FPROCESSORREVISION%3E%3CBR%20%2F%3E%3C%2FPROCESSORS%3E%3CBR%20%2F%3E%3COPERATINGSYSTEM%3E%3CBR%20%2F%3E%3CPLATFORM%3EWindows%208%3C%2FPLATFORM%3E%3CBR%20%2F%3E%3CBUILDNUMBER%3E9200%3C%2FBUILDNUMBER%3E%3CBR%20%2F%3E%3CSERVICEPACK%3E0.0%3C%2FSERVICEPACK%3E%3CBR%20%2F%3E%3C%2FOPERATINGSYSTEM%3E%3CBR%20%2F%3E%3CMEMORY%3E%3CBR%20%2F%3E%3CPAGESIZE%3E4096%20Bytes%3C%2FPAGESIZE%3E%3CBR%20%2F%3E%3CTOTALPHYSICAL%3E16263%20MB%3C%2FTOTALPHYSICAL%3E%3CBR%20%2F%3E%3CAVAILABLEPHYSICAL%3E14154%20MB%3C%2FAVAILABLEPHYSICAL%3E%3CBR%20%2F%3E%3CTOTALVIRTUAL%3E2047%20MB%3C%2FTOTALVIRTUAL%3E%3CBR%20%2F%3E%3CAVAILABLEVIRTUAL%3E1513%20MB%3C%2FAVAILABLEVIRTUAL%3E%3CBR%20%2F%3E%3C%2FMEMORY%3E%3CBR%20%2F%3E%3CDISK%3E%3CBR%20%2F%3E%3CTOTALDISKSPACE%3E115486%20MB%3C%2FTOTALDISKSPACE%3E%3CBR%20%2F%3E%3CFREEDISKSPACE%3E13158%20MB%3C%2FFREEDISKSPACE%3E%3CBR%20%2F%3E%3CUSERDISKSPACE%3E13158%20MB%3C%2FUSERDISKSPACE%3E%3CBR%20%2F%3E%3C%2FDISK%3E%3CBR%20%2F%3E%3CNETWORKADAPTER%3E%3CBR%20%2F%3E%3CACTIVELANNAME%3EIntel(R)%20Ethernet%20Connection%20%3C%2FACTIVELANNAME%3E%3CBR%20%2F%3E%3C%2FNETWORKADAPTER%3E%3CBR%20%2F%3E%3C%2FSYSTEMREPORT%3E%3CBR%20%2F%3E%3CTIMEZONE%3E%3CBR%20%2F%3E%3CTIMEZONE%3E(UTC-05%3A00)%20Eastern%20Time%20(US%20%26amp%3Bamp%3B%20Canada)%3C%2FTIMEZONE%3E%3CBR%20%2F%3E%3CDAY%3E2%3C%2FDAY%3E%3CBR%20%2F%3E%3CMONTH%3E3%3C%2FMONTH%3E%3CBR%20%2F%3E%3CBIAS%3E-60%3C%2FBIAS%3E%3CBR%20%2F%3E%3CDSTDISABLE%3ETRUE%3C%2FDSTDISABLE%3E%3CBR%20%2F%3E%3C%2FTIMEZONE%3E%3CBR%20%2F%3E%3CLASTBOOT%3E2019-05-07%2009%3A38%3A13%3C%2FLASTBOOT%3E%3CBR%20%2F%3E%3C%2FPCINFORMATION%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-564505%22%20slang%3D%22en-US%22%3ERe%3A%20ALA%20XML%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-564505%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F339112%22%20target%3D%22_blank%22%3E%40lpoulin%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIs%20it%20this%20line%3F%26nbsp%3B%3CSERIALNUMBER%3E00000000%2F62000029%2FING10000%3C%2FSERIALNUMBER%3E%26nbsp%3B%20-%20if%20its%20not%20what%20does%20it%20look%20like%3F.%3C%2FP%3E%0A%3CP%3EIs%20the%20serial%20number%20always%20the%20same%20format%2C%20or%20occurs%20so%20many%20lines%20after%20a%20pinpad%20entry%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20it%20is%20then%20the%20Parse%20line%20could%20be%20changed%20to%3A%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3Elet%20dummyData%20%3D%20datatable(Date%3Adatetime%2C%20Computer%3Astring%2C%20RawData%3Astring%2C%20Type%3Astring)%0A%5B%0A%20%20%20%20datetime(now)%20%2C%20%22Server0001%22%20%2C%22%3CSERIALNUMBER%3E000000%2F1111111%2F2222222%3C%2FSERIALNUMBER%3E%22%2C%22PCinfo_CL%22%0A%5D%3B%0AdummyData%0A%2F%2F%20look%20for%20serial%20number%20between%20two%20unique%20strings%20in%20the%20rawdata%20row%0A%7C%20parse%20RawData%20with%20*%20%22%3CSERIALNUMBER%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E000000%3C%2FSTRONG%3E%3C%2FFONT%3E%22%20intSerialNumber%20%22%26lt%3B%2FSerialNumber%26gt%3B%22%20*%0A%7C%20project%20Date%2C%20Computer%2C%20SerialNumber%20%3D%20intSerialNumber%2C%20Type%3C%2FSERIALNUMBER%3E%3C%2FPRE%3E%0A%3CP%3EWhat%20I'm%20trying%20to%20find%20is%20something%20unique%20in%20the%20line%20or%20a%20pattern%2C%20that%20doesn't%20occur%20for%20any%20of%20the%20other%20serial%20numbers.%26nbsp%3B%20So%20if%20they%20always%20%3CFONT%20color%3D%22%23000000%22%3Estarted%3C%2FFONT%3E%20with%26nbsp%3B%22%3CSTRONG%3E%3CSERIALNUMBER%3E%3C%2FSERIALNUMBER%3E%3C%2FSTRONG%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E000000%22%26nbsp%3B%3C%2FSTRONG%3E%3CFONT%20color%3D%22%23000000%22%3Ewe%20could%20find%20that%2C%20and%20add%20them%20back%20to%20the%20output%20with%20this%20amended%20final%20line%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CPRE%3E%7C%20project%20Date%2C%20Computer%2C%20SerialNumber%20%3D%20strcat(%22000000%22%2C%22%22%2CintSerialNumber)%2C%20Type%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-566414%22%20slang%3D%22en-US%22%3ERe%3A%20ALA%20XML%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-566414%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20again%20for%20the%20help.%20I've%20looked%20into%20it%20and%20I%20can't%20guarantee%20the%20serial%20number%20will%20always%20start%20the%20same%20and%20the%20row%20number%20in%20the%20file%20is%20dependent%20on%20the%20amount%20of%20equipment%20connected%20which%20can%20vary.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20the%20row%20number%20of%20the%20serial%20number%20I%20want%20will%20always%20be%205%20rows%20below%20the%20%3CPINPAD%3E%20row.%20Is%20it%20possible%20to%20generate%20a%20query%20based%20off%20this%3F%26nbsp%3B%3C%2FPINPAD%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-568899%22%20slang%3D%22en-US%22%3ERe%3A%20ALA%20XML%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-568899%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F339112%22%20target%3D%22_blank%22%3E%40lpoulin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20about%20this%3F%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fportal.loganalytics.io%2FDemo%3Fq%3DH4sIAAAAAAAAA61VPU%252FDMBDdI%252BU%252FHJ6aylLTmC6lZSkjQlVhQwi5zZVYSpzKcQlF%252FHhst6QfLJD4LdFdfO%252Be70W5HDWk26LY3XHNYQqpeWi%252BzLFnEjg2IWpRIIVZWWy2GtW40krINwoLXtuaJn7abfAQRGHwHAZg8FPfk2UdAQXyiOodVRzHQwKUTOZCznl6SyiZz4Rcl6%252Bze0L%252FVDq0aFGXWEwGJil4%252FrAtlqjatGcW3WmuLbrTTCpHIPcEscVguMeAMU9aRxadaJL2nictPU%252F8eJ748Tzx43ny23N3Ra%252Bee9LK2nvOWnrOGs%252B7mc4a07tNkvmc5JnrTts%252FXX%252B5CYPmjx8GX1CVSsNyB%252FaHDylWK5d0FOITwUb4oVGmoDPcUy%252FK2uwKadK9wxqgo8ge3HBV4fm5WugM%252BnChnICQ%252BlSnOXChG%252FonrTOhTUch1j8NIeMVnHxYYFbPih%252F1EEIvOkSUrHleIXFK6wwVOtqr6SHv7m0mw5W9t5mIeXtcfGHwDRSAOLMsBwAA%26amp%3Btimespan%3DP1D%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ELink%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3Elet%20dummyData%20%3D%20datatable(Date%3Adatetime%2C%20Computer%3Astring%2C%20RawData%3Astring%2C%20Type%3Astring)%0A%5B%0A%20%20%20%20datetime(now)%20%2C%20%22Server0001%22%20%2C%22%26lt%3BPinPad%26gt%3B%22%2C%22PCinfo_CL%22%2C%0A%20%20%20%20datetime(now)%20%2C%20%22Server0001%22%20%2C%2211111%22%2C%22PCinfo_CL%22%2C%0A%20%20%20%20datetime(now)%20%2C%20%22Server0001%22%20%2C%2222222%26lt%3B%2FSerialNumber%26gt%3B%22%2C%22PCinfo_CL%22%2C%0A%20%20%20%20datetime(now)%20%2C%20%22Server0001%22%20%2C%2233333%26lt%3B%2FSerialNumber%26gt%3B%22%2C%22PCinfo_CL%22%2C%0A%20%20%20%20datetime(now)%20%2C%20%22Server0001%22%20%2C%2244444%26lt%3B%2FSerialNumber%26gt%3B%22%2C%22PCinfo_CL%22%2C%0A%20%20%20%20datetime(now)%20%2C%20%22Server0001%22%20%2C%22%26lt%3Bserialnumber%26gt%3B00000%2F1111111%2F3333333%26lt%3B%2FSerialNumber%26gt%3B%22%2C%22PCinfo_CL%22%2C%0A%20%20%20%20datetime(now)%20%2C%20%22Server0001%22%20%2C%2255555%26lt%3B%2FSerialNumber%26gt%3B%22%2C%22PCinfo_CL%22%2C%0A%20%20%20%20datetime(now)%20%2C%20%22Server0002%22%20%2C%22%26lt%3BPinPad%26gt%3B%22%2C%22PCinfo_CL%22%2C%0A%20%20%20%20datetime(now)%20%2C%20%22Server0002%22%20%2C%2211111%22%2C%22PCinfo_CL%22%2C%0A%20%20%20%20datetime(now)%20%2C%20%22Server0002%22%20%2C%2222222%26lt%3B%2FSerialNumber%26gt%3B%22%2C%22PCinfo_CL%22%2C%0A%20%20%20%20datetime(now)%20%2C%20%22Server0002%22%20%2C%2233333%26lt%3B%2FSerialNumber%26gt%3B%22%2C%22PCinfo_CL%22%2C%0A%20%20%20%20datetime(now)%20%2C%20%22Server0002%22%20%2C%2244444%26lt%3B%2FSerialNumber%26gt%3B%22%2C%22PCinfo_CL%22%2C%0A%20%20%20%20datetime(now)%20%2C%20%22Server0002%22%20%2C%22%26lt%3Bserialnumber%26gt%3B22222%2F1111111%2F3333333%26lt%3B%2FSerialNumber%26gt%3B%22%2C%22PCinfo_CL%22%2C%0A%20%20%20%20datetime(now)%20%2C%20%22Server0002%22%20%2C%2244444%26lt%3B%2FSerialNumber%26gt%3B%22%2C%22PCinfo_CL%22%2C%0A%20%20%20%20datetime(now)%20%2C%20%22Server0003%22%20%2C%22%26lt%3BPinPad%26gt%3B%22%2C%22PCinfo_CL%22%2C%0A%20%20%20%20datetime(now)%20%2C%20%22Server0003%22%20%2C%2211111%22%2C%22PCinfo_CL%22%2C%0A%20%20%20%20datetime(now)%20%2C%20%22Server0003%22%20%2C%22222222%26lt%3B%2FSerialNumber%26gt%3B%22%2C%22PCinfo_CL%22%2C%0A%20%20%20%20datetime(now)%20%2C%20%22Server0003%22%20%2C%22333333%26lt%3B%2FSerialNumber%26gt%3B%22%2C%22PCinfo_CL%22%2C%0A%20%20%20%20datetime(now)%20%2C%20%22Server0003%22%20%2C%2244444%26lt%3B%2FSerialNumber%26gt%3B%22%2C%22PCinfo_CL%22%2C%0A%20%20%20%20datetime(now)%20%2C%20%22Server0003%22%20%2C%22%26lt%3Bserialnumber%26gt%3B33333%2F1111111%2F3333333%26lt%3B%2FSerialNumber%26gt%3B%22%2C%22PCinfo_CL%22%2C%0A%5D%3B%0AdummyData%0A%7C%20serialize%20%0A%7C%20extend%20theSerialRow%20%3D%20next(RawData%2C5)%0A%7C%20parse%20theSerialRow%20with%20*%20%22%26lt%3Bserialnumber%26gt%3B%22%20intSerialNumber%20%22%26lt%3B%2FSerialNumber%26gt%3B%22%20*%0A%7C%20extend%20hit%20%3D%20iif(RawData%20has%20%22%26lt%3BPinPad%26gt%3B%22%2C%20strcat(RawData%2C%22%22%2CintSerialNumber)%2C%22false%22)%0A%7C%20where%20hit%20!%3D%22false%22%0A%7C%20summarize%20by%20hit%2C%20Computer%0A%3C%2FPRE%3E%0A%3CP%3EWhich%20looks%20like%2C%20this%20on%20your%20system%20(I%20think)%3C%2FP%3E%0A%3CPRE%3EPCINFO_CL%0A%7C%20serialize%20%0A%7C%20extend%20theSerialRow%20%3D%20next(RawData%2C5)%0A%7C%20parse%20theSerialRow%20with%20*%20%22%26lt%3Bserialnumber%26gt%3B%22%20intSerialNumber%20%22%26lt%3B%2FSerialNumber%26gt%3B%22%20*%0A%7C%20extend%20hit%20%3D%20iif(RawData%20has%20%22%26lt%3BPinPad%26gt%3B%22%2C%20strcat(RawData%2C%22%22%2CintSerialNumber)%2C%22false%22)%0A%7C%20where%20hit%20!%3D%22false%22%0A%7C%20summarize%20by%20hit%2C%20Computer%0A%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOutput%20is%20like%20this%3A%3C%2FP%3E%0A%3CDIV%20class%3D%22k-grid-header%22%3E%0A%3CDIV%20class%3D%22k-grid-header-wrap%20k-auto-scrollable%22%20data-role%3D%22resizable%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CDIV%20class%3D%22k-grid-content%20k-auto-scrollable%22%3E%0A%3CTABLE%20tabindex%3D%220%22%20role%3D%22treegrid%22%20aria-activedescendant%3D%22queryResultsViewGrid_active_cell%22%3E%3CCOLGROUP%3E%3CCOL%20class%3D%22k-hierarchy-col%22%20%2F%3E%3CCOL%20%2F%3E%3CCOL%20%2F%3E%3CCOL%20%2F%3E%3C%2FCOLGROUP%3E%0A%3CTBODY%20role%3D%22rowgroup%22%3E%0A%3CTR%20class%3D%22k-master-row%22%20role%3D%22row%22%20data-uid%3D%22948c376d-8532-46a3-b95c-2789c07a8ac1%22%3E%0A%3CTD%20class%3D%22k-hierarchy-cell%22%20aria-expanded%3D%22false%22%3E%26nbsp%3B%3C%2FTD%3E%0A%3CTD%20class%3D%22%22%20aria-describedby%3D%223caaef63-6e17-400f-98eb-df585fadc312%22%3E%3CPINPAD%3E00000%2F1111111%2F3333333%3C%2FPINPAD%3E%3C%2FTD%3E%0A%3CTD%20class%3D%22%22%20aria-describedby%3D%22400d62b1-42d8-4320-9b94-06938f105658%22%3EServer0001%3C%2FTD%3E%0A%3CTD%20aria-describedby%3D%222b7e4875-54e9-42d0-ad5a-e08b87f87532%22%3E%26nbsp%3B%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%20class%3D%22k-alt%20k-master-row%22%20role%3D%22row%22%20data-uid%3D%22bed56abf-492b-4f35-a95d-766e7a4eed57%22%3E%0A%3CTD%20class%3D%22k-hierarchy-cell%22%20aria-expanded%3D%22false%22%3E%26nbsp%3B%3C%2FTD%3E%0A%3CTD%20class%3D%22%22%20aria-describedby%3D%223caaef63-6e17-400f-98eb-df585fadc312%22%3E%3CPINPAD%3E22222%2F1111111%2F3333333%3C%2FPINPAD%3E%3C%2FTD%3E%0A%3CTD%20class%3D%22%22%20aria-describedby%3D%22400d62b1-42d8-4320-9b94-06938f105658%22%3EServer0002%3C%2FTD%3E%0A%3CTD%20aria-describedby%3D%222b7e4875-54e9-42d0-ad5a-e08b87f87532%22%3E%26nbsp%3B%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%20class%3D%22k-master-row%22%20role%3D%22row%22%20data-uid%3D%2273df515b-fdac-45ff-9ed9-13912d9e4dd0%22%3E%0A%3CTD%20class%3D%22k-hierarchy-cell%22%20aria-expanded%3D%22false%22%3E%26nbsp%3B%3C%2FTD%3E%0A%3CTD%20class%3D%22%22%20aria-describedby%3D%223caaef63-6e17-400f-98eb-df585fadc312%22%3E%3CPINPAD%3E33333%2F1111111%2F3333333%3C%2FPINPAD%3E%3C%2FTD%3E%0A%3CTD%20class%3D%22%22%20aria-describedby%3D%22400d62b1-42d8-4320-9b94-06938f105658%22%3EServer0003%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-570042%22%20slang%3D%22en-US%22%3ERe%3A%20ALA%20XML%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-570042%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20seeing%20this%20through%20and%20thinking%20of%20different%20ways%20to%20tackle%20the%20problem.%20I%20wasn't%20aware%20of%20the%20next%20function%20but%20that%20is%20exactly%20what%20I%20needed.%20Your%20code%20works%20perfect%20once%20I%20accounted%20for%20the%20case%20sensitivity%20and%20changed%20%3CSERIALNUMBER%3E%20to%20%3CSERIALNUMBER%3E%3C%2FSERIALNUMBER%3E%3C%2FSERIALNUMBER%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-570059%22%20slang%3D%22en-US%22%3ERe%3A%20ALA%20XML%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-570059%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F339112%22%20target%3D%22_blank%22%3E%40lpoulin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhoops%2C%20sorry%20about%20the%20typo%20%3A)%3C%2Fimg%3E%26nbsp%3B%20%3CBR%20%2F%3E%3CBR%20%2F%3EI'm%20really%20pleased%20this%20works%2C%20and%20it%20took%20me%20a%20while%20to%20remember%20that%20'next'%20was%20an%20option.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%20Clive%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-633693%22%20slang%3D%22en-US%22%3ERe%3A%20ALA%20XML%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-633693%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20one%20more%20question%20for%20you%20if%20you%20are%20still%20available.%20I'm%20looking%20to%20add%20a%20column%20to%20my%20query%20that%20does%20not%20exist%20inside%20the%20%3CPINPAD%3E.%20I'm%20so%20close%20but%20just%20need%20them%20to%20display%20on%20the%20same%20row.%20It's%20the%20Bulloch_Version.%20How%20do%20I%20join%20them%20since%20I%20am%20getting%20false%20flags%3F%3C%2FPINPAD%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3EPCINFO_CL%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eserialize%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20theSerialRow%20%3D%20next(RawData%2C%3C%2FSPAN%3E%3CSPAN%3E5%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20theModelNumber%20%3D%20next(RawData%2C%3C%2FSPAN%3E%3CSPAN%3E6%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20thefirmwareRow%20%3D%20next(RawData%2C%3C%2FSPAN%3E%3CSPAN%3E8%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20theBullochVersion%20%3D%20RawData%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eparse%3C%2FSPAN%3E%3CSPAN%3E%20theBullochVersion%20with%20*%20%3C%2FSPAN%3E%3CSPAN%3E%22%3CBT9000_VERSION%3E%22%3C%2FBT9000_VERSION%3E%3C%2FSPAN%3E%3CSPAN%3E%20intBullochVersion%20%3C%2FSPAN%3E%3CSPAN%3E%22%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%22%3CSPAN%3E%20*%3C%2FSPAN%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20Bulloch_Version%20%3D%20strcat(substring(intBullochVersion%2C%3C%2FSPAN%3E%3CSPAN%3E0%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E2%3C%2FSPAN%3E%3CSPAN%3E)%2C%3C%2FSPAN%3E%3CSPAN%3E%22.%22%3C%2FSPAN%3E%3CSPAN%3E%2Csubstring(intBullochVersion%2C%3C%2FSPAN%3E%3CSPAN%3E2%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E2%3C%2FSPAN%3E%3CSPAN%3E)%2C%3C%2FSPAN%3E%3CSPAN%3E%22.%22%3C%2FSPAN%3E%3CSPAN%3E%2Csubstring(intBullochVersion%2C%3C%2FSPAN%3E%3CSPAN%3E4%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E2%3C%2FSPAN%3E%3CSPAN%3E))%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eparse%3C%2FSPAN%3E%3CSPAN%3E%20theSerialRow%20with%20*%20%3C%2FSPAN%3E%3CSPAN%3E%22%3CSERIALNUMBER%3E%22%3C%2FSERIALNUMBER%3E%3C%2FSPAN%3E%3CSPAN%3E%20intSerialNumber%20%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3CSPAN%3E%20*%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20PinPad_SerialNumber%20%3D%20iif(RawData%20%3C%2FSPAN%3E%3CSPAN%3Ehas%3C%2FSPAN%3E%20%3CSPAN%3E%22%3CPINPAD%3E%22%3C%2FPINPAD%3E%3C%2FSPAN%3E%3CSPAN%3E%2C%20strcat(intSerialNumber)%2C%3C%2FSPAN%3E%3CSPAN%3E%22false%22%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eparse%3C%2FSPAN%3E%3CSPAN%3E%20theModelNumber%20with%20*%20%3C%2FSPAN%3E%3CSPAN%3E%22%3CMODELNUMBER%3E%22%3C%2FMODELNUMBER%3E%3C%2FSPAN%3E%3CSPAN%3E%20intModelNumber%20%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3CSPAN%3E%20*%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20PinPad_ModelNumber%20%3D%20iif(RawData%20%3C%2FSPAN%3E%3CSPAN%3Ehas%3C%2FSPAN%3E%20%3CSPAN%3E%22%3CPINPAD%3E%22%3C%2FPINPAD%3E%3C%2FSPAN%3E%3CSPAN%3E%2C%20strcat(intModelNumber)%2C%3C%2FSPAN%3E%3CSPAN%3E%22false%22%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eparse%3C%2FSPAN%3E%3CSPAN%3E%20thefirmwareRow%20with%20*%20%3C%2FSPAN%3E%3CSPAN%3E%22%3CFIRMWAREVERSION%3E%22%3C%2FFIRMWAREVERSION%3E%3C%2FSPAN%3E%3CSPAN%3E%20intFirmwareVersion%20%3C%2FSPAN%3E%3CSPAN%3E%22%22%3C%2FSPAN%3E%3CSPAN%3E%20*%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20PinPad_FirmwareVersion%20%3D%20iif(RawData%20%3C%2FSPAN%3E%3CSPAN%3Ehas%3C%2FSPAN%3E%20%3CSPAN%3E%22%3CPINPAD%3E%22%3C%2FPINPAD%3E%3C%2FSPAN%3E%3CSPAN%3E%2C%20strcat(intFirmwareVersion)%2C%3C%2FSPAN%3E%3CSPAN%3E%22false%22%3C%2FSPAN%3E%3CSPAN%3E)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20PinPad_FirmwareVersion%20!%3D%3C%2FSPAN%3E%3CSPAN%3E%22false%22%3C%2FSPAN%3E%20%3CSPAN%3Eor%3C%2FSPAN%3E%3CSPAN%3E%20PinPad_ModelNumber%20!%3D%3C%2FSPAN%3E%3CSPAN%3E%22false%22%3C%2FSPAN%3E%20%3CSPAN%3Eor%3C%2FSPAN%3E%3CSPAN%3E%20PinPad_SerialNumber%20!%3D%3C%2FSPAN%3E%3CSPAN%3E%22false%22%3C%2FSPAN%3E%20%3CSPAN%3Eor%3C%2FSPAN%3E%3CSPAN%3E%20RawData%20%3C%2FSPAN%3E%3CSPAN%3Econtains%3C%2FSPAN%3E%20%3CSPAN%3E%22%3CBT9000_VERSION%3E%22%3C%2FBT9000_VERSION%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Esummarize%3C%2FSPAN%3E%3CSPAN%3E%20arg_max(Date_Stamp%3DTimeGenerated%2C%20Bulloch_Version)%20%3C%2FSPAN%3E%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20Province%3Dsubstring(Computer%2C%20%3C%2FSPAN%3E%3CSPAN%3E5%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3E2%3C%2FSPAN%3E%3CSPAN%3E)%2C%20Outlet%3Dsubstring(Computer%2C%20%3C%2FSPAN%3E%3CSPAN%3E7%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3E5%3C%2FSPAN%3E%3CSPAN%3E)%2C%20Terminal%3Dsubstring(Computer%2C%20%3C%2FSPAN%3E%3CSPAN%3E12%3C%2FSPAN%3E%3CSPAN%3E%2C%20%3C%2FSPAN%3E%3CSPAN%3E2%3C%2FSPAN%3E%3CSPAN%3E)%2C%20PinPad_ModelNumber%2C%20PinPad_SerialNumber%2C%20PinPad_FirmwareVersion%3C%2FSPAN%3E%3C%2FDIV%3E%3CLINGO-SUB%20id%3D%22lingo-sub-637700%22%20slang%3D%22en-US%22%3ERe%3A%20ALA%20XML%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-637700%22%20slang%3D%22en-US%22%3E%3CP%3Ehello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F339112%22%20target%3D%22_blank%22%3E%40lpoulin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'm%20not%20sure%20I%20understand%20the%20ask%20%22%3CEM%3Ejust%20need%20them%20to%20display%20on%20the%20same%20row.%20It's%20the%20Bulloch_Version.%20How%20do%20I%20join%20them%22%26nbsp%3B%20%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ESo%20you%20have%20successfully%26nbsp%3Bcreated%20a%20column%26nbsp%3Bcalled%20%22Bulloch%20Version%22%20and%20you%20want%20to%20join%20that%20data%20to%20what%3F%26nbsp%3B%20Can%20you%20use%20MS%20Paint%20or%20something%26nbsp%3Bto%20show%20me%20an%20example%20of%20what%20you%20need%20the%20row%20or%20column%20to%20look%20like%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-638077%22%20slang%3D%22en-US%22%3ERe%3A%20ALA%20XML%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-638077%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20for%20the%20confusion.%20I%20would%20like%20my%20report%20to%20only%20have%20two%20rows.%20I%20added%20a%20new%20column%20for%20Bulloch_Version%20but%20it%20is%20displaying%20on%20its%20own%20row%20instead%20of%20joining%20with%20the%20PinPad%20information.%20I%20get%20...%20for%20some%20reason.%20The%20information%20comes%20from%20the%20same%20pcinfo.xml%20file.%20I%20highlighted%20in%20yellow%20the%20values%20that%20I%20would%20I%20like%20to%20move.%20Please%20see%20pcinforeport1.png%20for%20the%20original%20and%20finalpcinfo.png%20for%20what%20I%20want%20it%20to%20look%20like.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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. 

12 Replies
Highlighted

@lpoulin 

 

How did you import the custom log, with a timestamp or with 'new line'?  
Annotation 2019-05-10 111840.png

 

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

 

 

Highlighted

@Clive Watson 

 

Thank you for the reply. 

 

I imported the data with new line: 

 

RAWDATA.PNGrawdata2.PNG

 

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:

 

limit1.PNG

Highlighted

@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

Annotation 2019-05-13 181632.png

 

Run this from here

Highlighted

@Clive Watson 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>

Highlighted

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

 

 

 

 

Highlighted

@Clive Watson 

 

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? 

Highlighted
Solution

@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
Highlighted

@Clive Watson 

 

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>

Highlighted

@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

Highlighted

@Clive Watson 

 

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
Highlighted

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?

Highlighted

@Clive Watson 

 

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.