topic Re: Group multiple Keys and count the values by status and table the results in Splunk Search

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

mkrauss1 — Thu, 20 Apr 2017 16:06:05 GMT

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!

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

somesoni2 — Thu, 20 Apr 2017 16:27:03 GMT

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

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

DalJeanis — Thu, 20 Apr 2017 16:56:57 GMT

@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

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

woodcock — Thu, 20 Apr 2017 17:07:31 GMT

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

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

somesoni2 — Thu, 20 Apr 2017 18:45:21 GMT

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

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

mkrauss1 — Fri, 21 Apr 2017 08:02:54 GMT

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