How to parse and extract OU data

[Vfinney]

I am trying to parse and extract the field data from AD distinguishedName field using regex, but I'm not having too much luck.

Here is what I'm looking at:

CN=AVB6F9974,OU=Farley PC's, OU=Kansas City, OU=Workstations,DC=lop,DC=local
CN=AVB6F9975,OU=Booth PC's, OU=Scott Building, OU=Workstations,DC=lop,DC=local
CN=KBOD6F9975,OU=Booth PC's, OU=Zale Building, OU=Workstations,DC=lop,DC=local

The end goal is to create a report that looks like the table below.

Workstation   Location       Operating System  IP address 
AVB6F9974     Kansas City    Windows 10
AVB6F9975     Scott Building Windows 10
KBOD6F9975    Zale Building  Windows 10

[Vfinney]

I should have provided more data in my original post. Here are examples of the the entire distinguishedName field data:

CN=KDORSC22PHRR2,OU=Desktops,OU=Taxation,OU=Scott Building,OU=Workstations,DC=kdor,DC=ks,DC=gov
CN=KDORKC9RJFMN2,OU=Booth PC's,OU=Kansas City,OU=Workstations,DC=kdor,DC=ks,DC=gov
CN=RV20013743,OU=Laptops,OU=Legal,OU=Mills Building,OU=Workstations,DC=kdor,DC=ks,DC=gov

[wenthold]

This may be difficult based on your OU structure, but try something like this:

rex "CN=(?<commonName>[^\,]+)\,.*?(?<!\\)\,\s*OU=(?<location>.*?(?<!\\))\,\s*OU=.*?\,DC"

It will require that the location field is always the child OU of the Workstation (or Server, etc.) OU, and that is in turn is always the child of "DC=lop,DC=local". This command will always run against the full _raw field but if you have the DN in a separate field already it might be better to use "rex field= ..."

Link to regex101, which can explain each of the steps: https://regex101.com/r/3H4CO9/3

The first regex is pretty computationally expensive, if your OU structure is strict and the location will always be in the same place, and you don't use commas in any locations this is more efficient:

CN=(?<commonName>[^\,]+)\,OU=.*?\,\s*OU=(?<location>[^\,]+)\,\s*OU=

[Vfinney]

When trying the second string, received the following error Error in 'SearchParser': Missing a search command before '^'. Error at position '83' of search query 'search sourcetype=ActiveDirectory operatingSystem=...{snipped} {errorcontext = mmonName>[^\,]+)\,OU=}'.

[wenthold]

Here is the fully formatted example with the sample data you provided:

 | makeresults 
 | eval test=" CN=AVB6F9974,OU=Farley PC's, OU=Kansas City, OU=Workstations,DC=lop,DC=local|CN=AVB6F9975,OU=Booth PC's, OU=Scott Building, OU=Workstations,DC=lop,DC=local|CN=KBOD6F9975,OU=Booth PC's, OU=Zale Building, OU=Workstations,DC=lop,DC=local"
 | makemv delim="|" test | mvexpand test
 | eval _raw=test
 | rex "CN=(?<commonName>[^\,]+)\,OU=.*?\,\s*OU=(?<location>[^\,]+)\,\s*OU="

[rbechtold]

Hey Vfinney,

Give this a try for me:

...BASE SEARCH...
| rex field=distinguishedName max_match=0 "(?<distinguishedName>[^\n]+)" 
| mvexpand distinguishedName 
| rex field=distinguishedName max_match=0 "CN=(?<Workstation>[^\,]+)\,OU=[^\,]+\,\sOU=(?<Location>[^\,]+)[^\n]+"
| table Workstation Location operating_system ip_address

I tried working around not using mvexpand, but in this case I believe it is the only way to make the search function the way you want it to.

Please let me know if there are any unforeseen problems you run into and I will try to help you correct them!

[rbechtold]

Hey Vfinney!

Is that data all one log, or is that three individual logs?

Also, where are you getting the Operating System data and the IP Address data from? I'm not seeing it in the dataset you provided.

[Vfinney]

Yes, it's all from one Active Directory log. As for the OS and IP, those are just other values from my search query. They are not part of the the issue.