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!
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
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
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
This is exactly doing what i'm looking for. Great, many thanks for this.
@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
Thanks again @DalJeanis, I overlooked his value calculation logic. Fixed now.