Splunk Search

dbxquery eval _time as column

Path Finder

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!

0 Karma

Champion

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" |
0 Karma

Path Finder

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.

0 Karma

Champion

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.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!