How do you modify your raw data in Splunk?

[m7787580]

Hello Splunkers,

My original data looks like this for a particular day in a below example.

Currently, there are 10 entries in a day sometimes it's 4 entries and it's totally random. I would like to reshape my data as per hour and whenever there are no entries in an hour it should fill it as zero. TIME AND VALUE are the main key fields.

ORIGINAL DATA

             _time                 TIME          Value
1    2018-07-26 23:43:01.079    26-JUL-2018 00  2
2    2018-07-26 23:43:01.079    26-JUL-2018 04  2
3    2018-07-26 23:43:01.079    26-JUL-2018 06  2
4    2018-07-26 23:43:01.079    26-JUL-2018 09  2
5    2018-07-26 23:43:01.078    26-JUL-2018 12  2
6    2018-07-26 23:43:01.078    26-JUL-2018 15  3
7    2018-07-26 23:43:01.078    26-JUL-2018 16  4
8    2018-07-26 17:43:04.176    26-JUL-2018 18  2
9    2018-07-26 15:43:01.062    26-JUL-2018 21  1
10   2018-07-26 09:43:01.160    26-JUL-2018 23  1

I would like to change into per hour of raw data and fill with Zero for the time period where we don't have logs in Splunk.

REQUIRED DATA

     TIME                 Value
1   7/26/2018 0:00  2
2   7/26/2018 1:00  0
3   7/26/2018 2:00  0
4   7/26/2018 3:00  0
5   7/26/2018 4:00  2
6   7/26/2018 5:00  0
7   7/26/2018 6:00  2
8   7/26/2018 7:00     0
9   7/26/2018 8:00     0
10   7/26/2018 9:00    2
11   7/26/2018 10:00    0
12   7/26/2018 11:00    0
13   7/26/2018 12:00    2
14   7/26/2018 13:00    0
15   7/26/2018 14:00    0
16   7/26/2018 15:00    3
17   7/26/2018 16:00    4
18   7/26/2018 17:00    0
19   7/26/2018 18:00    2
20   7/26/2018 19:00    0
21   7/26/2018 20:00    0
22   7/26/2018 21:00    1
23   7/26/2018 22:00    0
24   7/26/2018 23:00    1

Thanks in advance for your help 🙂

[thambisetty]

Hi,

Try something like below,

 index=index host=splunk-test  sourcetype=sample 
 | makecontinuous _time span=1h
 | timechart span=1h sum(value)
 | fillnull value=0 

[FrankVl]

Won't the timechart already make it continuous? No need for the explicit | makecontinuous I think?

[thambisetty]

what if there is no data for 1 full hour.I am not sure this time will be considered. if empty hours also consider then no need of | makecontinuous

[m7787580]

Yes I agree we don't need to mention explicitly

[HiroshiSatoh · Accepted solution]

_row data can not be changed. I think that you can edit it with a search sentence.

(your search)
| eval _time=strptime(TIME,"%d-%b-%Y %H")|bin _time span=1h 
| stats sum(Value) as Value by _time
| makecontinuous _time span=1h
| fillnull value=0 Value

※This query will create data up to the latest TIME.

[thambisetty]

what if there are two values for the same hour. how do you aggregate them? do you want sum the values which are there in the same hour or count ?

[m7787580]

I would like to add them.
Thanks

[thambisetty]

index=index host=splunk-test  sourcetype=sample 
| makecontinuous _time span=1h
| timechart span=1h sum(value)
| fillnull value=0