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

[poddraj]

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?

[ololdach]

Hi poddraj,

first, you create an "empty table" with all possible time slots:
| gentimes [|makeresults|addinfo|eval start=strftime(info_min_time,"%m/%d/%Y:%H:%M:%S")| return start] [|makeresults|addinfo|eval end=strftime(info_max_time,"%m/%d/%Y:%H:%M:%S")| return end] increment=15min

the next will create some demo devices. In your query you could use a | join [|inputlookup] to create one line per time per device
| eval FT="deviceA,deviceB,deviceC" | makemv delim="," FT | mvexpand FT

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

[poddraj]

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

| gentimes start=04/20/2020:11:00:00 end=04/20/2020:13:00:00 increment=15m | rename starttime as _time
| fields _time
| lookup ftthresholdlkp FT
| eval FT=FT | makemv delim="," FT | mvexpand FT | eval Failure=0
| 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)]
| stats count(WPID) as Total, sum(Failure) AS Failure by FT,_time | eval Failurepercent=if(Failure=0,0,round(Failure/Total*100))

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?

[ololdach]

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

[richgalloway]

Please share the query that uses timechart.