SOLVED

parsing two options in the same cell?

%3CLINGO-SUB%20id%3D%22lingo-sub-882214%22%20slang%3D%22en-US%22%3Eparsing%20two%20options%20in%20the%20same%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-882214%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20parse%20for%20usernames%20that%20can%20appear%20under%20different%20text%20context%20in%20the%20same%20cell.%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20check%20for%20different%20variants%20in%20the%20same%20cell%20and%20stop%20on%20the%20first%20match%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20obviously%20not%20working%20but%20explains%20my%20question%3A%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eparse%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3BMessage%20with%20*%3C%2FSPAN%3E%3CSPAN%3E%22ABC%22%3C%2FSPAN%3E%3CSPAN%3E%20UserName%20%3C%2FSPAN%3E%3CSPAN%3E%22DEF%22%3C%2FSPAN%3E%3CSPAN%3E*%20%3C%2FSPAN%3E%3CSTRONG%3Eor%3C%2FSTRONG%3E%3CSPAN%3E%20*%3C%2FSPAN%3E%3CSPAN%3E%22GHI%22%3C%2FSPAN%3E%3CSPAN%3E%20UserName%20%3C%2FSPAN%3E%3CSPAN%3E%22JKL%22%3C%2FSPAN%3E%3CSPAN%3E*%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3EThank%20You.%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-882214%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EQuery%20Language%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-882764%22%20slang%3D%22en-US%22%3ERe%3A%20parsing%20two%20options%20in%20the%20same%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-882764%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F409040%22%20target%3D%22_blank%22%3E%40CloudMe%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20does%20the%20column%20'Message'%20actaulyly%20look%20like%20look%20like%3F%26nbsp%3B%20Can%20you%20provide%20some%20examples%20of%20the%20contents%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ee.g.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3Eprint%3C%2FSPAN%3E%3CSPAN%3E%20Message%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22ABCyyyyDEFGHIzzzzJKL%22%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3Eor%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CSPAN%3Eprint%3C%2FSPAN%3E%3CSPAN%3E%20Message%20%3D%20%3C%2FSPAN%3E%3CSPAN%3E%22ABCDEFGHIzzzzJKL%22%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%3CSPAN%3Eor%20use%20a%20datatable%3C%2FSPAN%3E%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E%0A%3CDIV%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%20msg%20%3D%20datatable%20(Message%3Astring)%0A%5B%0A%22ABCyyyyDEFGHIzzzzJKLMNOaaaaaPQR%22%0A%5D%3B%0Amsg%0A%E2%80%8B%3C%2FCODE%3E%3C%2FPRE%3E%3CBR%20%2F%3E%0A%3CDIV%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fdatatableoperator%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fkusto%2Fquery%2Fdatatableoperator%3C%2FA%3E%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-883053%22%20slang%3D%22en-US%22%3ERe%3A%20parsing%20two%20options%20in%20the%20same%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-883053%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%3EIts%20a%20SyslogMessage%20that%20contains%20failed%20authentication%20info%20and%20can%20come%20in%20two%20types.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EFailed%20password%20for%20invalid%20user%20UserName%20from%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eor%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EFailed%20password%20for%20UserName%20from%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Ei%20need%20to%20parse%20the%26nbsp%3BUserName%20for%20my%20next%20actions%20regardless%20of%20the%20message%20type%20but%20put%20it%20in%20only%20one%20variable(UserName).%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-885260%22%20slang%3D%22en-US%22%3ERe%3A%20parsing%20two%20options%20in%20the%20same%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-885260%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F409040%22%20target%3D%22_blank%22%3E%40CloudMe%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAssuming%20the%20word%20%22from%22%20is%20the%20final%20word%20in%20this%20column%20the%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%20msg%20%3D%20datatable%20(Date%3Adate%2C%20Message%3Astring)%0A%5B%0Adatetime(2019-09-29)%2C%22Failed%20password%20for%20%20testperson%20from%22%2C%0Adatetime(2019-09-29)%2C%22Failed%20password%20for%20invalid%20user%20clive%20from%22%2C%0Adatetime(2019-09-30)%2C%22Failed%20password%20for%20testuser%20from%22%2C%0Adatetime(2019-09-30)%2C%22Failed%20password%20fakeuser%20from%22%0A%5D%3B%0Amsg%0A%7C%20extend%20txtArray%20%3Dsplit(Message%2C%20%22%20%22)%20%20%20%2F%2F%20split%20the%20column%20if%20we%20see%20a%20%3CSPACE%3E%20into%20an%20array%0A%7C%20extend%20posArray%20%3D%20array_length(txtArray)%20-2%20%20%20%2F%2F%20Now%20calculate%20the%20positionin%20the%20array%20of%20the%202nd%20to%20last%20entry%20(the%20word%20before%20%22from%22)%0A%7C%20extend%20UserName%20%3D%20trim(%40%22%5B%5E%5Cw%5D%2B%22%2Ctostring(split(txtArray%5BposArray%5D%2C%22%20%22)))%20%2F%2F%20clean%20the%20text%20(non%20Alpha%20chars)%20and%20get%20the%202nd%20to%20last%20entry%20%0A%3C%2FSPACE%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CTABLE%20cellspacing%3D%221%22%20cellpadding%3D%225%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTH%3EDate%3C%2FTH%3E%0A%3CTH%3EMessage%3C%2FTH%3E%0A%3CTH%3EtxtArray%3C%2FTH%3E%0A%3CTH%3EposArray%3C%2FTH%3E%0A%3CTH%3EUserName%3C%2FTH%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2019-09-29T00%3A00%3A00Z%3C%2FTD%3E%0A%3CTD%3EFailed%20password%20for%20testperson%20from%3C%2FTD%3E%0A%3CTD%3E%5B%22Failed%22%2C%22password%22%2C%22for%22%2C%22%22%2C%22testperson%22%2C%22from%22%5D%3C%2FTD%3E%0A%3CTD%3E4%3C%2FTD%3E%0A%3CTD%3Etestperson%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2019-09-29T00%3A00%3A00Z%3C%2FTD%3E%0A%3CTD%3EFailed%20password%20for%20invalid%20user%20clive%20from%3C%2FTD%3E%0A%3CTD%3E%5B%22Failed%22%2C%22password%22%2C%22for%22%2C%22invalid%22%2C%22user%22%2C%22clive%22%2C%22from%22%5D%3C%2FTD%3E%0A%3CTD%3E5%3C%2FTD%3E%0A%3CTD%3Eclive%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2019-09-30T00%3A00%3A00Z%3C%2FTD%3E%0A%3CTD%3EFailed%20password%20for%20testuser%20from%3C%2FTD%3E%0A%3CTD%3E%5B%22Failed%22%2C%22password%22%2C%22for%22%2C%22testuser%22%2C%22from%22%5D%3C%2FTD%3E%0A%3CTD%3E3%3C%2FTD%3E%0A%3CTD%3Etestuser%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3CTR%3E%0A%3CTD%3E2019-09-30T00%3A00%3A00Z%3C%2FTD%3E%0A%3CTD%3EFailed%20password%20fakeuser%20from%3C%2FTD%3E%0A%3CTD%3E%5B%22Failed%22%2C%22password%22%2C%22fakeuser%22%2C%22from%22%5D%3C%2FTD%3E%0A%3CTD%3E2%3C%2FTD%3E%0A%3CTD%3Efakeuser%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3C%2FDIV%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-887320%22%20slang%3D%22en-US%22%3ERe%3A%20parsing%20two%20options%20in%20the%20same%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-887320%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%2C%20I%20appreciate%20your%20time%20and%20effort.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

 

