topic Re: Splunk DB Connect 1: How to use "timechart avg()" in a dbquery search? in Splunk Search

Splunk DB Connect 1: How to use "timechart avg()" in a dbquery search?

mark_chuman — Tue, 26 Jan 2016 22:04:46 GMT

This search works fine:

"DBOMA" "SELECT "Time" , "Virtual_Machine" , "ready" FROM DBSTDBO.CPUBYVM where "Virtual_Machine" BETWEEN 'vmname001' AND 'vmname025'" | eval Time=strftime(Time,"%m/%d/%y %H:%M:%S") | search Time>"01/26/16 09:25:00" | search Time<"01/26/16 09:55:00"

But this search below fails with no results found. I've used timechart avg successfully before, so not sure if it's snagging on something because it's a dbquery.

"DBOMA" "SELECT "Time" , "Virtual_Machine" , "ready" FROM DBSTDBO.CPUBYVM where "Virtual_Machine" BETWEEN 'vmname001' AND 'vmname025'" | eval Time=strftime(Time,"%m/%d/%y %H:%M:%S") | search Time>"01/26/16 09:25:00" | search Time<"01/26/16 09:55:00" | timechart avg("ready") by Virtual_Machine

Re: Splunk DB Connect 1: How to use "timechart avg()" in a dbquery search?

gyslainlatsa — Tue, 26 Jan 2016 22:15:23 GMT

hi mark.chuman,

if ready is one field, try replace | timechart avg("ready") by Virtual_Machine by | timechart avg(ready) by Virtual_Machine

Re: Splunk DB Connect 1: How to use "timechart avg()" in a dbquery search?

somesoni2 — Tue, 26 Jan 2016 22:32:23 GMT

The reason timechart is failing as it expects a fields with name "_time" in epoch format timestmap and which is not present in your output of dbquery. Try something like this (also change the comparison of date in epoch format)

"DBOMA" "SELECT "Time" , "Virtual_Machine" , "ready" FROM DBSTDBO.CPUBYVM where "Virtual_Machine" BETWEEN 'vmname001' AND 'vmname025'"  | search Time>strptime("01/26/16 09:25:00","%m/%d/%y %H:%M:%S") AND Time<strptime("01/26/16 09:55:00","%m/%d/%y %H:%M:%S") | rename Time as _time | timechart avg("ready") by Virtual_Machine

Re: Splunk DB Connect 1: How to use "timechart avg()" in a dbquery search?

mark_chuman — Wed, 27 Jan 2016 13:09:30 GMT

thanks. tried this before and it comes up with same result as original query

Re: Splunk DB Connect 1: How to use "timechart avg()" in a dbquery search?

mark_chuman — Wed, 27 Jan 2016 13:12:56 GMT

So, this query completes successfully

| dbquery "TQOMA" "SELECT "Time" , "Virtual_Machine" , "ready" FROM TQSTDBO.CPUBYVM where "Virtual_Machine" BETWEEN 'vW7NTXMCSOMA001' AND 'vW7NTXMCSOMA025'"

This query completes with no results found

| dbquery "TQOMA" "SELECT "Time" , "Virtual_Machine" , "ready" FROM TQSTDBO.CPUBYVM where "Virtual_Machine" BETWEEN 'vW7NTXMCSOMA001' AND 'vW7NTXMCSOMA025'" | search Time>strptime("01/26/16 09:25:00","%m/%d/%y %H:%M:%S") AND Time<strptime("01/26/16 09:55:00","%m/%d/%y %H:%M:%S")