topic Re: Time Chart and DBXquery in Splunk Search

Time Chart and DBXquery

shubhamkanugo — Wed, 12 Feb 2020 06:00:27 GMT

I am new to splunk. I have a DB connection from where I am fetching a table. I want to create a dashboard for with x-axis as time and Y-axis as count of table in every hour.
i tried with timechart function but I am unable to achive my goal. I am getting data without timechart.

| dbxquery query="SELECT * FROM \"CASE\"" | timechart count by Id

this is my query.

Re: Time Chart and DBXquery

to4kawa — Wed, 12 Feb 2020 09:50:23 GMT

Can you provide the result, | dbxquery query="SELECT * FROM \"CASE\""?

Re: Time Chart and DBXquery

shubhamkanugo — Wed, 12 Feb 2020 10:22:57 GMT

update:
Instead of * it is count(1) in query so it is giving number of rows as result.

| dbxquery query="SELECT count(1) FROM \"CASE\"" | timechart count by Id

@to4kawa

Re: Time Chart and DBXquery

to4kawa — Wed, 12 Feb 2020 10:26:11 GMT

@shubhamkanugo

timechart needs epoch time value.
so, I say again,
Can you provide the result, | dbxquery query="SELECT * FROM \"CASE\"" ?

Re: Time Chart and DBXquery

shubhamkanugo — Thu, 13 Feb 2020 05:55:48 GMT

@to4kawa
I will not be able to provide results but you can consider result of any general table like person or organization having ID and timestamps also as columns.

Re: Time Chart and DBXquery

to4kawa — Thu, 13 Feb 2020 08:46:09 GMT

I see and don't want such sensitive data.
but you can't provide sample?

Re: Time Chart and DBXquery

shubhamkanugo — Mon, 17 Feb 2020 10:36:19 GMT

XYZ 521463 PQR LMN 2012-09-14 18:13:12.919648

Above is the sample row we get from that query, last column is time stamp with date.
@to4kawa

Re: Time Chart and DBXquery

to4kawa — Mon, 17 Feb 2020 13:52:43 GMT

| makeresults
| eval _raw="XYZ 521463 PQR LMN 2012-09-14 18:13:12.919648"
| rex "(?<time>\d{4}-\d{2}-\d{2} \S+$)"
| eval _time =strptime(time,"%F %T.%6Q")
| timechart count

I am not sure id , you should extract from the result.

Re: Time Chart and DBXquery

shubhamkanugo — Mon, 24 Feb 2020 11:11:16 GMT

@to4kawa
Hi If you are OK it will be more comfortable for me to understand your answer with some explanation.

Re: Time Chart and DBXquery

to4kawa — Mon, 24 Feb 2020 11:25:47 GMT

If you are not sure rex , check regex101
if you are not sure other SPL, try Fundametal 1