Splunk Search

Count occurrences of all values of a field for another field

behudelson
Path Finder

Hello all, I am trying to count all the occurrences of keywords that show up in logs. Here is an example:

Here is lookup data:

Code, Keyword
1, Fuel
2, Velocity
3, Tire Pressure
4, Temperature
5, Windshield

Here are some logs:

Feb 4 2017
Fuel setting 80%. Tire Pressure Normal. 

Feb 5 2017
Velocity 80mph

Feb 6 2017
Windshield cracked. Tire Pressure low.  

This is the type of result I am looking for:

Keyword, Occurrences
Fuel, 1
Velocity, 1
Tire Pressure, 2
Temperature, 0 
Windshield, 1

Thank you for any suggestions you might have!

Tags (1)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

I've not tested it but you can try this.

step 1) Update your lookup table entries to add asterisk wildcard, like this (say name is yourLookup.csv)

Code, Keyword
 1, *Fuel*
 2, *Velocity*
 3, *Tire Pressure*
 4, *Temperature*
 5, *Windshield*

Step 2) Create lookup transform with wildcard match enabled (transforms.conf on your search head(s), will require restart)

[yourLookup]
 filename = yourLookup.csv
 match_type = WILDCARD(Keyword)

Step 3) search like this

your base search 
| lookup yourLookup Keyword as _raw OUTPUT Code
| stats count by Code 
| lookup yourLookup Code OUTPUT Keyword
| table Keyword count

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

I've not tested it but you can try this.

step 1) Update your lookup table entries to add asterisk wildcard, like this (say name is yourLookup.csv)

Code, Keyword
 1, *Fuel*
 2, *Velocity*
 3, *Tire Pressure*
 4, *Temperature*
 5, *Windshield*

Step 2) Create lookup transform with wildcard match enabled (transforms.conf on your search head(s), will require restart)

[yourLookup]
 filename = yourLookup.csv
 match_type = WILDCARD(Keyword)

Step 3) search like this

your base search 
| lookup yourLookup Keyword as _raw OUTPUT Code
| stats count by Code 
| lookup yourLookup Code OUTPUT Keyword
| table Keyword count

micahkemp
Champion

This absolutely works, multi-values and all. I suggest converting this to an answer so the poster can accept it.

0 Karma

elliotproebstel
Champion

Given the OP's sample event:

 Feb 4 2017
 Fuel setting 80%. Tire Pressure Normal. 

And the keyword listing:

 Code, Keyword
 1, Fuel
 2, Velocity
 3, Tire Pressure
 4, Temperature
 5, Windshield

Would the wildcard lookup OUTPUT both codes 1 and 3? Or would it only OUTPUT one of the codes for this event?

0 Karma

somesoni2
SplunkTrust
SplunkTrust

The max number of matches returned are controlled by max_matches which defaults to 1000, so I'm guessing both codes will be returned.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Is the lookup table static and managed by you? (can you can edit lookup content)

0 Karma

behudelson
Path Finder

Hi somesoni2, I manage the lookup. Yes.

0 Karma

behudelson
Path Finder

It's not static though. It is subject to change.

0 Karma
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 ...