# Logstash sum values from jdbc input **URL:** \ **Category:** Logstash\ **Created:** [March 28, 2019, 4:51pm UTC](https://discuss.elastic.co/t/logstash-sum-values-from-jdbc-input/174393 "2019-03-28T16:51:06Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Thiago\_Anate](https://avatars.discourse-cdn.com/v4/letter/t/e9a140/32.png) [@Thiago\_Anate](https://discuss.elastic.co/u/Thiago_Anate)\ **Post date:** [March 28, 2019, 4:51pm UTC](https://discuss.elastic.co/t/logstash-sum-values-from-jdbc-input/174393/1 "2019-03-28T16:51:06Z")
Hello, I'm new on logstash and i'm trying to sum the value from two colums from my database and generate a new metric. I've exhausted all my alternatives. this is my conf file. the new varible that I'm creating is the 'tod\_ped' I created another variable to try to understand what is happenig with the value of 'TOT\_PROD', the variable is 'valor'. the two colums that i`m trying to sum is 'TOT\_PROD' and 'TOT\_SERV'. ``` input { jdbc { jdbc_driver_library => "jtds-1.3.1.jar" jdbc_driver_class => "Java::net.sourceforge.jtds.jdbc.Driver" jdbc_connection_string => "jdbc:jtds:sqlserver://xxxxxx:1433/dbPHXPSS" jdbc_user => "readonly" jdbc_password => "xxxxx" statement => "SELECT [NVENDA] ,[CPROJETO] ,[TECNOLOGIA] ,[PREVISAO] ,[APROVACAO] ,[STATUS] ,[CLIENTE] ,[TITULO] ,[TOT_PROD] ,[TOT_SERV] ,[QTD_H_FE] ,[QTD_H_SE] ,[QTD_H_PM] ,[QTD_H_DES] ,[TOT_DESPESA] ,[VENDEDOR] ,[TIPO_SOLICITACAO] ,[TECNOLOGIAPROJ] FROM [dbPHXPSS].[dbo].[VW_PROVISAOPROJETOS] where QTD_H_PM IS NOT NULL" } } filter { ruby { code =>" hash = event.to_hash hash.each do |k,v| if v == nil event.set(k,'0') end if k == 'TOT_PROD' event.set(teste, v) end end # testing the content from de varible 'TOT_PROD' event.set('valor', event.get('teste')) " } mutate { convert => ["TOT_PROD","float_eu"] } ruby { code =>" # adding the values to 'tot_ped' event.set('tot_ped', (event.get('TOT_PROD').to_f + event.get('TOT_SERV').to_f )) " } } output { elasticsearch { hosts => "localhost" index => "phoenix" document_type => "phxdb" } stdout {} } ``` this is the return code from logstash. What i've noticed is that the varible 'tot\_ped' is not adding the values, and the test varible is returning the value of 'TOT\_PROD' as nil. ``` { "@version" => "1", "cliente" => "OAB SP ", "vendedor" => "Sxxxxxxxx", "status" => "MEDIA", "tipo_solicitacao" => "0", "aprovacao" => "0", "tot_serv" => 0.0, "cprojeto" => "0", "qtd_h_se" => 132.0, "qtd_h_pm" => 24.0, "valor" => nil, "tot_ped" => 0.0, "qtd_h_des" => "0", "@timestamp" => 2019-03-15T13:42:15.243Z, "tot_prod" => 134133.7195, "tot_despesa" => "0", "titulo" => "Projeto Wifi", "previsao" => "0", "tecnologia" => "VSF", "tecnologiaproj" => "0", "qtd_h_fe" => 56.0, "nvenda" => 20361.0 } ``` Thank you advanced. ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [March 28, 2019, 6:51pm UTC](https://discuss.elastic.co/t/logstash-sum-values-from-jdbc-input/174393/2 "2019-03-28T18:51:51Z")
> [@Thiago\_Anate](#): > > if k == 'TOT\_PROD' event.set(teste, v) end In your sample data the field is called tot\_prod, not TOT\_PROD. And you need single quotes around 'teste' there. To be honest I am unclear why you are using a ruby filter. It looks to me like you could do the same with ``` mutate { add_field => { "teste" => "%{tot_prod}" } } ``` ---
**Author:** ![Thiago\_Anate](https://avatars.discourse-cdn.com/v4/letter/t/e9a140/32.png) [@Thiago\_Anate](https://discuss.elastic.co/u/Thiago_Anate)\ **Post date:** [March 29, 2019, 2:36pm UTC](https://discuss.elastic.co/t/logstash-sum-values-from-jdbc-input/174393/3 "2019-03-29T14:36:19Z")
Hi Badger! thanks for the fast answer! It didn't work with the mutate, because a needed to sum the value from two variables. tot\_prod + tot\_serv. But it worked with the ruby! You called my attention for name of variable, i changed for lower case and now my ruby ​​code it working like a charm !!! This is the code ``` ruby { code =>" event.set('tot_ped', ((event.get('tot_prod').to_f * 2.25) + event.get('tot_serv').to_f )) " } ``` this is the return from logstash ``` { "vendedor" => "André Chagas Nitta", "tipo_solicitacao" => "Projeto", "@timestamp" => 2019-03-29T13:34:49.862Z, "teste" => 18109.21, "qtd_h_se" => 14.0, "tot_despesa" => 300.0, "tecnologiaproj" => "Colaboração & Comunicação", **"tot_prod" => 18109.21,** "qtd_h_des" => 0.0, "cliente" => "SPORT CLUB CORINTHIANS PAULIST", **"tot_ped" => 56729.24189999999,** "tecnologia" => "VSF", "status" => "GANHA", "cprojeto" => "PROJ-00215", "qtd_h_fe" => 34.0, "qtd_h_pm" => 8.0, "nvenda" => 393.0, "@version" => "1", "previsao" => "05/08/2013", "aprovacao" => "05/08/2013", "titulo" => "Lan to Lan", **"tot_serv" => 15983.5194,** "valor" => 18109.21 } ``` You really helped me, this was driving me crazy for almost 2 weeks. Thank you very much!!! ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [March 29, 2019, 2:50pm UTC](https://discuss.elastic.co/t/logstash-sum-values-from-jdbc-input/174393/4 "2019-03-29T14:50:42Z")
Yeah, I get that the second ruby filter requires ruby. Not so clear for the first. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [April 26, 2019, 2:50pm UTC](https://discuss.elastic.co/t/logstash-sum-values-from-jdbc-input/174393/5 "2019-04-26T14:50:44Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.