Scheduled report not showing zero lines

[alicepessani]

Hello,

I have scheduled a report that not show output lines if their value is zero.

I try to explain you, I have scheduled a report like this:

| inputlookup mylookup.csv
| join _time [search index="my_index" sourcetype="my_sourcetype" my query | fields ** | timechart span=30min count as "my_value1" ]
| my calculation
| table my_value1, my_value2, my_value3,.....
| eval index_id = "my_id" | eval env="my_env" | eval ... | fields *
| collect index=test

The report is scheduled every half an hour and takes the data of "last 30 minutes" and put them into an index "index=test". The problem is that when the my_value1 has zero value ("0") the report doesn't put any line in output index.

I have noticed also that if I schedule or launch manually the same report by taking more then "last 30 mintues", for example last 2 hours the report write in the output index "index=test" also the "0" line/s (with my_value1=0), ONLY if the other lines (in my example other 3 lines) have a my_value1 different from "0".

Example (last 30 min) output:
"No results found. Try expanding the time range."

Example (last 2 hour) output:
line 1. _time , 0
line 2. _time, 22
line 3. _time 3
line 4. _time 4

Could you kindly suggest me how to have a line in the output index when my_value1 has zero value?

I anticipate you that I can't perform a left join (join type=left) because my lookup contains also other _time values (greater than the actual) that I don't want to show in my index as results (I just want to take the data in the look up table that has the same actual _time).

Thanks for a feedback.
Alice

[DalJeanis · Accepted solution]

Try something like this after your calculation and before the table command...

| appendpipe 
    [| stats count 
     | where count==0  
     | addinfo | eval _time = info_max_time
     | eval ... add your desired fields here
     ]

The appendpipe will add one record of whatever format you like to create, and will do so only when there are no other records returned by the earlier part of the search. You could use info_max_time or info_min_time or any other time you care to set, for the _time of your dummy record.

[alicepessani]

Hello,

thanks for your feedback! Your method is working fine, but in order to have in output also other values from mylookup.csv I have added a lookup command in my final solution (see below).

My final solution is the following:

| inputlookup mylookup.csv
| join _time [search index="my_index" sourcetype="my_sourcetype" my query | fields | timechart span=30min count as "my_value1" ]
| my calculation
| appendpipe [| stats count | where count==0 | addinfo | eval _time = round (info_min_time,0)| eval"my_value1" = count | lookup "mylookup.csv " _time OUTPUT _time .... fields ...
| my calculation]
| table _time, ...fields...
| eval index_id = "my_id" | eval env="my_env" | eval ... | fields
| collect index=test

[DalJeanis]

I suspect, from reviewing your code, that you are doing something periodically, and that you have made it more complicated than it needs to be.

For instance, this..

| inputlookup mylookup.csv  
| join _time [search index="my_index" sourcetype="my_sourcetype" my query | fields ** | timechart span=30min count as "my_value1" ]

...is equivalent to this...

index="my_index" sourcetype="my_sourcetype" my query 
| fields ** 
| timechart span=30min count as "my_value1" 
| lookup mylookup.csv _time OUTPUT _time as foundtime  ... fields...
| where isnotnull(foundtime) 

...but the second method is not subject to limitations from subsearch and join. If there are any other fields being brought back, you would test them for null rather than doing _time as foundtime