How do you pass a case or if statement value to a lookup table search?

[doodoodonk]

Background:

I have having some issues with LDAP Identities that have the same identity name so I was trying to carve out the domain name from the workstation name "e.g., somesystem.domain.whatever.com" --> "domain.whatever.com" and use that newly created value to do a case or if comparison to run one lookup search over another.

sourcetype=WinEventLog:PowerShell `PS_Command_Filter` `PS_Cmd_Rules` NOT [|inputlookup Powershell_Path_WL.csv | fields Powershell_WL | rename Powershell_WL as Path] |lookup Powershell_Blacklist BlacklistedPS as Command_Line OUTPUT Alert | eval Command_Line=substr(Command_Line,1,100) | rex field=ComputerName "\.(?<Domain>.*)" | table _time, ComputerName, EventCode, Type, Sid, LogName, Path, ScriptBlock_ID, Alert, Command_Line, recipients | where isnotnull(Alert)| eval usrlookup=case(Domain=="somedomain.whatever.com", "ldap_identities_sat", Domain=="someotherdomain.whatever.com", "ldap_identities") |lookup $usrlookup$ sid as Sid OUTPUT identity as user, nick as user_nick, bunit as user_bunit, category as user_category, email as user_email, watchlist as user_watchlist, work_city as user_work_city | makemv delim="|" allowempty=true user_category

Basically I am trying to determine which ldap lookup to use based on the domain comparison from the ComputerName results of regex field Domain. I want to pass the result to the lookup table to use, but I couldn't really find any answer that showed a really good example of how this could be done other than on dashboards as an input token, but I want this as a correlation search to run and alert off of. The ldap lookup would identify the proper user based on the Domain.

Emphasis of what I am trying to do code wise:

| eval usrlookup=case(Domain=="somedomain.whatever.com", "ldap_identities_sat", Domain=="someotherdomain.whatever.com", "ldap_identities") |lookup $usrlookup$ sid as Sid OUTPUT identity as user, nick as user_nick, bunit as user_bunit, category as user_category, email as user_email, watchlist as user_watchlist, work_city as user_work_city

[elliotproebstel · Accepted solution]

When I've hit a similar issue, my approach was similar to yours, but I couldn't figure out a way to pass the lookup table name as a variable. So instead, I sorted the values I needed to lookup into different variables, based on the if/case statement describing them. In your case, I think that would look like this:

sourcetype=WinEventLog:PowerShell `PS_Command_Filter` `PS_Cmd_Rules` NOT 
[| inputlookup Powershell_Path_WL.csv 
 | fields Powershell_WL 
 | rename Powershell_WL as Path ] 
| lookup Powershell_Blacklist BlacklistedPS as Command_Line OUTPUT Alert | eval Command_Line=substr(Command_Line,1,100) 
| rex field=ComputerName "\.(?<Domain>.*)" 
| table _time, ComputerName, EventCode, Type, Sid, LogName, Path, ScriptBlock_ID, Alert, Command_Line, recipients 
| where isnotnull(Alert)
| eval somedomain_sid=if(Domain=="somedomain.whatever.com", Sid, NULL)
| eval someotherdomain_sid=if(Domain=="someotherdomain.whatever.com", Sid, NULL) 
| lookup ldap_identities_sat sid as somedomain_sid OUTPUT identity as user, nick as user_nick, bunit as user_bunit, category as user_category, email as user_email, watchlist as user_watchlist, work_city as user_work_city 
| lookup ldap_identities sid as someotherdomain_sid OUTPUT identity as user, nick as user_nick, bunit as user_bunit, category as user_category, email as user_email, watchlist as user_watchlist, work_city as user_work_city 
| makemv delim="|" allowempty=true user_category

With this approach, the Sid values are separated into somedomain_sid and someotherdomain_sid based on the Domain value, and then the appropriate lookups are consulted for each.

[doodoodonk]

That worked perfectly. Thanks.

[somesoni2]

One dirty workaround would be like this:

|lookup ldap_identities_sat sid as Sid OUTPUT identity as user, nick as user_nick, bunit as user_bunit, category as user_category, email as user_email, watchlist as user_watchlist, work_city as user_work_city
|lookupldap_identities sid as Sid OUTPUT identity as user#, nick as user_nick#, bunit as user_bunit#, category as user_category#, email as user_email#, watchlist as user_watchlist#, work_city as user_work_city#
| foreach *# [ eval "<<MATCHSTR>>"=case(Domain=="somedomain.whatever.com", '<<MATCHSTR>>', Domain=="someotherdomain.whatever.com", '<<FIELD>>') ]
| fields - *#

Basically run both the lookup but use different output field names from one lookup. The based on domain, use specific field (either output field from lookup 1 or lookup 2)