I need to parse for usernames that can appear under different text context in the same cell.

Is it possible to check for different variants in the same cell and stop on the first match?

 

This is obviously not working but explains my question:

| parse Message with *"ABC" UserName "DEF"* or *"GHI" UserName "JKL"*
 
Thank You.
4 Replies

@CloudMe

 

What does the column 'Message' actaulyly look like look like?  Can you provide some examples of the contents?

 

e.g.

 

print Message = "ABCyyyyDEFGHIzzzzJKL"
 
or
 
print Message = "ABCDEFGHIzzzzJKL"
 
or use a datatable
 
let msg = datatable (Message:string)
[
"ABCyyyyDEFGHIzzzzJKLMNOaaaaaPQR"
];
msg
​

 

@Clive Watson 

 

Its a SyslogMessage that contains failed authentication info and can come in two types.

 

Failed password for invalid user UserName from

or

Failed password for UserName from

 

i need to parse the UserName for my next actions regardless of the message type but put it in only one variable(UserName).

 

best response confirmed by CloudMe (Occasional Contributor)
Solution

@CloudMe 

 

Assuming the word "from" is the final word in this column the:

 

let msg = datatable (Date:date, Message:string)
[
datetime(2019-09-29),"Failed password for  testperson from",
datetime(2019-09-29),"Failed password for invalid user clive from",
datetime(2019-09-30),"Failed password for testuser from",
datetime(2019-09-30),"Failed password fakeuser from"
];
msg
| extend txtArray =split(Message, " ")   // split the column if we see a <space> into an array
| extend posArray = array_length(txtArray) -2   // Now calculate the positionin the array of the 2nd to last entry (the word before "from")
| extend UserName = trim(@"[^\w]+",tostring(split(txtArray[posArray]," "))) // clean the text (non Alpha chars) and get the 2nd to last entry 

 

 

Date Message txtArray posArray UserName
2019-09-29T00:00:00Z Failed password for testperson from ["Failed","password","for","","testperson","from"] 4 testperson
2019-09-29T00:00:00Z Failed password for invalid user clive from ["Failed","password","for","invalid","user","clive","from"] 5 clive
2019-09-30T00:00:00Z Failed password for testuser from ["Failed","password","for","testuser","from"] 3 testuser
2019-09-30T00:00:00Z Failed password fakeuser from ["Failed","password","fakeuser","from"] 2 fakeuser

 

 

@Clive Watson Thank you, I appreciate your time and effort.