Splunk Search

Is there a way to display Count per hr for last 24hrs with Average per hr for the last 30 days as an overlay?

kozanic_FF
Path Finder

Hi Splunk Gurus,

Hoping someone out there might be able to provide some assistance with this one.

I have a requirement to be able to display a count of sales per hr for the last 24 hrs (with flexibility to adjust that as needed), but also to show the average sales per hr for the last 30 days as an overlay.

First bit is easy, second bit has me stumped. Have spent the last hr going through various posts looking or solutions, but nothing quite right for my situation.

Initial search would be something like this:

index=* source=flow_stage*order_header
          | timechart span=1h count AS SalesPerHr by WebsiteCountry

giving a graph like this:
alt text
What I need to add is the overlay line that shows the average sales per hr based on the last 30 days sales data.

Hopefully this is clear - any help gratefully appreciated!

EDIT:
As requested - some sample data (does not contain all fields as some contain sensitive data).

2019-02-22 07:21:14.823, order_no="AU02051432", nav_order_no="AU02051432", order_date="2019-02-21 20:17:13.0", invoice_no="07324912", customer_no="CA06908192", newsletter_subscription="0", currency="AUD", total_net_price="127.22", total_tax="12.73", total_gross_price="139.95", dw_order_filename="Order_AU02051432.xml", merchandize_total_net_price="115.45", merchandize_total_tax="11.55", merchandize_total_gross_price="127.0", adj_merchandize_total_net_price="115.45", adj_merchandize_total_tax="11.55", adj_merchandize_total_gross_price="127.0", status="0", inserted="2019-02-22 07:21:14.823", updated="2019-02-22 07:22:03.0", status_shipping="NOT_SHIPPED"
2019-02-22 07:01:17.127, order_no="AU02051484", nav_order_no="AU02051484", order_date="2019-02-21 19:51:16.0", invoice_no="07325413", customer_no="CA06908750", newsletter_subscription="0", currency="AUD", total_net_price="83.59", total_tax="8.36", total_gross_price="91.95", dw_order_filename="Order_AU02051484.xml", merchandize_total_net_price="71.82", merchandize_total_tax="7.18", merchandize_total_gross_price="79.0", adj_merchandize_total_net_price="71.82", adj_merchandize_total_tax="7.18", adj_merchandize_total_gross_price="79.0", status="0", inserted="2019-02-22 07:01:17.127", updated="2019-02-22 07:02:04.0", status_shipping="NOT_SHIPPED"
2019-02-22 06:51:13.643, order_no="AU02051483", nav_order_no="AU02051483", order_date="2019-02-21 19:39:23.0", invoice_no="07325412", customer_no="CA06908749", newsletter_subscription="0", currency="AUD", total_net_price="252.72", total_tax="25.28", total_gross_price="278.0", dw_order_filename="Order_AU02051483.xml", merchandize_total_net_price="252.72", merchandize_total_tax="25.28", merchandize_total_gross_price="278.0", adj_merchandize_total_net_price="252.72", adj_merchandize_total_tax="25.28", adj_merchandize_total_gross_price="278.0", status="0", inserted="2019-02-22 06:51:13.643", updated="2019-02-22 06:52:03.0", status_shipping="NOT_SHIPPED"
0 Karma
1 Solution

pkeenan87
Communicator

You can use addtotals and eventstats after your timechart command to compute the average of the SalesPerHr field. I suggest adding an index to your search to optimize performance. Here is the syntax you will need

index=your_index_here source=flow_stage*order_header
| timechart span=1h count AS SalesPerHr by WebsiteCountry
| addtotals
| eventstats avg(Total) as average
| fields - total

Then on the visualization tab select Format > Chart Overlay and select "average" as your overlay field

alt text

View solution in original post

0 Karma

pkeenan87
Communicator

You can use addtotals and eventstats after your timechart command to compute the average of the SalesPerHr field. I suggest adding an index to your search to optimize performance. Here is the syntax you will need

index=your_index_here source=flow_stage*order_header
| timechart span=1h count AS SalesPerHr by WebsiteCountry
| addtotals
| eventstats avg(Total) as average
| fields - total

Then on the visualization tab select Format > Chart Overlay and select "average" as your overlay field

alt text

0 Karma

kozanic_FF
Path Finder

Hi @pkeenan87,

Thanks for your response.

I have tested this but it doesn't work. In the chart overly area, I get a overlay value of "Null" which when I select only gives me the average line (which is also I'm assuming is also only based on the same period that the report is run for??), no longer shows the bars for each hr.

0 Karma

pkeenan87
Communicator

@kozanic_FF Whoops, sorry about that. Because you are using timechart we will need to add the totals first before we can compute the average. I have updated my post accordingly

0 Karma

kozanic_FF
Path Finder

Thanks @pkeenan87 ,

A minor tweak - change "- total" to "- NULL" and this works now.

While this is great if I just wanted to add the sales average for the report range, I need to be able to show the Sales Per Hour Average for the last 30 days on the overly.

Any thoughts on how I might achieve this?

0 Karma

pkeenan87
Communicator

Thats a little tricky, give this a try:

index=your_index_here source=flow_stage*order_header
| timechart span=1h count AS SalesPerHr by WebsiteCountry
| eval date_hour = strftime(_time, "%H")
| join date_hour [
search earliest=-30d index=your_index_here source=flow_stage*order_header
| timechart span=1h count
| eval date_hour = strftime(_time, "%H")
| stats avg(count) as average by date_hour
| eval average = round(average)
| fields date_hour average ]
| fields - date_hour

0 Karma

kozanic_FF
Path Finder

Thanks @pkeenan87, that seems to be exactly what I was after!

0 Karma

somesoni2
SplunkTrust
SplunkTrust

So this overlay should be a flat line with average sales per hour for all countries and all hours (single values)??

0 Karma

kozanic_FF
Path Finder

Hi @somesoni2,

what I'd like is a moving line that for each hr, shows the average sales for that hr in the last 30 days, based on total sales for that hr.

The line would go up and down in accordance with hourly average.

0 Karma

p_gurav
Champion

Could you please share some sample events?

0 Karma
Get Updates on the Splunk Community!

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 ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...