Splunk Search

MySQL Connector Null Inserts

bsykes
Engager

Greetings -

I have a MySQL table that I'm trying to insert records into:

mysql> explain delivery_manager;
+---------+-------------+------+-----+------------+-------+
| Field   | Type        | Null | Key | Default    | Extra |
+---------+-------------+------+-----+------------+-------+
| date    | date        | NO   | MUL | 0000-00-00 |       |
| domain  | varchar(30) | NO   | MUL | 0          |       |
| sent    | int(11)     | NO   |     | 0          |       |
| bounced | int(11)     | NO   |     | 0          |       |
+---------+-------------+------+-----+------------+-------+
4 rows in set (0.00 sec)

I'm searching through our logs with the following search however while I get the proper results in my search output, it doesn't appear to be inputting those results properly into the database.

My search:

 sourcetype="dm_mainlog" logType="D" OR logType="P" recipientDomain=yahoo.com OR recipientDomain=gmail.com OR recipientDomain=aol.com OR recipientDomain=hotmail.com | eval recipientDomain=lower(recipientDomain) | fields logType, recipientDomain, _time | eval date=strftime(_time, "%Y-%m-%d") | rename recipientDomain as domain| stats count(eval(logType="D")) as sent, count(eval(logType="P")) as bounced by date,domain | mysqloutput host=dbhost username=un password=pw schema=db table=delivery_manager insert=date,domain,sent,bounced

mysql> select * from delivery_manager where date='2012-07-23';
+------------+--------+------+---------+
| date       | domain | sent | bounced |
+------------+--------+------+---------+
| 2012-07-23 | 0      |    0 |       0 |
| 2012-07-23 | 0      |    0 |       0 |
| 2012-07-23 | 0      |    0 |       0 |
| 2012-07-23 | 0      |    0 |       0 |
+------------+--------+------+---------+
4 rows in set (0.00 sec)

Is this something that the MySQL connector can input? Where might I be going wrong?

0 Karma
1 Solution

bsykes
Engager

So for those playing along at home, the issue was I was missing quotes around the insert command:

sourcetype="dm_mainlog" logType="D" OR logType="P" recipientDomain=yahoo.com OR recipientDomain=gmail.com OR recipientDomain=aol.com OR recipientDomain=hotmail.com | eval recipientDomain=lower(recipientDomain) | fields logType, recipientDomain, _time | eval date=strftime(_time, "%Y-%m-%d") | rename recipientDomain as domain| stats count(eval(logType="D")) as sent, count(eval(logType="P")) as bounced by date,domain | mysqloutput host=dbhost username=un password=pw schema=db table=delivery_manager insert="date,domain,sent,bounced"

View solution in original post

0 Karma

bsykes
Engager

So for those playing along at home, the issue was I was missing quotes around the insert command:

sourcetype="dm_mainlog" logType="D" OR logType="P" recipientDomain=yahoo.com OR recipientDomain=gmail.com OR recipientDomain=aol.com OR recipientDomain=hotmail.com | eval recipientDomain=lower(recipientDomain) | fields logType, recipientDomain, _time | eval date=strftime(_time, "%Y-%m-%d") | rename recipientDomain as domain| stats count(eval(logType="D")) as sent, count(eval(logType="P")) as bounced by date,domain | mysqloutput host=dbhost username=un password=pw schema=db table=delivery_manager insert="date,domain,sent,bounced"
0 Karma
Get Updates on the Splunk Community!

AppDynamics Summer Webinars

This summer, our mighty AppDynamics team is cooking up some delicious content on YouTube Live to satiate your ...

SOCin’ it to you at Splunk University

Splunk University is expanding its instructor-led learning portfolio with dedicated Security tracks at .conf25 ...

Credit Card Data Protection & PCI Compliance with Splunk Edge Processor

Organizations handling credit card transactions know that PCI DSS compliance is both critical and complex. The ...