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!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...