Splunk Search

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

sndpgiri
Engager

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

DateRestaurant idFood CodeAverage Order
1/1/2017 0:0010110.004
1/1/2017 1:0010110.004
1/1/2017 2:0010110.004
1/1/2017 3:0010110.004
1/1/2017 4:0010110.003
1/1/2017 5:0010110.003
1/1/2017 6:0010110.003
1/1/2017 7:0010110.003
1/1/2017 8:0010110.004
1/1/2017 9:0010110.003
1/1/2017 10:0010110.004
1/1/2017 11:0010110.004
1/1/2017 12:0010110.004
1/1/2017 13:0010110.005
1/1/2017 14:0010110.006
1/1/2017 15:0010110.006
1/1/2017 16:0010110.006
1/1/2017 17:0010110.005
1/1/2017 18:0010110.005
1/1/2017 19:0010110.005
1/1/2017 20:0010110.004
1/1/2017 21:0010110.004
1/1/2017 22:0010110.004
1/1/2017 23:0010110.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:

 

DateRestaurant idFood CodeAverage Order
1/1/201710110.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)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

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

View solution in original post

0 Karma

PickleRick
SplunkTrust
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

 

0 Karma

sndpgiri
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

.....

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| fieldformat date=strftime(date,"%Y-%m-%d")
0 Karma

sndpgiri
Engager

The measurement date disappeared. 

 

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

| eval date=mvindex(split(date," "),0)
0 Karma

sndpgiri
Engager

sndpgiri_0-1633171255611.png

 

This is the output is my command chained correctly?

 

0 Karma

sndpgiri
Engager

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

sndpgiri_0-1633171593819.png

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

| eval Date=strptime(Date,"%d/%m/%Y")
0 Karma

ITWhisperer
SplunkTrust
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"
0 Karma
Get Updates on the Splunk Community!

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

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...