Change String to Hours - date filters

[katara]

Hello everyone,
I have a column like this:
business_time_left

3 Hours 24 Minutes
59 Minutes
4 Days 23 Hours 58 Minutes
0 Seconds
1 Hour

and so on..

What I want to do in Logstash is to convert this entirely into hours.
So mu value should entirety convert to something like

 business_time_left
    3.24
    0.59
    119.58
    0 
    1

Is this possible?

My config file:

http_poller {
    urls => {
    snowinc => {
    url => "https://service-now.com"
    user => "your_user"
    password => "yourpassword"
    headers => {Accept => "application/json"}
    }
}
request_timeout => 60
metadata_target => "http_poller_metadata"
schedule => { cron => "* * * * * UTC"}
codec => "json"
}
}
filter
   {
   json {source => "result" }
   split{ field => ["result"] }
}
output {
  elasticsearch {
    hosts => ["yourelastuicIP"]
    index => "inc"
    action=>update
    document_id => "%{[result][number]}"
    doc_as_upsert =>true
}
        stdout { codec => rubydebug }
}

Sample Json input data, when the url is hit.

{"result":[
{
"made_sla":"true",
"Type":"incident resolution p3",
"sys_updated_on":"2019-12-23 05:00:00"
"business_time_left":" 59 Minutes"} ,
{
"made_sla":"true",
"Type":"incident resolution l1.5 p4",
"sys_updated_on":"2019-12-24 07:00:00"
"business_time_left":"3 Hours 24 Minutes"}]}

Thanks in advance

Katara.

[pastechecker (Charlie)]

You would have to write some ruby code to do that.
Maybe work more on the source of your data? Because it looks like a bad architecture.

[Fabio-sama (Fabio)]

I agree with Charlie since a ruby code would look something like this

ruby {
    code => "
      time = event.get('business_time_left').downcase()
      hours = time[/\d+(?=\s*hour)/].to_i
      minutes_in_hours = time[/\d+(?=\s*minute)/].to_i / 60.0
      days_in_hours = time[/\d+(?=\s*day)/].to_i * 24
      time_in_hours = hours + minutes_in_hours + days_in_hours
      event.set('business_time_left_hours', time_in_hours.round(2))
    "
  }

[katara]

@Fabio-sama,
I understand,
there might be another way to get this data, by finding the difference between the start time and the end time columns in the json input.
However,I am not sure how to use a date difference in Logstash manipulation. Is this also something I can try?

Please help me with which is best suited.

[Fabio-sama (Fabio)]

As always, try pasting here the input that Logstash receives so we can think of a solution (I don't see any start time or end time in what you posted).

Also, I've replied to some of you posts and I'd like to point out couple of things that might be good for you to know.
I understand you come from a relational DB and you're quite used to using its technical words but please, when posting here try to adopt the right language.

The sentence the start time and the end time columns in the json input, for example, makes no sense since the json has no columns but a key-value structure.
Elasticsearch is organized in indices, each of which has documents in it. Each document has fields with a given value associated to it.

Speaking of columns when referring to jsons or elasticsearch doesn't really make much sense.
You can have columns in Kibana only once the data have been aggregated and visualized in a Table visualization. Out of that context the concept of column hardly makes any sense.

What's more, it can mislead you. In fact, you might think you can operate in Logstash on a column based logic while it rarely is possible (each row of that column of yours is often a new input in logstash and rarely can you use tools like aggregate filter to keep the state of what you are processing).

So please, be as clear as possible when posting here, for those who wanna help you but for yourself too, since this might help you better understand what you want to achieve.

Thanks

[katara]

@Fabio-sama,
My sincere apologies for not being very clear about it!

the input json is actually pretty long. So i cut down most of it do generalize and concentrate on my issue at first.
Adding the required rows:
My input would be

{"result":[
{
"made_sla":"true",
"Type":"incident resolution p3",
"start_time":"2019-12-23 05:00:00"
"end_time":"2019-12-24 07:00:00"
"business_time_left":" 59 Minutes"}]}

Im using the below ruby to get a simple difference of the business duration itself.

ruby {
code => "duration = (((event.get('end_time') - event.get('start_time'))/60)/60) rescue nil; 
event.set('Business_duration', duration); "
}

as a simple task. But this calculation could be a little complicated in different cases of mine.

I tried your ruby code,
which gives me this error:

Ruby exception occurred: undefined method `downcase' for nil:NilClass

Am i missing something here?

I'm trying to see if the source can be modified instead as well, to avoid it

Thank you!
Katara.

[Fabio-sama (Fabio)]

No problem. Just wanted to point out that the clearer the question, the easier it is to answer.

Anyway, in which case might it be complicated? IMO it would be much more straight to make a date difference than trying to regex that string everytime.

About my ruby code, you're not missing anything. It can give you that error in case the field business_time_left is missing in one of you input.

I can add a check on its presence but you gotta make sure that what you're ingesting does have that field:

ruby {
  code => "
    unless  (time = event.get('business_time_left')).nil?
      time = time.downcase()
      hours = time[/\d+(?=\s*hour)/].to_i
      minutes_in_hours = time[/\d+(?=\s*minute)/].to_i / 60.0
      days_in_hours = time[/\d+(?=\s*day)/].to_i * 24
      time_in_hours = hours + minutes_in_hours + days_in_hours
      event.set('business_time_left_hours', time_in_hours.round(2))
    end
  "
}

[katara]

@Fabio-sama,
Okay, Im pretty sure the field exists.
I added the latest you've provided,
Right now, Im not getting the business_time_left_hours field created in ES.

exception=>java.lang.ClassCastException: org.jruby.RubyArray cannot be cast to org.jruby.RubyIO}

Is the only thing i see alarming in my logs.

Katara

[Fabio-sama (Fabio)]

Ok this confirms what I thought. That ruby filter has to be applied on a root element. I wrote it as if the input document contains the business_time_left_hours as root field, not knowing which kind of filter you might have already applied to the event.

You should make the effort to adapt that ruby filter to your input event and apply it in the right position.

To make things a bit clearer, it is obvious that if the input event is something like:

{
  "result": [
    {
      "business_time_left": "xxx"
    },
    {
      "business_time_left": "yyy"
    }
  ]
} 

and you try to apply that filter, it will fail since it won't find anything in event.get('business_time_left') cause the only root field of your event is result.

Dunno if this helps you in what you're trying to do or makes things even more complicated for you.

[katara]

Hello @Fabio-sama,
It does seem to be not applicable to my data then, its better to move with the source change or manually calculate with the dates.
This isnt making it complicated, but instead im now having more insights about Logstash
Thank you for that and your time with many of my queries

Katara.