Splunk Search

## Transform data measured in hour interval to day with some conditions.

Engager

I have data in the following format, measured in an interval of an hour.

 Date Restaurant id Food Code Average Order 1/1/2017 0:00 101 1 0.004 1/1/2017 1:00 101 1 0.004 1/1/2017 2:00 101 1 0.004 1/1/2017 3:00 101 1 0.004 1/1/2017 4:00 101 1 0.003 1/1/2017 5:00 101 1 0.003 1/1/2017 6:00 101 1 0.003 1/1/2017 7:00 101 1 0.003 1/1/2017 8:00 101 1 0.004 1/1/2017 9:00 101 1 0.003 1/1/2017 10:00 101 1 0.004 1/1/2017 11:00 101 1 0.004 1/1/2017 12:00 101 1 0.004 1/1/2017 13:00 101 1 0.005 1/1/2017 14:00 101 1 0.006 1/1/2017 15:00 101 1 0.006 1/1/2017 16:00 101 1 0.006 1/1/2017 17:00 101 1 0.005 1/1/2017 18:00 101 1 0.005 1/1/2017 19:00 101 1 0.005 1/1/2017 20:00 101 1 0.004 1/1/2017 21:00 101 1 0.004 1/1/2017 22:00 101 1 0.004 1/1/2017 23:00 101 1 0.004

I am trying to merge each 24-hour event into a single event and take an average of the average order. The output should be like this:

 Date Restaurant id Food Code Average Order 1/1/2017 101 1 0.003

I tried using a time chart, bin every time I get all 24 rows. Since I have data measured every hour which is too specific, I want to turn it to a day interval instead and take the average for the average order.

Help would be highly appreciated.

Labels (3)

• ### timechart

1 Solution
SplunkTrust

Probably means it isn't an epoch datetime anymore - treat it as a string

``| eval date=mvindex(split(date," "),0)``
SplunkTrust

Firstly - why do you want to have average from averages? Wouldn't average over the whole day make more sense? (it's not the same value!).

Secondly - there's not much point in manually binning over time if you can simply do a timechart.

Why not just

`<<your initial search>> | timechart span=1d avg(order) by restaurant code`

Engager

It worked but for the Date column, all the data appears to be 2017-01-01 00:00. The date should increment like

2017-01-01

2017-01-02

.....

SplunkTrust
``| fieldformat date=strftime(date,"%Y-%m-%d")``
Engager

The measurement date disappeared.

SplunkTrust

Probably means it isn't an epoch datetime anymore - treat it as a string

``| eval date=mvindex(split(date," "),0)``
Engager

This is the output is my command chained correctly?

Engager

Actually, the date should be Date but still, I am getting all the rows instead of 1 for 24-hour interval.

SplunkTrust

Your date is not in epoch datetime format - you need to convert it

``| eval Date=strptime(Date,"%d/%m/%Y")``
SplunkTrust

Assuming Date is an epoch datetime, e.g. _time

``````| bin Date span=1d
| stats avg("Average Order") as "Average Order" by Date "Restaurant id" "Food Code"``````
Get Updates on the Splunk Community!

#### Announcing Scheduled Export GA for Dashboard Studio

We're excited to announce the general availability of Scheduled Export for Dashboard Studio. Starting in ...

#### Extending Observability Content to Splunk Cloud

Watch Now!   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to leverage ...

#### More Control Over Your Monitoring Costs with Archived Metrics GA in US-AWS!

What if there was a way you could keep all the metrics data you need while saving on storage costs?This is now ...