topic Re: dbxquery eval _time as column in Splunk Search

dbxquery eval _time as column

zhatsispgx — Wed, 14 Feb 2018 23:32:37 GMT

Hi all,

I am trying to set the values in column insertepoch in a mysql database to be the new _time index in splunk to be able to timechart. The following search is returning with 0 results. In fact, when I take the timechart off, the query runs, however, I don't see it working and I'm not able to find anything in the way of a solution to this.

| dbxquery shortnames=1 connection="myconn" query="SELECT * FROM `mydb`.`mytable` ORDER BY Id DESC Limit 200" |
eval insertepoch=strftime(insertepoch, "%Y-%m-%d %H:%M:%S.%N") |
eval _time=insertepoch | timechart span=1h count(_raw)

Please help! Thanks!

Re: dbxquery eval _time as column

micahkemp — Thu, 15 Feb 2018 02:00:59 GMT

Disclaimer: I can’t test this right now

Try explicitly defining your needed fieldnames in your query, including insertedtime (which seems to be all that is needed for your example).

 | dbxquery shortnames=1 connection="myconn" query="SELECT insertepoch FROM `mydb`.`mytable` ORDER BY Id DESC Limit 200"
 | eval _time=insertepoch
 | timechart span=1h count

If that doesn’t work, please add the output of:

 | dbxquery shortnames=1 connection="myconn" query="SELECT insertepoch FROM `mydb`.`mytable` ORDER BY Id DESC Limit 200" |

Re: dbxquery eval _time as column

zhatsispgx — Thu, 15 Feb 2018 18:18:05 GMT

Tested explicitly defining the fieldnames in the query and it didn't work. The output of | dbxquery shortnames=1 connection="myconn" query="SELECT insertepoch FROM mydb.mytable ORDER BY Id DESC Limit 200" | is an epoch timeformat which gets converted with eval insertepoch=strftime(insertepoch, "%Y-%m-%d %H:%M:%S.%N") |

sample output before eval:

1518714601
1518711331
1518710573

sample output after eval:

2018-02-15 11:15:02.000000000
2018-02-15 10:10:01.000000000
2018-02-15 09:15:31.000000000

I am guessing this has something to do with the data not being indexed.

Re: dbxquery eval _time as column

micahkemp — Thu, 15 Feb 2018 18:34:55 GMT

I just edited my answer.

You don't need to do strftime if your value is already in epoch format. I'm sorry I missed this earlier, I was responding from my phone and didn't realize it was strftime instead of strptime to convert to epoch.