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
Revered Legend

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
Revered Legend

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
Revered Legend

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
Revered Legend

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!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...