SOLVED

How to extract Common name from Distinguished Name in Kusto Query

Copper 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

 

@CliveWatson  

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

 

@CliveWatson 

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

@CliveWatson 

 

Hi Clive 

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

1 best response

Accepted Solutions
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

 

View solution in original post