Finding the age of tickets

[katara]

Hi All,
Ive been working on finding the age of a ticket with respect to its open and closed time.

@Fabio-sama, helped me with the below ruby,

input {
  http_poller {
    urls => { 
      snowinc => {
        url => "https://******"
        user => "*****"
        password => "******"
        headers => {Accept => "application/json"}
      }
    } 
    request_timeout => 60
    metadata_target => "http_poller_metadata"
    schedule => { cron => " * * * * * UTC"}
    codec => "json"
  }
}

filter {
  split {
    field => "result"
  }

  ruby {
    code => "
      require 'date'

      event.get('result').each { |k, v|
          event.set(k,v)
      }
      event.remove('result')

      closed_at = event.get('closed_at')
      end_date = (closed_at.nil? || closed_at.empty?) ? Date.today :  Date.parse(closed_at)
      start_date = event.get('sys_created_on')
      if start_date.nil? || start_date.empty?
        event.set('time_in_days', 'Creation date not specified')
      else 
        time_in_days = (end_date -  Date.parse(start_date)).to_i
        event.set('time_in_days', time_in_days)

        case time_in_days
        when 0
          event.set('age_group', '0 Days')
        when 1..4
          event.set('age_group', '1-4 Days')
        when 5..9
          event.set('age_group', '5-9 Days')
        when 10..Float::INFINITY
          event.set('age_group', 'Over 10 Days')
        else
          event.set('age_group', 'Uncomputable')
        end
      end
    "
  }

  date {
    match => ["sys_created_on","yyyy-MM-dd HH:mm:ss"]
    target => "sys_created_on"
  }

  date {
    match => ["sys_updated_on","yyyy-MM-dd HH:mm:ss"]
    target => "sys_updated_on"
  }

  date {
    match => ["closed_at","yyyy-MM-dd HH:mm:ss"]
    target => "closed_at"
  }
}

output {
  elasticsearch {
    hosts => ["10.116.15.127:9200"]
    index => "incidentsnow"
    action=>update
    document_id => "%{number}"
    doc_as_upsert =>true
  }
} 

For which the query works well, however, the loaded data only takes the tickets that are closed, i.e, t has a closed date.
I want to load data, irrespective of its closed state, and only where applicable, the age calculation should be done.

Im trying to create a graph similar to the below in Kibana.

I am using an API to get all the ticketing fields including the created date and closed date.
So I need to do the following to get to this:

1. check if the ticket is closed, if yes, ignore.
2. ticket is open, do the difference between now - created on in days
3. Create a new field with values as in the image (0-1,1-7,etc) and each ticket should have their corresponding age rage.

This is to find, if a ticket is open, how long is it open for?
Sample input data:

{"result":[
{
"made_sla":"true",
"Type":"incident resolution p3",
"created_on":"2019-12-23 05:00:00",
"closed_at":"2019-12-24 05:00:00"
"sys_updated_on_on":"2019-12-24 05:00:00"
"number":"INC0010275",
"category":"Network"} ,
{
"made_sla":"true",
"Type":"incident resolution l1.5 p4",
"created_on":"2019-12-24 07:00:00",
"closed_at":""
"sys_updated_on":"2019-12-27 08:00:00"
"number":"INC0010567",
"category":"DB"}]}

Kindly help me fix this issue.

Katara.

[Fabio-sama (Fabio)]

Hi Katara!

Sorry I couldn't answer you further but I've been so busy. I'll look deeper into it as soon as I can.