Logstash sum values from jdbc input

Elastic Stack Logstash
1

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.

2

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}" } }
3

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!!!

4

Yeah, I get that the second ruby filter requires ruby. Not so clear for the first.