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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...