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!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...