# How to add a tag if device lookup using JDBC fails? **URL:** \ **Category:** Logstash\ **Created:** [July 20, 2018, 12:13pm UTC](https://discuss.elastic.co/t/how-to-add-a-tag-if-device-lookup-using-jdbc-fails/140887 "2018-07-20T12:13:03Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![victor.nilsson](https://avatars.discourse-cdn.com/v4/letter/v/eb8c5e/32.png) [@victor.nilsson](https://discuss.elastic.co/u/victor.nilsson)\ **Post date:** [July 20, 2018, 12:13pm UTC](https://discuss.elastic.co/t/how-to-add-a-tag-if-device-lookup-using-jdbc-fails/140887/1 "2018-07-20T12:13:04Z")
Hi We have a MySQL database with all of our devices stored as a table with an ID, FQDN, IP and a service name. Now we want to have some assurance that if the device that is sending logs to our ELK stack isn't in our database it will send it's logs to a seperate index called "unknown". Does logstash or the JDBC plugin have any method of adding a tag or something like that if the device lookup fails or if the values returned are zero/null? Here is our configuration: ``` filter { if "syslog" in [tags] { jdbc_static { loaders => [ { id => "elkDevIndexAssoc" query => "select * from elkDevIndexAssoc" local_table => "elkDevIndexAssoc" } ] local_db_objects => [ { name => "elkDevIndexAssoc" index_columns => ["cenDevIP"] columns => [ ["cenDevSID", "varchar(255)"], ["cenDevFQDN", "varchar(255)"], ["cenDevIP", "varchar(255)"], ["cenDevServiceName", "varchar(255)"] ] } ] local_lookups => [ { id => "localObjects" query => "select * from elkDevIndexAssoc WHERE cenDevIP = :host" parameters => {host => "[host]"} target => "cendotEnhanced" } ] # using add_field here to add & rename values to the event root add_field => { cendotFQDN => "%{[cendotEnhanced[0][cendevfqdn]}" } add_field => { cendotSID => "%{[cendotEnhanced[0][cendevsid]}" } add_field => { cendotServiceName => "%{[cendotEnhanced[0][cendevservicename]}" } remove_field => ["cendotEnhanced"] jdbc_user => "unsername" jdbc_password => "password" jdbc_driver_class => "com.mysql.jdbc.Driver" jdbc_driver_library => "/usr/share/java/mysql-connector-java-8.0.11.jar" jdbc_connection_string => "jdbc:mysql://84.19.155.71:3306/logstash?serverTimezone=Europe/Stockholm" #jdbc_default_timezone => "Europe/Stockholm" } } } ``` ---
**Author:** ![mikebd](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mikebd/32/33437_2.png) [@mikebd](https://discuss.elastic.co/u/mikebd)\ **Post date:** [July 20, 2018, 1:18pm UTC](https://discuss.elastic.co/t/how-to-add-a-tag-if-device-lookup-using-jdbc-fails/140887/2 "2018-07-20T13:18:59Z")
Perhaps something like this: ```auto filter { ... if ! [centdotFQDN] { mutate { add_tag => ["TAG"] } } } ``` ---
**Author:** ![guyboertje](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/guyboertje/32/31592_2.png) [@guyboertje](https://discuss.elastic.co/u/guyboertje)\ **Post date:** [July 23, 2018, 10:55am UTC](https://discuss.elastic.co/t/how-to-add-a-tag-if-device-lookup-using-jdbc-fails/140887/3 "2018-07-23T10:55:16Z")
Docs -\> [tag\_on\_failure](http://tag_on_failurehttps://www.elastic.co/guide/en/logstash/current/plugins-filters-jdbc_static.html#plugins-filters-jdbc_static-tag_on_failure) ---
**Author:** ![victor.nilsson](https://avatars.discourse-cdn.com/v4/letter/v/eb8c5e/32.png) [@victor.nilsson](https://discuss.elastic.co/u/victor.nilsson)\ **Post date:** [July 26, 2018, 9:25am UTC](https://discuss.elastic.co/t/how-to-add-a-tag-if-device-lookup-using-jdbc-fails/140887/4 "2018-07-26T09:25:03Z")
Hi, i tried adding that in the following way: ``` add_field => { cendotFQDN => "%{[cendotEnhanced[0][cendevfqdn]}" } add_field => { cendotSID => "%{[cendotEnhanced[0][cendevsid]}" } add_field => { cendotServiceName => "%{[cendotEnhanced[0][cendevservicename]}" } remove_field => ["cendotEnhanced"] jdbc_user => "username" jdbc_password => "password" jdbc_driver_class => "com.mysql.jdbc.Driver" jdbc_driver_library => "/usr/share/java/mysql-connector-java-8.0.11.jar" jdbc_connection_string => "jdbc:mysql://123.123.123.123:3306/logstash?serverTimezone=Europe/Stockholm" #jdbc_default_timezone => "Europe/Stockholm" loader_schedule => "*/5 * * * *" #add_tag => ["sql_successful"] #this works #tag_on_failure => ["sql_failure"] #this does nothing ``` However, nothing gets tagged with "sql\_failure" even though they don't have any entries in our mysql database. I added the following output to monitor if anything gets tagged with "sql\_failure" but it shows nothing.. ``` else if "sql_failure" in [tags] { stdout { codec => rubydebug } } ``` EDIT: I think that the tag never gets applied because it technically never "fails", it just doesn't add anything to the fields? ---
**Author:** ![victor.nilsson](https://avatars.discourse-cdn.com/v4/letter/v/eb8c5e/32.png) [@victor.nilsson](https://discuss.elastic.co/u/victor.nilsson)\ **Post date:** [July 27, 2018, 5:55am UTC](https://discuss.elastic.co/t/how-to-add-a-tag-if-device-lookup-using-jdbc-fails/140887/5 "2018-07-27T05:55:27Z")
So i sent the dead\_letter\_queue to stdout and this is what i got. It still gets the tag "syslog" but not "sql\_failure" that should be added with the jdbc plugin. Can anyone help remedy this? Would it be possible to have a conditional to check for these kinds of messages and send them to a seperate index? ``` Jul 27 07:50:40 sealijvblog01 logstash[39452]: } Jul 27 07:50:40 sealijvblog01 logstash[39452]: }, Jul 27 07:50:40 sealijvblog01 logstash[39452]: "host" => "123.123.123.123", Jul 27 07:50:40 sealijvblog01 logstash[39452]: "@version" => "1", Jul 27 07:50:40 sealijvblog01 logstash[39452]: "message" => "<167>2018-07-05T11:03:11.122Z ivcl00esxi03 Vpxa: verbose vpxa[2D264B70] [Originator@6876 sub=hostdvm opID=WFU-7bfc9bfc] [VpxaHalVmHostagent] 2: GuestInfo changed 'guest.disk'\n", Jul 27 07:50:40 sealijvblog01 logstash[39452]: "cendotServiceName" => [ Jul 27 07:50:40 sealijvblog01 logstash[39452]: [0] "%{[cendotEnhanced[0][cendevservicename]}", Jul 27 07:50:40 sealijvblog01 logstash[39452]: [1] "%{[cendotEnhanced[0][cendevservicename]}" Jul 27 07:50:40 sealijvblog01 logstash[39452]: ], Jul 27 07:50:40 sealijvblog01 logstash[39452]: "tags" => [ Jul 27 07:50:40 sealijvblog01 logstash[39452]: [0] "syslog" Jul 27 07:50:40 sealijvblog01 logstash[39452]: ] Jul 27 07:50:40 sealijvblog01 logstash[39452]: } Jul 27 07:50:40 sealijvblog01 logstash[39452]: { Jul 27 07:50:40 sealijvblog01 logstash[39452]: "cendotSID" => [ Jul 27 07:50:40 sealijvblog01 logstash[39452]: [0] "%{[cendotEnhanced[0][cendevsid]}", Jul 27 07:50:40 sealijvblog01 logstash[39452]: [1] "%{[cendotEnhanced[0][cendevsid]}" Jul 27 07:50:40 sealijvblog01 logstash[39452]: ], Jul 27 07:50:40 sealijvblog01 logstash[39452]: "@timestamp" => 2018-07-05T11:03:11.155Z, Jul 27 07:50:40 sealijvblog01 logstash[39452]: "cendotFQDN" => [ Jul 27 07:50:40 sealijvblog01 logstash[39452]: [0] "%{[cendotEnhanced[0][cendevfqdn]}", Jul 27 07:50:40 sealijvblog01 logstash[39452]: [1] "%{[cendotEnhanced[0][cendevfqdn]}" Jul 27 07:50:40 sealijvblog01 logstash[39452]: ], Jul 27 07:50:40 sealijvblog01 logstash[39452]: "@metadata" => { Jul 27 07:50:40 sealijvblog01 logstash[39452]: "dead_letter_queue" => { Jul 27 07:50:40 sealijvblog01 logstash[39452]: "entry_time" => 2018-07-05T11:03:27.940Z, Jul 27 07:50:40 sealijvblog01 logstash[39452]: "plugin_id" => "8dd52b09c1d7fb90affc84e3485088635706795b78d975ead53ea53db0b1c7e0", Jul 27 07:50:40 sealijvblog01 logstash[39452]: "plugin_type" => "elasticsearch", Jul 27 07:50:40 sealijvblog01 logstash[39452]: "reason" => "Could not index event to Elasticsearch. status: 400, action: [\"index\", {:_id=>nil, :_index=>\"logstash-%{[cendotEnhanced[0][cendevservicename]}-2018-07-05\", :_type=>\"doc\", :_routing=>nil}, #], response: {\"index\"=>{\"_index\"=>\"logstash-%{[cendotEnhanced[0][cendevservicename]}-2018-07-05\", \"_type\"=>\"doc\", \"_id\"=>nil, \"status\"=>400, \"error\"=>{\"type\"=>\"invalid_index_name_exception\", \"reason\"=>\"Invalid index name [logstash-%{[cendotEnhanced[0][cendevservicename]}-2018-07-05], must be lowercase\", \"index_uuid\"=>\"_na_\", \"index\"=>\"logstash-%{[cendotEnhanced[0][cendevservicename]}-2018-07-05\"}}}" ``` ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [August 24, 2018, 5:55am UTC](https://discuss.elastic.co/t/how-to-add-a-tag-if-device-lookup-using-jdbc-fails/140887/6 "2018-08-24T05:55:28Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.