Splunk Search

Can't evaluate expression in query over multiple files

New Member

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?

Tags (2)
0 Karma
1 Solution

Revered Legend

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)

View solution in original post

Revered Legend

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)

View solution in original post

New Member

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

0 Karma

Ultra Champion

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

State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!