Splunk Search

Using lookup tables to evaluate multiple conditions

New Member

We're evaluating using Splunk to identify changes to a system's state (like installed apps, listening ports, ACLs, etc.). I'm trying to create a dynamic eval/case based on data from a lookup table (essentially, there will be a large number of eval/case to run, and it would be preferable, if I could use a 'foreach' from a lookup table). Many of the 'changes' we'll see will be expected changes based on normal behavior of the OS, but we'd still need to "justify" them to an auditor.

Here's what an event looks like:
alt text

Here's what a search looks like:

index="index" sourcetype="sourcetype" host=host
| stats earliest(_time) as earliestTime,
     latest(_time) as latestTime,
     count(_time) as Event_Count,
     values(OwningProcess) as OwningProcess_values
     by ProcessName,ProcessPath,OperationalStatus,LocalPort,LocalAddress
| eventstats max(Event_Count) as Event_Count_Max
| eval earliestTime=strftime(earliestTime,"%Y-%m-%d %H:%M:%S")
| eval latestTime=strftime(latestTime,"%Y-%m-%d %H:%M:%S")
| eval UDP_Listener_Add_Remove=if(Event_Count!=Event_Count_Max,"True","False")
| eval UDP_Listener_Add_Remove=if((UDP_Listener_Add_Remove="True") AND (ProcessPath="C:\Windows\system32\lsass.exe") AND (LocalPort > 49152),"Expected","True")
| eval Justification=if((UDP_Listener_Add_Remove="Expected") AND (ProcessPath="C:\Windows\system32\lsass.exe") AND (LocalPort > 49152),"Lsass.exe is the Local Security Authority Subsystem Service, responsible for authentication and authorization. This process utilizes the dynamic port ranges 49152-65535","")
| where like(UDP_Listener_Add_Remove,"%")
| sort ProcessName,LocalPort,LocalAddress
| table earliestTime,latestTime,Event_Count,ProcessName,ProcessPath,OwningProcess_values,LocalPort,LocalAddress,UDP_Listener_Add_Remove,Justification

Here's what the results look like:
alt text

I'd like to replace this portion of the search with a dynamic search from a lookup table, if possible (or at least some other scalable method):

| eval UDP_Listener_Add_Remove=if((UDP_Listener_Add_Remove="True") AND (ProcessPath="C:\Windows\system32\lsass.exe") AND (LocalPort > 49152),"Expected","True")
| eval Justification=if((UDP_Listener_Add_Remove="Expected") AND (ProcessPath="C:\Windows\system32\lsass.exe") AND (LocalPort > 49152),"Lsass.exe is the Local Security Authority Subsystem Service, responsible for authentication and authorization. This process utilizes the dynamic port ranges 49152-65535","")

I'm just spit balling here, but, I'd imagine the lookup table would look something like this:

  • key=processPath, value=c:\windows\system32\lsass.exe, condition=UDPListenerAdd_Remove, operator=EQ, Eval=True, Justification=Some justification string
  • key=processPath, value=c:\windows\system32\lsass.exe, condition=LocalPort, operator=GT, Eval=49152, Justification=Some justification string

Then the search would so something along these lines:
Theory:

  • If Key is equal to Value
  • Foreach condition (there are two in this case)
  • Create if statement and JOIN with AND

Example:

  • If ProcessPath=C:\Windows\system32\lsass.exe
  • If (UDPListenerAdd_Remove = “True”) AND (LocalPort > 49152)
  • Then Justification = “Some justification string…”

Am I heading in the right direction or should I be thinking about this differently? How would you go about accomplishing a large number of case/eval statements for a large set of data?

0 Karma

Esteemed Legend

You are over-complicating it; try using a MyRulesLookup.csv Lookup table like this:

condition,                                                                               Justification
((processPath=="C:\windows\system32\lsass.exe") AND (UDP_Listener_Add_Remove="True")),   Some justification string
((processPath=="C:\windows\system32\lsass.exe") AND (LocalPort>49152)),                  Some other justification string

Then your search should be like this:

index=YouShouldAlwaysSpecifyAnIndex AND sourcetype=AndSourcetypeToo AND
[|inputlookup MyRulesLookup.csv
| table condition
| stats values(condition) AS search
| eval search = mvjoin(search, " OR ")
| return $search ]
0 Karma

Builder

Hey @BHumphrey_Tep - How large is your dynamic set? at some stage, you should be having a fixed set in your loookup table that can go into your conditional statements.

I got a similar use case in a dashboard rather than a search query. If you wanted it in a dashboard, it can be achieved as there are options to select multiple conditions and line formatting is easy in dashboards rather than search queries/data sets.

0 Karma

New Member

Hey @nareshinsvu - It will grow over time, likely in the hundreds and eventually even thousands. It's essentially a list of all state changes on a server that we would expect, such as lsass.exe listening on dynamic port ranges (49152+), OneSync service stopping and starting, some file in the system files that is regularly changed by a system process, etc. Also, it's a bit tricky as well because some of these "case" or "eval" statements might have 1 condition, some might have 2, and some might have three+. For instance, the case statement for lsass.exe would be, if name is lsass.exe, if pathname is c:\windows\system32\lsass.exe, if localport is between 49152 and 65535, then it's expected. But, let's say it was lsass.exe and the port range was dynamic, but the pathname was c:\temp\lsass.exe, then that's NOT expected. Also, to make it more complicated, operators need to be dynamic (equals, less than, greater than, etc.), so I'd have to account for those in the lookup table as well.

0 Karma