Splunk Search

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

doodoodonk
Engager

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
0 Karma
1 Solution

elliotproebstel
Champion

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.

View solution in original post

0 Karma

elliotproebstel
Champion

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.

0 Karma

doodoodonk
Engager

That worked perfectly. Thanks.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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)

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...