I have a query like
| dbquery TESTDB "select a.timestamp, a.numbusyengines, a.numtotalengines, a.numtaskspending, b.brokername
from brokerstats a, brokers b
where a.brokerid = 2131184378 and a.brokerid = b.brokerid
and timestamp > '2014-02-20 4:00:00 PM' and timestamp < '2014-02-21 3:00:00 AM' order by timestamp asc"
| convert timeformat="%F %H:%M:%S" ctime(timestamp) AS statstime | chart list(numbusyengines) AS BusyEngines, list(numtotalengines) AS TotalEngines over stats_time
I am charting this as line graph, but the problem is the maximum visualization of the graph is seen only for 3-4hrs i.e. from 4:00pm to 9:00pm; What should I change to view the graph until 3:00am? I tried timechart but not successful. Please help.
now its working;
| dbquery TESTDB "your SQL here" | convert timeformat="%F %H:%M:%S" ctime(timestamp) AS time --> did not work
| dbquery TESTDB "your SQL here" | rename time_stamp as _time --> Worked
Thank You for you time.
timechart command is not returning any output. nly chart command works. I tried this and many more with timechart but no luck; it return only time values nothing else.
| dbquery TESTDB "your SQL here" | rename time_stamp as _time | timechart avg(*engines)
To rephrase the first question, how many timestamps do you get from 4pm to 3am? Splunk JSCharts will only display 500ish data points, you're likely going over that.
Your timechart in b) looks weird, and it needs a _time field to work with. Try something like this:
| dbquery TEST_DB "your SQL here" | rename time_stamp as _time | timechart avg(*engines)
Depending on your timestamp you may need to keep your convert call from the original query.
In the query:
timestamp > '2014-02-20 4:00:00 PM' and timestamp < '2014-02-21 3:00:00 AM'
a) the chart shows only the results from 4:00pm to 9:00pm instead of until 3:00am. is there a way to see the line graph until 3:00am
b) I tried timechart perhour(timestamp) list(numbusyengines) but not working. How can I use timechart command for this?