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!
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
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
This absolutely works, multi-values and all. I suggest converting this to an answer so the poster can accept it.
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?
The max number of matches returned are controlled by max_matches
which defaults to 1000, so I'm guessing both codes will be returned.
Is the lookup table static and managed by you? (can you can edit lookup content)
Hi somesoni2, I manage the lookup. Yes.
It's not static though. It is subject to change.