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!

Automatic Discovery Part 1: What is Automatic Discovery in Splunk Observability Cloud ...

If you’ve ever deployed a new database cluster, spun up a caching layer, or added a load balancer, you know it ...

Real-Time Fraud Detection: How Splunk Dashboards Protect Financial Institutions

Financial fraud isn't slowing down. If anything, it's getting more sophisticated. Account takeovers, credit ...

Splunk + ThousandEyes: Correlate frontend, app, and network data to troubleshoot ...

 Are you tired of troubleshooting delays caused by siloed frontend, application, and network data? We've got a ...