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!
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" |
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.
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.