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
Champion

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)

View solution in original post

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
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!