Splunk Search

Dbquery and time_stamp

gudavasr
Path Finder

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.

Tags (3)
0 Karma
1 Solution

martin_mueller
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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

0 Karma

gudavasr
Path Finder

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.

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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

0 Karma

gudavasr
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

martin_mueller
SplunkTrust
SplunkTrust

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

0 Karma

gudavasr
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

martin_mueller
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

gudavasr
Path Finder

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?

0 Karma

martin_mueller
SplunkTrust
SplunkTrust

What span are your timestamps?

How did you fail when using timechart?

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...