Splunk Search

Is it possible to use a lookup table to populate a search where the values in the table become a series of OR statements

Admiral_Marith
Explorer

Right now I am tasked with creating a report for a department showing who is using elevated privileges in Linux and for what commands.

That search looks somewhat like this (I've anonymized the server names because of policy, but it will give you the idea.

index=* sudo host=[LOGHOST] ( ("[serverytype1]dl002" ) OR ("[serverytype1]dl004" ) OR ("[serverytype1]pl007" ) OR ("[serverytype1]pl008" ) OR ("[serverytype1]pl009" ) OR ("[serverytype1]pl010" ) OR ("[serverytype1]pl011" ) OR ("[serverytype1]pl012" ) OR ("[serverytype1]pl013" ) OR ("[serverytype1]pl014" ) OR ("[serverytype1]tl002" ) OR ("[serverytype1]tl005" ) OR ("[serverytype1]tl006" ) OR ("[serverytype1]tl009" ) OR ("[serverytype1]tl010" ) OR ("[serverytype1]tl011" ) OR ("[serverytype1]tl012" ) OR ("[serverytype1]tl013" ) OR ("[serverytype3]pl001" ) OR ("[serverytype3]sl001" ) OR ("[serverytype4]pl001" ) OR ("[serverytype4]pl002" ) OR ("[serverytype4]pl003" ) OR ("[serverytype4]pl004" ) OR ("[serverytype4]pl005" ) OR ("[serverytype4]pl006" ) OR ("[serverytype4]pl007" ) OR ("[serverytype4]pl008" ) OR ("[serverytype4]tl001" ) OR ("[serverytype2]pl003" ) OR ("[serverytype2]pl004" ) OR ("[serverytype2]pl005" ) OR ("[serverytype2]pl006" ) OR ("[serverytype2]pl007" ) OR ("[serverytype2]pl008" ) OR ("[serverytype2]pl009" ) OR ("[serverytype2]tl001" ) OR ("[serverytype2]tl002" ) OR ("[serverytype2]tl005" ) OR ("[serverytype1]pl016" ) OR ("[serverytype1]pl015" ) )  ( NOT scomact ) (NOT USER=root)

So I have a lookup table with all of the server names that are listed above. The problem with doing either method, is that either the list in the search needs to be updated every time (Today alone 12 server decommissions for this group and 6 server build request last week) or the lookup table needs to be updated.

The syslog data is coming in, in most cases via remote syslog to a loghost which then sends it in via a universal forwarder. This makes all of the data come in as hostname '[LOGHOST]' so I cannot do a host=name. Sourcetype=sudo is too limiting as I have some sudo log stuff coming in on auth-priv sourctype and a couple others. Could we normalize that stuff? Yeah probably. Just haven't.

The server names are split between a naming convention that would allow pattern matching, and an older naming convention where servers were named after Minnesota lakes and thus not able to be pattern matched for all of the hosts.

If this was a scripting language I'd just loop through the list of servers and grab the data that met the date ranges and stuff it into a file, salt to taste and serve. How do I create a less cumbersome search than the above example?

0 Karma

erickyi
Path Finder

I know this was a very old question. I encountered a similar requirement as our syslog is forwarded from a forwarder

It would help you if you do a regex field extraction to break down the fields; identify your user, "hostname" (which is your [serverType]p100n), your serverType (if you want), your linux command. This would be your input to your dynamic lookup (see later)
then you can configure a csv file as the input to all your "hostname" (this is not your lookup in the traditional splunk sense )
last, code up a dynamic lookup (dynamic lookup) that uses the above csv file to perform the search.

Just sharing my experience.

0 Karma

Magnus_001
Explorer

Hi,

You can do this with the following search. For example, if your lookup table is called "lookup_host" with host=server1, host=server2, host=server3...etc. your search would be:

index=your_index_here sudo host=your_LOGHOST_here | inputlookup lookup_host | rename host as query | format (NOT USER=root)

Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...