topic Need every time interval as a row even though the count of records is 0 in that interval in Splunk Search

Need every time interval as a row even though the count of records is 0 in that interval

poddraj — Wed, 30 Sep 2020 05:22:17 GMT

Hi,
I am using below query to get the stats o/p of Total, Failure & Failure percent by couple of fields for every 15 min interval over 2 hrs duration.

index=dte_fios sourcetype=dte2_Fios FT=*FT earliest=04/20/2020:11:00:00 latest=04/20/2020:13:00:00
| bin _time span=15m
| stats count as Total, count(eval(Error_Code!="0000")) AS Failure by FT,Error_Code,_time
| eval Failurepercent=round(Failure/Total*100)

I am getting O/p as expected in terms of cols like below:

FT Error_Code _time Total Failure Failurepercent
ALCATEL_FT 8950 2020-04-20 12:15:00 10 10 100%
ALCATEL_FT 8950 2020-04-20 12:30:00 10 5 50%
ALCATEL_FT 8950 2020-04-20 12:45:00 10 10 100%

The issue here is if any interval is having 0 records (we do not have row for 11, 11:15 11:30 intervals) is is not showing a row. I need the O/P to give row for every 15min interval and show Total & Failure as 0. I tried to use timechart but I could not get the above o/p format as stats is not working with timehcart.

Can someone help with the query?

Re: Need every time interval as a row even though the count of records is 0 in that interval

richgalloway — Wed, 06 May 2020 12:50:03 GMT

Please share the query that uses timechart.

Re: Need every time interval as a row even though the count of records is 0 in that interval

ololdach — Wed, 06 May 2020 13:26:13 GMT

Hi poddraj,

the next step is to create one line per possible error code per device per time:
| eval Error_Code="8950,2166,7633" | makemv delim="," Error_Code |mvexpand Error_Code

and finally initialize the counter with 0 and rename the time:
| eval Failure=0, _time=starttime

If you concatenate all in sequence, you will get a table like this:
'2020-05-06 12:00:00' 'DeviceA' 'Error1' 0
'2020-05-06 12:00:00' 'DeviceA' 'Error2' 0
'2020-05-06 12:00:00' 'DeviceA' 'Error3' 0
'2020-05-06 12:00:00' 'DeviceB' 'Error1' 0
...
'2020-05-06 12:15:00' 'DeviceC' 'Error3' 0

Now, you can append your query:
| append [search index=dte_fios sourcetype=dte2_Fios FT=*FT earliest=04/20/2020:11:00:00 latest=04/20/2020:13:00:00 | bin _time span=15m | eval Failure=if(Error_Code!="0000",1,0)]

The resulting table has your query results mixed into the "empty frame of no failures". Finally you can run your stats and since you have a result for every possible time value in your query range, you will get a perfectly filled result table

| stats count as Total, sum(Failure) AS Failure by FT,Error_Code,_time | eval Failurepercent=round(Failure/Total*100)

Hope it helps
Oliver

Re: Need every time interval as a row even though the count of records is 0 in that interval

poddraj — Wed, 30 Sep 2020 05:22:36 GMT

Based on your inputs I have used below query but it is not creating rows for every 15 min interval.

Instead of lookup if I give FT="ALCATEL_FT,HNM_FT,GWR_FT" then I am getting rows for these FT's for every 15 min interval with 0 Total,Failure counts

I am doing anything wrong in the join?

Re: Need every time interval as a row even though the count of records is 0 in that interval

ololdach — Thu, 07 May 2020 11:41:23 GMT

Hi poddraj, when I don't get the expected result, I copy the whole query to a scratchpad and execute it step by step, adding one | at a time until I see the step that delivers unexpected results. Just executing the gentimes, rename and fields gives you exactly the expected time intervals. The lookup seems wrong, because what you ask Splunk to do is: Take the lookup by the name ftthresholdlkp and match on the field FT. You don't have a field FT to match on, the only field you have at that point is _time. Create your lookup table like this:
"devicetype","FT"
"FT","DeviceA"
"FT","DeviceB"
...
"FT","DeviceX"
Do your lookup like this: | eval devicetype="FT" | lookup ftthresholdlkp devicetype OUTPUT FT

HiH
Oliver