After doing span, how to find the time in the middle of search range with 0 data for multiple id?

[Julia1231]

Hi everyone,

In my search, I set bucket span=2h _time. It returns only hours which have data

There are some hours where no data returns so, it is not shown in the result. I want to find it and I use makecontinous

Raw data:

_time	id	count
10/10/2022 16:00	1	12
10/10/2022 18:00	1	14
11/10/2022 08:00	1	15
11/10/2022 10:00	1	54
10/10/2022 16:00	2	78
10/10/2022 18:00	2	45
10/10/2022 20:00	2	5
11/10/2022 00:00	2	6

Expectation:

_time	id	count
10/10/2022 16:00	1	12
10/10/2022 18:00	1	14
10/10/2022 20:00
10/10/2022 22:00
11/10/2022 00:00
10/10/2022 20:00
10/10/2022 22:00
11/10/2022 00:00
11/10/2022 08:00	1	15
11/10/2022 10:00	1	54
10/10/2022 16:00	2	78
10/10/2022 18:00	2	45
10/10/2022 20:00	2	5
10/10/2022 22:00
11/10/2022 00:00	2	6

After that I want to fill the id = null by the previous id and count = null by 0

I can do it for a single id but the makecontinuous doesn't work like that for multiple id (in the example I take 2 but in reality I have more)

Do you have any idea please?

[ITWhisperer]

| chart sum(count) as count by _time id
| eventstats min(_time) as start
| eval bin=floor((_time-start)/(60*60*2))
| makecontinuous bin
| filldown start
| eval _time=start+(bin*60*60*2)
| fillnull value=0
| fields - bin start
| untable _time id count

[Julia1231]

@ITWhisperer Here my script:

|dbxquery connection="database" query="
SELECT id, time, count(*) as count
FROM table
GROUP BY id, time "
|lookup lookup.csv numero OUTPUT id
|eval list_id= "123,456,789"
|eval split_list_id= split(list_id,",")
|mvexpand split_list_id
|where NUM=split_list_id
|eval _time=strptime(time,"%Y-%m-%dT%H:%M:%S.%N")

|chart sum(count) as count by _time NUM
| untable _time NUM count
|eventstats min(_time) as start
|eval bin=floor((_time-start)/(60*60*2))
| makecontinuous bin

in this step, Splunk tells me that Unexpected duplicate values in field 'bin' have been detected.

I still find the gap between _time, means |makecontinous doesn't work.

Do you know why?

[ITWhisperer]

Possibly because you put my suggested command in a different order? You also seem to have dropped the 2 hour bucketing your original post seemed to have - or does this already come from your dbxquery? Try something like this

|dbxquery connection="database" query="
SELECT id, time, count(*) as count
FROM table
GROUP BY id, time "
|lookup lookup.csv numero OUTPUT id
|eval list_id= "123,456,789"
|eval split_list_id= split(list_id,",")
|mvexpand split_list_id
|where NUM=split_list_id
|eval _time=strptime(time,"%Y-%m-%dT%H:%M:%S.%N")

| bin _time span=2h
| chart sum(count) as count by _time NUM
| eventstats min(_time) as start
| eval bin=floor((_time-start)/(60*60*2))
| makecontinuous bin
| filldown start
| eval _time=start+(bin*60*60*2)
| fillnull value=0
| fields - bin start
| untable _time NUM count

[Julia1231]

@ITWhisperer it works.

Thanks very much!

Btw, if I want to filter the result by time, do you have any suggestion to do it by Splunk?

[yuanliu]

I'm guessing you are looking for filldown and fillnull.

| filldown id
| fillnull count value=0