DB Connect how to export _time into MySQL table

[deodion]

How to insert 3 below fields into MySQL table?

sourcetype="mydata" 
| table  _time field1 field2

MySQL table:
id_table, INT(11)
field1, INT(11)
field2, INT(11)
time2, TIMESTAMP or DATETIME, it does not work?

Am I missing something? Thanks!

[Richfez]

_time in Splunk is a unix epoch time. That means it's a big long integer. For instance, right about now it's 1561640458. For humans to convert back and forth with, you can use something like epochconverter.com.

The MySQL TIMESTAMP column type looks like a "time", like "2019-06-27 07:59:43".

In order to stick a Splunk _time into a MySQL TIMESTAMP field, you have to convert it to the format it wants. You correctly did something like it in your second example. Just use a version of _time that has been converted.

sourcetype="mydata" 
| eval time = strftime(_time, "%Y-%m-%d %H:%M:%S") 
| table time field1 field2

When put into a table like this:

time, TIMESTAMP
field1, INT(11)
field2, INT(11)

It should be fine. (NOTE I'm not sure if it should be TIMESTAMP or DATETIME, that's an excercise left to the reader.)

Also note that I'm ignoring the "let's not normalize this properly" ID fields, add those in if you feel you must <-- and ignore my curmudgeonly cane-shaking at those darn kids on my lawn... LOL I crack myself up sometimes. I only wish someone else got my jokes. 🙂

So, you had it right - this is how it works and how it must be, given that Splunk and MySQL have vastly different ideas about what "time" looks like.

Happy Splunking,
Rich

[enzotaufan8]

the timestamp and datetime nor text does not work for me

[Richfez]

You might find that you'll get faster, better responses if you include at least some additional information. I mean, imagine how frustrated you would be if I replied "Well it works for me" and didn't give you any additional information or help!

But it does work for me; I just built a test case 10 minutes ago that works splendidly.

So let's dig a bit farther...

Please check the DB connect "Connection Health" page. Isolate your output that you are having problems with. Click around a bit in there - at some point you can get to the actual events that are showing the error. Include that here so we can help.

Also, perhaps it would be useful if you include the code you are using:
- The actual table you are sending to, if it differs from your example,
- The search you are using to send it there,
- The output of the search - or at least a row or two from it
- Any errors or warnings from the above dig-around in the DB Health pages.

For instance, I tried this:
In Mysql in a DB I already had set up as a connection in DBX, which I had write permission to:

CREATE TABLE answers_test (time_1 INT, time_2 DATETIME, field_1 VARCHAR(100));

I then created a DB Output in which I cobbled together a run-anywhere search to populate with one event of information.

| makeresults 
| eval field_1 = "Something Random"
| eval time_1 = _time
| eval time_2 = strftime(_time, "%Y-%m-%d %H:%M:%S")
| fields - _time

This outputs (ignoring the poor formatting...)

field_1     time_1      time_2  
Something Random    1561733073  2019-06-28 09:51:17 

The rest of the steps in creating the output were
Picked answers_test from the list.
Mapped fields.
Scheduled it once every 100 seconds.

After that I jumped back to MySQL and ran:

mysql> select * from answers_test;
+------------+---------------------+------------------+
| time_1     | time_2              | field_1          |
+------------+---------------------+------------------+
| 1561733495 | 2019-06-28 14:51:35 | Something Random |
+------------+---------------------+------------------+
1 row in set (0.00 sec)

So you can see that not only was _time unformatted pushed into the INT field of time_1, time_2 also went in fine formatted like it is and as a DATETIME.

Please let us know if this helps!

[deodion]

If i use:

sourcetype="mydata" 
| eval time1 = strftime(_time, "%Y-%m-%d %H:%M:%S") 
| table  time1 field1 field2

MySQL table:
id_table, INT(11)
field1, INT(11)
field2, INT(11)
time2, TEXT

Then its working,
So again am I missing something to get it work?

[enzotaufan8]

same problem here