Splunk Search

Dbquery and time_stamp

Path Finder

Hi,

I have a query like
| dbquery TESTDB "select a.timestamp, a.numbusyengines, a.numtotalengines, a.numtaskspending, b.brokername
from broker
stats a, brokers b
where a.brokerid = 2131184378 and a.brokerid = b.brokerid
and time
stamp > '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.
Thank You.

Tags (3)
0 Karma
1 Solution

SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Path Finder

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

Thank You for you time.

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Path Finder

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)

0 Karma

SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

Path Finder

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.

0 Karma

SplunkTrust
SplunkTrust

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.

0 Karma

Path Finder

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?

0 Karma

SplunkTrust
SplunkTrust

What span are your timestamps?

How did you fail when using timechart?

0 Karma