Awk a string followed by write to MySQL? 1

[paulobrads]

I have tcpdump piping to an awk command and I want to write it to a MySQL database.

I´m not sure the best way of going about this, through shell scripting or perl. A constant connection to the MySQL db will be required as tcpdump is constantly listening on the wire as the piping takes place.

The shell script is currently:

tcpdump -i eth1 -s 1500 | awk '{print $NF}'

Any ideas?

Cheers.

 

[feherke]

Hi

Why not simply :

tcpdump -i eth1 -s 1500 | awk '{print "insert into tablename (fieldname) values (" $NF ");"}' | mysql mysqlparameters

Note that you may need to flush the output after each write using [tt]fflush()[/tt]. ( [tt]gawk[/tt] extension ). Or use [tt]perl[/tt] instead. See the discussion in your previous thread, thread822-1483881 .

Feherke.

http://rootshell.be/~feherke/

 

[paulobrads]

I´m worried that I may need to write to the db quite regularly (up to a number of times a second) and creating a new connection to MySQL every time doesn´t feel very nice.

 

[feherke]

Hi

Well, my example uses a single connection for the lifetime of [tt]tcpdump[/tt].

Feherke.

http://rootshell.be/~feherke/

 

[paulobrads]

Now you mention it, that does look quite good, however are we missing some sort of escape character?

Here is my shell:

tcpdump -i eth1 -s 1500 port snmp | awk '{print "insert into oids (OID) values ( " $NF " );"}' | mysql -u myuser -pmypass snmp_OIDs

And I get the error:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.1 )' at line 1

'.1' is what I´m expecting the value of $NF to equal but why the syntax issues?

Cheers.

 

[feherke]

Hi

I do not know neither [tt]tcpdump[/tt] and the oids table's structure, so I can not suggest anything related to that .1.

Feherke.

http://rootshell.be/~feherke/

 

[LKBrwnDBA]

Maybe you need quotes?

tcpdump -i eth1 -s 1500 port snmp | awk '{print "insert into oids (OID) values (\"" $NF "\" );"}' | mysql -u myuser -pmypass snmp_OIDs

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[paulobrads]

Yup - that's exactly it, cheers.

Now any ideas how to cope when the $NF is a string containing double quotes " ?

The MySQL command messes up because it gets terminated early.

Cheers

 

[PHV]

how to cope when the $NF is a string containing double quotes

tcpdump -i eth1 -s 1500 port snmp | awk "{print \"insert into oids (OID) values ('\" \$NF \"');\"}" | mysql -u myuser -pmypass snmp_OIDs

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886