Splunk Search

How to list values for a multivalue field by both date and percentage?

Hartmannish
Explorer

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 🙂

0 Karma
1 Solution

woodcock
Esteemed Legend

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 *

View solution in original post

woodcock
Esteemed Legend

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 *

Hartmannish
Explorer

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.

0 Karma

woodcock
Esteemed Legend

Strip off everything from | fields to the end and see if it makes sense at that point. What did you use for ????

0 Karma

Hartmannish
Explorer

Appears I'd somehow missed final part of your post, so yeah, that did the trick. Thank you 🙂

0 Karma
Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...