Splunk Search

Group multiple Keys and count the values by status and table the results

mkrauss1
Explorer

Good day,
i have the follwing key values:

CMD=LOOK ITEM1=APPLE ITEM2=APPLE ITEM3=ORANGE STAT=0
CMD=LOOK ITEM1=APPLE ITEM2=ORANGE ITEM3=ORANGE STAT=2
CMD=BOOK ITEM=APPLE STAT=1
CMD=BOOK ITEM=ORANGE STAT=0

As a result i'm looking for a table like:

Item   | Look | Book 
APPLE  | 1    | 1    
ORANGE | 2    | 0

I was tying the following search but that doesn't get me anywhere

(CMD=LOOK OR CMD=BOOK) |
eval ITEM=coalesce(ITEM1,ITEM2,ITEM3)  |
stats count(eval("STAT=1 AND CMD=BOOK")) as Book count(eval("STAT>=1 AND CMD=LOOK")) as Look by ITEM

Any help is appreciated, thanks!

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Give this a try

Updated

(CMD=LOOK OR CMD=BOOK) 
 | eval temp=CMD."##".STAT
 | fields temp ITEM*
 | untable temp Key Item
 | rex field=temp "(?<CMD>.+)##(?<STAT>.+)"
 | table CMD Item STAT | where (CMD="BOOK" AND STAT=1) OR (STAT>=1 AND CMD="LOOK") | chart count over Item by CMD

Run anywhere sample (first 3 lines are to generate sample data)

| gentimes start=-1| eval temp="CMD=LOOK ITEM1=APPLE ITEM2=APPLE ITEM3=ORANGE STAT=0##CMD=LOOK ITEM1=APPLE ITEM2=ORANGE ITEM3=ORANGE STAT=2##CMD=BOOK ITEM=APPLE STAT=1##CMD=BOOK ITEM=ORANGE STAT=0" | table temp | makemv temp delim="##" | mvexpand temp | rename temp as _raw | extract 
| eval temp=CMD."##".STAT
 | fields temp ITEM*
 | untable temp Key Item
 | rex field=temp "(?<CMD>.+)##(?<STAT>.+)"
 | table CMD Item STAT | where (CMD="BOOK" AND STAT=1) OR (STAT>=1 AND CMD="LOOK") | chart count over Item by CMD

View solution in original post

0 Karma

woodcock
Esteemed Legend

Like this:

| makeresults 
| eval raw="CMD=LOOK ITEM1=APPLE ITEM2=APPLE ITEM3=ORANGE STAT=0::CMD=LOOK ITEM1=APPLE ITEM2=ORANGE ITEM3=ORANGE STAT=2::CMD=BOOK ITEM=APPLE STAT=1::CMD=BOOK ITEM=ORANGE STAT=0"
| makemv delim="::" raw 
| mvexpand raw 
| rename raw AS _raw 
| fields - _time 
| rex "^CMD=(?<CMD>\S+)\s+ITEM=(?<ITEM>\S+)\s+STAT=(?<STAT>.*)"
| rex "^CMD=(?<CMD>\S+)\s+ITEM1=(?<ITEM1>\S+)\s+ITEM2=(?<ITEM2>\S+)\s+ITEM3=(?<ITEM3>\S+)\s+STAT=(?<STAT>.*)"

| rename COMMENT AS "Everything above fakes your events; everything below is your solution"

| search (STAT=1 AND CMD=BOOK) OR (STAT>=1 AND CMD=LOOK)
| table CMD ITEM ITEM1 ITEM2 ITEM3

| fillnull value=":"
| eval ITEMS=ITEM.":".ITEM1.":".ITEM2.":".ITEM3
| makemv delim=":" ITEMS
| chart count OVER ITEMS BY CMD
0 Karma

somesoni2
SplunkTrust
SplunkTrust

Give this a try

Updated

(CMD=LOOK OR CMD=BOOK) 
 | eval temp=CMD."##".STAT
 | fields temp ITEM*
 | untable temp Key Item
 | rex field=temp "(?<CMD>.+)##(?<STAT>.+)"
 | table CMD Item STAT | where (CMD="BOOK" AND STAT=1) OR (STAT>=1 AND CMD="LOOK") | chart count over Item by CMD

Run anywhere sample (first 3 lines are to generate sample data)

| gentimes start=-1| eval temp="CMD=LOOK ITEM1=APPLE ITEM2=APPLE ITEM3=ORANGE STAT=0##CMD=LOOK ITEM1=APPLE ITEM2=ORANGE ITEM3=ORANGE STAT=2##CMD=BOOK ITEM=APPLE STAT=1##CMD=BOOK ITEM=ORANGE STAT=0" | table temp | makemv temp delim="##" | mvexpand temp | rename temp as _raw | extract 
| eval temp=CMD."##".STAT
 | fields temp ITEM*
 | untable temp Key Item
 | rex field=temp "(?<CMD>.+)##(?<STAT>.+)"
 | table CMD Item STAT | where (CMD="BOOK" AND STAT=1) OR (STAT>=1 AND CMD="LOOK") | chart count over Item by CMD
0 Karma

mkrauss1
Explorer

This is exactly doing what i'm looking for. Great, many thanks for this.

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

@somesoni2, I think this is not exactly working. Here's some test code to get the test data into the format I believe you are expecting...

| makeresults
| eval mydata="CMD=LOOK ITEM1=APPLE ITEM2=APPLE ITEM3=ORANGE STAT=0!!!!CMD=LOOK ITEM1=APPLE ITEM2=ORANGE ITEM3=ORANGE STAT=2!!!! CMD=BOOK ITEM=APPLE STAT=1!!!! CMD=BOOK ITEM=ORANGE STAT=0"
| makemv delim="!!!!" mydata 
| mvexpand mydata 
| streamstats count as recno
| eval _raw = mydata, _time=now() 
| table _time _raw recno
| rex field=_raw "CMD=(?<CMD>\w+)"
| rex field=_raw "STAT=(?<STAT>\d+)"
| rex field=_raw max_match=0 "(?<ItemList>ITEM[^=]*=\w+)"
| mvexpand ItemList
| rex field=ItemList max_match=0 "(?<ItemName>ITEM[^=]*)=(?<Item>\w+)"
| eval {ItemName}=Item
| table _time recno CMD STAT ITEM*
| stats values(*) as * by recno
0 Karma

somesoni2
SplunkTrust
SplunkTrust

Thanks again @DalJeanis, I overlooked his value calculation logic. Fixed now.

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