Okay, so I'm trying to create a funnel in Splunk. I have a multivalue field, I need to recalculate the values into percentages and then present them grouped into dates.
I get as far as this: Where all the data shows, in percentages, but only in a single clump based on the time range.
host=hostname* Identifier=name EventType=* | rex "(?<DeviceId>[0-9a-zA-Z]+)\s+event" | eventstats dc(DeviceId) AS all_visitors | search (EventType="type1" OR EventType="type2" OR EventType="type3") | stats values(all_visitors) AS all_visitors dc(DeviceId) AS dc_event_type by EventType | eval percentage=round(dc_event_type/all_visitors*100,2) | fields - dc_event_type | transpose | rename column as " " "row 1" as 1 "row 2" as 2 "row 3" as 3
The eval command seems to need the stats command, but I can't use timechart if I use stats and vice versa. And I can't figure out a way to lists events by days using stats.
I'm fairly (extremely) new to Splunk, so if you could be a little overly descriptive in your suggestions, I would appreciate it 🙂
Any suggestions are welcome 🙂
Here is the trick you need to use BOTH stats
and timechart
; let us suppose you need to use ... | timechart span=1d
someplace (the 1d
is the important part that we need to identify and copy to the bucket
command):
.... | bucket _time span=1d | stats <yourStuff> BY <moreYourStuff>,_time | ... | timechart span=1d <yourStuff and moreYourStuff>
Notice that we propagated the _time
that the timechart
command needs later on down the line and yet did not disturb the work that stats
has to do even though we added _time
to it in the BY
portion (because the exact same sorting out is going to happen later on when it hits the timechart
command).
So something like this should work for you (notice the other cleanup, too); be sure to change | timechart span=1h ???
into what you need the ???
to be:
host=hostname* Identifier=name EventType=* | rex "(?<DeviceId>[0-9a-zA-Z]+)s+event" | eventstats dc(DeviceId) AS all_visitors | search (EventType="type1" OR EventType="type2" OR EventType="type3") | bucket _time span=1h | stats first(all_visitors) AS all_visitors dc(DeviceId) AS dc_event_type by EventType,_time | eval percentage=round(dc_event_type/all_visitors*100,2) | timechart span=1h ??? | fields - dc_event_type | transpose | rename column as " " row* AS *
Here is the trick you need to use BOTH stats
and timechart
; let us suppose you need to use ... | timechart span=1d
someplace (the 1d
is the important part that we need to identify and copy to the bucket
command):
.... | bucket _time span=1d | stats <yourStuff> BY <moreYourStuff>,_time | ... | timechart span=1d <yourStuff and moreYourStuff>
Notice that we propagated the _time
that the timechart
command needs later on down the line and yet did not disturb the work that stats
has to do even though we added _time
to it in the BY
portion (because the exact same sorting out is going to happen later on when it hits the timechart
command).
So something like this should work for you (notice the other cleanup, too); be sure to change | timechart span=1h ???
into what you need the ???
to be:
host=hostname* Identifier=name EventType=* | rex "(?<DeviceId>[0-9a-zA-Z]+)s+event" | eventstats dc(DeviceId) AS all_visitors | search (EventType="type1" OR EventType="type2" OR EventType="type3") | bucket _time span=1h | stats first(all_visitors) AS all_visitors dc(DeviceId) AS dc_event_type by EventType,_time | eval percentage=round(dc_event_type/all_visitors*100,2) | timechart span=1h ??? | fields - dc_event_type | transpose | rename column as " " row* AS *
Thank you for the answer 🙂
I realize I might not have a multi-value field, but have three distinct fields, with one value each. In either case, I get zero values now, but I see dates. I'm sorry it's not much to go by.
Strip off everything from | fields
to the end and see if it makes sense at that point. What did you use for ???
?
Appears I'd somehow missed final part of your post, so yeah, that did the trick. Thank you 🙂