Splunk Search

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

mark_chuman
Path Finder

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
0 Karma

somesoni2
Revered Legend

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
0 Karma

mark_chuman
Path Finder

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

gyslainlatsa
Motivator

hi mark.chuman,

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

0 Karma

mark_chuman
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

The Splunk Success Framework: Your Guide to Successful Splunk Implementations

Splunk Lantern is a customer success center that provides advice from Splunk experts on valuable data ...

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...