topic Re: Dbquery and time_stamp in Splunk Search

Dbquery and time_stamp

gudavasr — Mon, 28 Sep 2020 15:57:55 GMT

Hi,

I have a query like
| dbquery TEST_DB "select a.time_stamp, a.num_busy_engines, a.num_total_engines, a.num_tasks_pending, b.broker_name
from broker_stats a, brokers b
where a.broker_id = 2131184378 and a.broker_id = b.broker_id
and time_stamp > '2014-02-20 4:00:00 PM' and time_stamp < '2014-02-21 3:00:00 AM' order by time_stamp asc"
| convert timeformat="%F %H:%M:%S" ctime(time_stamp) AS stats_time | chart list(num_busy_engines) AS BusyEngines, list(num_total_engines) 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.
Thank You.

Re: Dbquery and time_stamp

martin_mueller — Mon, 24 Feb 2014 20:24:38 GMT

What span are your timestamps?

How did you fail when using timechart?

Re: Dbquery and time_stamp

gudavasr — Mon, 28 Sep 2020 15:58:06 GMT

In the query:
time_stamp > '2014-02-20 4:00:00 PM' and time_stamp < '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 per_hour(time_stamp) list(num_busy_engines) but not working. How can I use timechart command for this?

Re: Dbquery and time_stamp

martin_mueller — Mon, 24 Feb 2014 20:56:20 GMT

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.

Re: Dbquery and time_stamp

gudavasr — Thu, 27 Feb 2014 19:08:00 GMT

Hi,
I think I know what is going on here. X-axis is limited to plot first 500 values (or points). Do you know how this can be extended?
Thank You.

Re: Dbquery and time_stamp

martin_mueller — Thu, 27 Feb 2014 19:27:01 GMT

Use the timechart command to limit the bucket count to a sensible, well-chartable number.

Re: Dbquery and time_stamp

gudavasr — Mon, 28 Sep 2020 16:00:11 GMT

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 TEST_DB "your SQL here" | rename time_stamp as _time | timechart avg(*engines)

Re: Dbquery and time_stamp

martin_mueller — Thu, 27 Feb 2014 21:28:12 GMT

Is your time_stamp field an epoch timestamp or a human-readable string?

Re: Dbquery and time_stamp

gudavasr — Mon, 28 Sep 2020 16:00:25 GMT

now its working;
| dbquery TEST_DB "your SQL here" | convert timeformat="%F %H:%M:%S" ctime(time_stamp) AS _time --> did not work
| dbquery TEST_DB "your SQL here" | rename time_stamp as _time --> Worked

Thank You for you time.

Re: Dbquery and time_stamp

martin_mueller — Thu, 27 Feb 2014 21:39:13 GMT

Great. I've converted a comment to an answer so you can mark the question as solved.