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

[mark_chuman]

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

[somesoni2]

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

[mark_chuman]

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") 

[gyslainlatsa]

hi mark.chuman,

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

[mark_chuman]

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