SOLVED

How to extract Common name from Distinguished Name in Kusto Query

%3CLINGO-SUB%20id%3D%22lingo-sub-1528795%22%20slang%3D%22en-US%22%3EHow%20to%20extract%20Common%20name%20from%20Distinguished%20Name%20in%20Kusto%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1528795%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EAnyone%20have%20a%20Solution%20on%20how%20to%20extract%20Common%20name%20from%20Distinguished%20Name%20In%20Kusto%20I%20have%20tried%20parse%2C%20split%2C%20Sub%20string%20and%20what%20ever%2C%20but%20haven%C2%B4t%20have%20a%20success%20with%20VB%20and%20Power%20Shell%20it%20is%20simple%20and%20a%20lot%20of%20examples%20to%20grab%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20a%20table%20called%20Member%20Name%26nbsp%3B%20containing%3C%2FP%3E%3CP%3ECN%3Dtest%20test%2C%20OU%3Dsomething%2C%20OU%3DSomething%2C%20OU%3DSomething%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewould%20like%20to%20display%26nbsp%3Bonly%20the%20Name%20in%20output%20table%3C%2FP%3E%3CP%3E%26nbsp%3BTest%20Test%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1528795%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Log%20Analytics%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1528995%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20extract%20Common%20name%20from%20Distinguished%20Name%20in%20Kusto%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1528995%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F635452%22%20target%3D%22_blank%22%3E%40Stig_hj%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20are%20two%20ways%2C%20I%20prefer%20Parse%20to%20split%20in%20this%20case%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3Elet%20MemberTable%20%3D%20datatable%20(cn%3Astring)%0A%5B%0A%22CN%3Dtest%20test%2C%20OU%3Dsomething%2C%20OU%3DSomething%2C%20OU%3DSomething%22%0A%5D%3B%0AMemberTable%0A%7C%20parse%20cn%20with%20*%20%22CN%3D%22%20strcnName%20%22%2C%22%20*%0A%7C%20project%20split(split(cn%2C%22%2C%22).%5B0%5D%2C%22%3D%22).%5B1%5D%20%2C%20strcnName%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1540490%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20extract%20Common%20name%20from%20Distinguished%20Name%20in%20Kusto%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540490%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%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20reply%2C%20had%20parse%20and%20split%20in%20the%20loop%2C%20but%20could%20not%20get%20a%20clean%20output%26nbsp%3B%3C%2FP%3E%3CP%3Egot%20something%20like%20%5Bcn%3DName%20Name%5D%3C%2FP%3E%3CP%3Einstead%20of%20Name%20Name%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1540875%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20extract%20Common%20name%20from%20Distinguished%20Name%20in%20Kusto%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540875%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F635452%22%20target%3D%22_blank%22%3E%40Stig_hj%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMaybe%20this%20a%20JSON%20rather%20than%20a%20string%3F%3CBR%20%2F%3E%3CBR%20%2F%3ECould%20you%20copy%20%26amp%3B%20paste%20one%20line%20of%20the%20real%20output%20from%20the%20real%20table%3F%26nbsp%3B%20Please%20remove%20any%20PII%20but%20leave%20the%20format!%3CBR%20%2F%3E%3CBR%20%2F%3Ee.g.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3EMemberTable%0A%7C%20limit%201%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1543363%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20extract%20Common%20name%20from%20Distinguished%20Name%20in%20Kusto%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1543363%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%3Etable%20name%20is%20MemberName%3C%2FP%3E%3CP%3Ethe%20string%20from%20the%20table%20to%20retrieve%3C%2FP%3E%3CP%3ECN%3DName%20Name%2COU%3DUsers%2COU%3Dorg%2COU%3DO_City%2COU%3DNN%2COU%3DXX_ABC%2COU%3DGroup%2CDC%3DDomain%2CDC%3Dcom%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EName%20Name%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETried%20this%20but%20have%20a%20%22wrong%22%20Column%20name%20would%20like%20MemberName%20instead%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3ESecurityEvent%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20EventID%20%3D%3D%20%3C%2FSPAN%3E%3CSPAN%3E%224729%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Ewhere%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20%26gt%3B%20now(-%3C%2FSPAN%3E%3CSPAN%3E7%3C%2FSPAN%3E%3CSPAN%3Ed)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20TargetAccount%20%3D%20substring%20(TargetAccount%2C%20%3C%2FSPAN%3E%3CSPAN%3E10%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%20TargetAccount%20%3C%2FSPAN%3E%3CSPAN%3Econtains%3C%2FSPAN%3E%20%3CSPAN%3E%22365%22%3C%2FSPAN%3E%20%3CSPAN%3Eor%3C%2FSPAN%3E%3CSPAN%3E%20TargetAccount%20%3C%2FSPAN%3E%3CSPAN%3Econtains%3C%2FSPAN%3E%20%3CSPAN%3E%22o365%22%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eextend%3C%2FSPAN%3E%3CSPAN%3E%20Split_MemberName%20%3D%20split(MemberName%2C%3C%2FSPAN%3E%3CSPAN%3E%22%2C%22%3C%2FSPAN%3E%3CSPAN%3E%2C%3C%2FSPAN%3E%3CSPAN%3E0%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%20SubString_Split_MemberName%20%3D%20substring(Split_MemberName%2C%20%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%20Trimmed_SubString_Split_MemberName%20%3D%20trim(%3C%2FSPAN%3E%3CSPAN%3E'%22%5D'%3C%2FSPAN%3E%3CSPAN%3E%2CSubString_Split_MemberName)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%20%3C%2FSPAN%3E%3CSPAN%3Eproject%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%2C%20Trimmed_SubString_Split_MemberName%2C%20TargetAccount%2C%20SubjectUserName%20%7C%20%3C%2FSPAN%3E%3CSPAN%3Esort%3C%2FSPAN%3E%20%3CSPAN%3Eby%3C%2FSPAN%3E%3CSPAN%3E%20TimeGenerated%20desc%20nulls%20first%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1543373%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20extract%20Common%20name%20from%20Distinguished%20Name%20in%20Kusto%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1543373%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%3EHi%20Clive%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20inputs%20it%20triggered%20some%20thoughts%20and%20I%20got%20the%20right%20output%20with%20a%20small%20alteration%20in%20the%20added%20script%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi

Anyone have a Solution on how to extract Common name from Distinguished Name In Kusto I have tried parse, split, Sub string and what ever, but haven´t have a success with VB and Power Shell it is simple and a lot of examples to grab

 

From a table called Member Name  containing

CN=test test, OU=something, OU=Something, OU=Something

 

would like to display only the Name in output table

 Test Test

 

 

5 Replies
best response confirmed by ThereseSolimeno (Microsoft)
Solution

@Stig_hj 

 

Here are two ways, I prefer Parse to split in this case:

let MemberTable = datatable (cn:string)
[
"CN=test test, OU=something, OU=Something, OU=Something"
];
MemberTable
| parse cn with * "CN=" strcnName "," *
| project split(split(cn,",").[0],"=").[1] , strcnName

 

@Clive Watson  

Thanks for your reply, had parse and split in the loop, but could not get a clean output 

got something like [cn=Name Name]

instead of Name Name

@Stig_hj 

 

Maybe this a JSON rather than a string?

Could you copy & paste one line of the real output from the real table?  Please remove any PII but leave the format!

e.g.

 

MemberTable
| limit 1

 

@Clive Watson 

table name is MemberName

the string from the table to retrieve

CN=Name Name,OU=Users,OU=org,OU=O_City,OU=NN,OU=XX_ABC,OU=Group,DC=Domain,DC=com

 

Name Name

 

Tried this but have a "wrong" Column name would like MemberName instead

SecurityEvent
| where EventID == "4729"
| where TimeGenerated > now(-7d)
| extend TargetAccount = substring (TargetAccount, 10)
| where TargetAccount contains "365" or TargetAccount contains "o365"
| extend Split_MemberName = split(MemberName,",",0)
| extend SubString_Split_MemberName = substring(Split_MemberName, 5)
| extend Trimmed_SubString_Split_MemberName = trim('"]',SubString_Split_MemberName)
| project TimeGenerated, Trimmed_SubString_Split_MemberName, TargetAccount, SubjectUserName | sort by TimeGenerated desc nulls first

@Clive Watson 

 

Hi Clive 

Thanks for the inputs it triggered some thoughts and I got the right output with a small alteration in the added script