Can't evaluate expression in query over multiple files

[rjahrling]

Here's my query in Search:

host=kpidata source="*avail*" | eval Time = _time | eval days_in_month = round((relative_time(_time,"@mon+1mon")-relative_time(_time,"@mon"))/86400) | convert rmunit("Achieved Average") as unsched_num | convert rmunit("Operational Average") as total_num | eval unsched_downtime = days_in_month * (100 - unsched_num) | eval total_downtime = days_in_month * (100 - total_num) | timechart max(total_downtime) max(unsched_downtime)

...and it works great. I am pulling Operational Average and Achieved Average out of the files as text ("98.7%"), converting them to numbers (98.7), and using them in calculations without difficulty. The calculation for days_in_month is returning correct values. The results for the query appear correct, and the graphs correspond to existing graphs.

But.

I want to be able to calculate a value called sched_downtime:

eval sched_downtime = total_downtime - unsched_downtime

When I add that to the query (before the timechart, of course), no values are returned. I'm not sure I understand what's going on. I can say sched_downtime = total_downtime and data appears; I can say sched_downtime = total_downtime + 3 and data appears. I can do the same, manipulating unsched_downtime alone. But if I try to use the two downtime stats together...nothing.

I tried skipping the intermediate *_downtime calculations entirely, but that didn't make a difference either. It looks like if I try to use both the unscheduled and total numbers in a single eval, there will be no results--but no errors, either, at least not that I can see.

"Operational Average" and "Achieved Average" are fields in separate files. Does that matter? What have I done wrong? How can I get the value for sched_downtime?

[somesoni2 · Accepted solution]

Since "Operational Average" and "Achieved Average" are fields in separate files, they both don't appear in the same event/row when you're calculating your total_downtime and "unsched_downtime". If in your current query, your remove the timechart part and added below, your will get data like this.

Query to replace timechart

| table _time, total_downtime, unsched_downtime

Results

_time   total_downtime      unsched_downtime 
............................................
timestamp1   total_downtime1  NULL/Blank
timestamp1    NULL/Blank      unsched_downtime1
....
...

Because of one of them will NULL for every event, the "|eval sched_downtime = total_downtime - unsched_downtime" returns nothing.

One workaround here, assuming you want to do timechart daily (your can change it per your need), try following.

host=kpidata source="*avail*" | eval Time = _time | eval days_in_month = round((relative_time(_time,"@mon+1mon")-relative_time(_time,"@mon"))/86400) | convert rmunit("Achieved Average") as unsched_num | convert rmunit("Operational Average") as total_num | eval unsched_downtime = days_in_month * (100 - unsched_num) | eval total_downtime = days_in_month * (100 - total_num) | bucket span=1d _time | stats max(total_downtime) as total_downtime, max(unsched_downtime) as unsched_downtime by _time |eval sched_downtime = total_downtime - unsched_downtime | timechart max(total_downtime) max(unsched_downtime) max(sched_downtime)

[rjahrling]

Thanks! Adding the stats clause worked. I appreciate your help.

[kristian_kolb]

oops, one should not watch TV while editing answers. 🙂