Splunk Search

Query against a lookup table

spammenot66
Contributor

If I have a lookup table of 5groups, is it possible to have SPLUNK query activity against the groups in the lookup table
Lookup table
Group1
Group2
Group3
Group4
Group5

Groups1/3/5 has activity, Group 2/4 has no activity

My logs currently tracks hits each time the group has activity. How do i get splunk to query against the lookup table and show in a report:
Groups with Activity: 3
Groups with no Activity: 2

Tags (1)
0 Karma
1 Solution

woodcock
Esteemed Legend

Assuming your lookup is called MyListOfGroups and has a single field called Group and your event data also has a field called Group and can be returned by a search string represented by EventDataSearchString, like this:

EventDataSearchString | append [|inputlookup MyListOfGroups] | stats count by Group | eval category=if((count=1), "Groups with no Activity", "Groups with activity") | stats count by category

You may have to do some field renaming so that both the file and the events have the same field name (which I called Group).

View solution in original post

woodcock
Esteemed Legend

Assuming your lookup is called MyListOfGroups and has a single field called Group and your event data also has a field called Group and can be returned by a search string represented by EventDataSearchString, like this:

EventDataSearchString | append [|inputlookup MyListOfGroups] | stats count by Group | eval category=if((count=1), "Groups with no Activity", "Groups with activity") | stats count by category

You may have to do some field renaming so that both the file and the events have the same field name (which I called Group).

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...