How do I use lookups where field has two formats?

capilarity — Mon, 26 Nov 2018 14:06:23 GMT

Owing to the way exchange outputs log files, for some reason we get two versions of the cs_username field

username eg employeebob

Email address eg Bob.Employee@work.com

Both versions exist in the active directory lookup file we have as "sAMAccountName" and "mail" and I want to get an output field of "Email Address".

I can get lookup files to work on either version during a search, but not on both at the same time in the same search.

Is there a way of running two lookups on the same file in the same search against the same field?

Was looking at the "if" and "where" options, but they don't appear to work. Also, I tried to set two lookups in the same search.....

index=msexchange sourcetype="MSWindows:2008R2:IIS" WebApplication="Microsoft-Server-ActiveSync" Cmd=Sync 
| lookup User_Info mail AS cs_username OUTPUT l AS Location, title AS Title, department AS Department, mail AS "Email Address"
| lookup User_Info sAMAccountName AS cs_username OUTPUT l AS Location, title AS Title, department AS Department, mail AS "Email Address"

Re: How do I use lookups where field has two formats?

FrankVl — Mon, 26 Nov 2018 14:10:24 GMT

And the issue is that the second lookup in your search clears the values set by the first lookup, when the username is in the format of an email?

Try it with OUTPUTNEW instead of OUTPUT, so it doesn't overwrite anything that resulted from the first lookup.

Alternatively: modify your lookup to contain an extra column say key, which is multivalued and contains both samaccountname and mail values. Then you can do the lookup once, against that key field.

topic Re: How do I use lookups where field has two formats? in Splunk Search

How do I use lookups where field has two formats?

Re: How do I use lookups where field has two formats?