Sort tickets by age group

katara · January 23, 2020, 6:52am

Hello all,
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 follwing to get to this:

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

So far,
Ive come up with the ruby code for finding the difference,

ruby {
	code => "event.set('AgeDays', (event.get('created_on') - event.get(event.get('@timestamp')))/3600/24)"
}

Now how do I nest this with my If else conditions to get the expected output?

Current conf file.

> input {
> 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"] }
date {
  match => ["[result][created_on]","yyyy-MM-dd HH:mm:ss"]
  target => "created_on"
     }
date {
  match => ["[result][closed_at]","yyyy-MM-dd HH:mm:ss"]
  target => "closed_at"
     }
date {
  match => ["[result][sys_updated_on]","yyyy-MM-dd HH:mm:ss"]
  target => "sys_updated_on"
     }
}
output {
  elasticsearch {
    hosts => ["yourelastuicIP"]
    index => "incidentsnow"
    action=>update
    document_id => "%{[result][number]}"
    doc_as_upsert =>true
}
        stdout { codec => rubydebug }
}

The output for the json url looks like the below:

> {"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",
"sys_updated_on":"2019-12-24 07:00:00",
"closed_at":""
"sys_updated_on":"2019-12-27 08:00:00"
"number":"INC0010567",
"category":"DB"}]}

Please help me with the same.
Note : @timestamp is the date field created by logstash of the time of data insertion.
Thanks!

Katara.

katara · January 27, 2020, 5:47am

Hi All,
Update:
I came up with something like this:

ruby {
  code => "
    unless  (time = event.get('closed_at')).nil?
      time_in_days = event.set('AgeDays', (event.get('created_on') - event.get(event.get(Time.now())))/3600/24)
      event.set('time_in_days', time_in_days.round(2))
    end
  "
}
if time_in_days = 0 {
    mutate {
        add_field => { "Age_days" => "0 Days" }
    }
}
if time_in_days > 1 AND time_in_days <= 4 {
    mutate {
        add_field => { "Age_days" => "1 - 4 Days" }
    }
}
if time_in_days > 5 AND time_in_days <= 9 {
    mutate {
        add_field => { "Age_days" => "5 - 9 Days" }
    }
}
if time_in_days > 10 {
    mutate {
        add_field => { "Age_days" => "Over 10 Days" }
    }
}

This still isnt working, Im no expert in this.
I would appreciate if you could help me correct my mistakes

Katara

katara · January 28, 2020, 6:14am

Hello Team,
Im getting the below error when i execute the above filter.

`

[2020-01-28T01:03:13,718][ERROR][logstash.agent ] Failed to execute action {:action=>LogStash::PipelineAction::Create/pipeline_id:inctest, :exception=>"LogStash::ConfigurationError", :message=>"Expected one of #, ( at line 36, column 17 (byte 1476) after filter\n {\n json\n {\n source => "result"\n }\n split\n {\n field => ["result"]\n }\nruby {\n code => "\n unless (time = event.get('business_time_left')).nil?\n time_in_days = event.set('AgeDays', (event.get('created_on') - event.get(event.get(Time.now())))/3600/24)\n event.set('Aging_days', time_in_days.round(2))\n end\n "\n}\n\nif time_in_days ", :backtrace=>["/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:41:in compile_imperative'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:49:in compile_graph'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:11:in block in compile_sources'", "org/jruby/RubyArray.java:2577:in map'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:10:in compile_sources'", "org/logstash/execution/AbstractPipelineExt.java:151:in initialize'", "org/logstash/execution/JavaBasePipelineExt.java:47:in initialize'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:24:in initialize'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline_action/create.rb:36:in execute'", "/usr/share/logstash/logstash-core/lib/logstash/agent.rb:325:in block in converge_state'"]}

`

Here's my complete config:

filter
   {
   json
       {
       source => "result"
       }
   split
        {
        field => ["result"]
        }
ruby {
  code => "
    unless  (time = event.get('closed_at')).nil?
      time_in_days = event.set('AgeDays', (event.get('sys_created_on') - event.get(event.get(Time.now())))/3600/24)
      event.set('Aging_days', time_in_days.round(2))
    end
  "
}

if time_in_days = 0 {
    mutate {
        add_field => { "Age_days" => "0 Days" }
    }
}
if time_in_days > 1 AND time_in_days <= 4 {
    mutate {
        add_field => { "Age_days" => "1 - 4 Days" }
    }
}
if time_in_days > 5 AND time_in_days <= 9 {
    mutate {
        add_field => { "Age_days" => "5 - 9 Days" }
    }
}
if time_in_days > 10 {
    mutate {
        add_field => { "Age_days" => "Over 10 Days" }
    }
}
}

where am i going wrong?

Badger · January 28, 2020, 3:38pm

That is complaining about

 if time_in_days = 0

The equality operator in logstash is ==, not =.

katara · January 30, 2020, 10:35am

@badger,
Apologies, I couldnt get back to you earlier,

ruby {
  code => "
    unless  (time = event.get('closed_at')).nil?
      time_in_days = event.set('AgeDays', (event.get('created_on') - event.get(event.get(Time.now())))/3600/24)
      event.set('time_in_days', time_in_days.round(2))
    end
  "
}
if time_in_days == 0 {
    mutate {
        add_field => { "Age_days" => "0 Days" }
    }
}
if time_in_days > 1 AND time_in_days <= 4 {
    mutate {
        add_field => { "Age_days" => "1 - 4 Days" }
    }
}
if time_in_days > 5 AND time_in_days <= 9 {
    mutate {
        add_field => { "Age_days" => "5 - 9 Days" }
    }
}
if time_in_days > 10 {
    mutate {
        add_field => { "Age_days" => "Over 10 Days" }
    }
}

Error Log:

Failed to execute action {:action=>LogStash::PipelineAction::Create/pipeline_id:snowinc, :exception=>"LogStash::ConfigurationError", :message=>"Expected one of #, ( at line 36, column 17 (byte 2394) after filter\n {\n json \n { \n source => "result"\n }\n split\n {\n field => ["result"]\n }\nruby {\n code => "\n unless (time = event.get('closed_at')).nil?\n time_in_days = event.set('AgeDays', (event.get('created_on') - event.get(event.get(Time.now())))/3600/24)\n event.set('time_in_days', time_in_days.round(2))\n end\n "\n}\nif time_in_days ", :backtrace=>["/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:41:in compile_imperative'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:49:in compile_graph'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:11:in block in compile_sources'", "org/jruby/RubyArray.java:2577:in map'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:10:in compile_sources'", "org/logstash/execution/AbstractPipelineExt.java:151:in initialize'", "org/logstash/execution/JavaBasePipelineExt.java:47:in initialize'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:24:in initialize'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline_action/create.rb:36:in execute'", "/usr/share/logstash/logstash-core/lib/logstash/agent.rb:325:in block in converge_state'"]}

`

I have similar ifs in the script, which holds >, <=, etc.
should that be changed as well?
Please let me know.

Thanks!
Katara

Badger · January 30, 2020, 5:43pm

To refer to the value of a field you need to enclose it in square brackets, and the logical operator has to be lowercase, so it should be

if [time_in_days] > 1 and [time_in_days] <= 4 {

etc.

katara · January 31, 2020, 4:24am

Hi @Badger,
Did this change,

ruby {
  code => "
    unless  (time = event.get('closed_at')).nil?
      time_in_days = event.set('AgeDays', (event.get('sys_created_on') - event.get(event.get(Time.now())))/3600/24)
      event.set('time_in_days', time_in_days.round(2))
    end
  "
}
if [time_in_days] == 0 {
    mutate {
        add_field => { "Age_days" => "0 Days" }
    }
}
if [time_in_days] > 1 AND [time_in_days] <= 4 {
    mutate {
        add_field => { "Age_days" => "1 - 4 Days" }
    }
}
if [time_in_days] > 5 AND [time_in_days] <= 9 {
    mutate {
        add_field => { "Age_days" => "5 - 9 Days" }
    }
}
if [time_in_days] > 10 {
    mutate {
        add_field => { "Age_days" => "Over 10 Days" }
    }
}

I still get
`

[2020-01-30T23:16:46,713][ERROR][logstash.agent ] Failed to execute action {:action=>LogStash::PipelineAction::Create/pipeline_id:snowinc, :exception=>"LogStash::ConfigurationError", :message=>"Expected one of #, and, or, xor, nand, { at line 41, column 23 (byte 2498) after filter\n {\n json \n { \n source => "result"\n }\n split\n {\n field => ["result"]\n }\nruby {\n code => "\n unless (time = event.get('closed_at')).nil?\n time_in_days = event.set('AgeDays', (event.get('sys_created_on') - event.get(event.get(Time.now())))/3600/24)\n event.set('time_in_days', time_in_days.round(2))\n end\n "\n}\nif [time_in_days] == 0 {\n mutate {\n add_field => { "Age_days" => "0 Days" }\n }\n}\nif [time_in_days] > 1 ", :backtrace=>["/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:41:in compile_imperative'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:49:in compile_graph'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:11:in block in compile_sources'", "org/jruby/RubyArray.java:2577:in map'", "/usr/share/logstash/logstash-core/lib/logstash/compiler.rb:10:in compile_sources'", "org/logstash/execution/AbstractPipelineExt.java:151:in initialize'", "org/logstash/execution/JavaBasePipelineExt.java:47:in initialize'", "/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:24:in initialize'", "/usr/share/logstash/logstash-core/lib/logstash/pipeline_action/create.rb:36:in execute'", "/usr/share/logstash/logstash-core/lib/logstash/agent.rb:325:in block in converge_state'"]}`

Is there a problem with my ruby code by any chance?
All the columns in the ruby code exists as well.

Is this how I get the number of days,
Im meaning to do a number of days from the created date till the current date.

time_in_days = event.set('AgeDays', (event.get('sys_created_on') - event.get(event.get(Time.now())))/3600/24)

Thanks.
Katara

Badger · January 31, 2020, 2:48pm

As I said in my previous post, the logical operator "and" has to be lowercase.

katara · February 3, 2020, 4:41am

@Badger,
Sorry i missed that.
I changed that, and now a Null Pointer exception arrives.

[2020-02-02T23:21:07,591][ERROR][org.logstash.execution.WorkerLoop] Exception in pipelineworker, the pipeline stopped processing new events, please check your filter configuration and restart Logstash.
java.lang.NullPointerException: null
at org.logstash.config.ir.compiler.EventCondition$Compiler$UnexpectedTypeException.(EventCondition.java:616) ~[logstash-core.jar:?]
at org.logstash.config.ir.compiler.EventCondition$Compiler.compare(EventCondition.java:418) ~[logstash-core.jar:?]
at org.logstash.config.ir.compiler.EventCondition$Compiler.lambda$compareFieldToConstant$11(EventCondition.java:409) ~[logstash-core.jar:?]
at org.logstash.config.ir.compiler.EventCondition$Compiler.lambda$booleanCondition$4(EventCondition.java:139) ~[logstash-core.jar:?]
at org.logstash.config.ir.compiler.Utils.filterEvents(Utils.java:27) ~[logstash-core.jar:?]
at org.logstash.generated.CompiledDataset11.compute(Unknown Source) ~[?:?]
at org.logstash.generated.CompiledDataset12.compute(Unknown Source) ~[?:?]
at org.logstash.generated.CompiledDataset13.compute(Unknown Source) ~[?:?]
at org.logstash.generated.CompiledDataset17.compute(Unknown Source) ~[?:?]
at org.logstash.generated.CompiledDataset18.compute(Unknown Source) ~[?:?]
at org.logstash.generated.CompiledDataset22.compute(Unknown Source) ~[?:?]
at org.logstash.generated.CompiledDataset26.compute(Unknown Source) ~[?:?]
at org.logstash.generated.CompiledDataset29.compute(Unknown Source) ~[?:?]
at org.logstash.generated.CompiledDataset30.compute(Unknown Source) ~[?:?]
at org.logstash.generated.CompiledDataset31.compute(Unknown Source) ~[?:?]
at org.logstash.generated.CompiledDataset34.compute(Unknown Source) ~[?:?]
at org.logstash.generated.CompiledDataset36.compute(Unknown Source) ~[?:?]
at org.logstash.generated.CompiledDataset37.compute(Unknown Source) ~[?:?]
at org.logstash.execution.WorkerLoop.run(WorkerLoop.java:64) [logstash-core.jar:?]
at sun.reflect.GeneratedMethodAccessor61.invoke(Unknown Source) ~[?:?]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_231]
at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_231]
at org.jruby.javasupport.JavaMethod.invokeDirectWithExceptionHandling(JavaMethod.java:440) [jruby-complete-9.2.7.0.jar:?]
at org.jruby.javasupport.JavaMethod.invokeDirect(JavaMethod.java:304) [jruby-complete-9.2.7.0.jar:?]
at org.jruby.java.invokers.InstanceMethodInvoker.call(InstanceMethodInvoker.java:36) [jruby-complete-9.2.7.0.jar:?]
at usr.share.logstash.logstash_minus_core.lib.logstash.java_pipeline.RUBY$block$start_workers$1(/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:239) [jruby-complete-9.2.7.0.jar:?]
at org.jruby.runtime.CompiledIRBlockBody.callDirect(CompiledIRBlockBody.java:136) [jruby-complete-9.2.7.0.jar:?]
at org.jruby.runtime.IRBlockBody.call(IRBlockBody.java:77) [jruby-complete-9.2.7.0.jar:?]
at org.jruby.runtime.Block.call(Block.java:124) [jruby-complete-9.2.7.0.jar:?]
at org.jruby.RubyProc.call(RubyProc.java:295) [jruby-complete-9.2.7.0.jar:?]
at org.jruby.RubyProc.call(RubyProc.java:274) [jruby-complete-9.2.7.0.jar:?]
at org.jruby.RubyProc.call(RubyProc.java:270) [jruby-complete-9.2.7.0.jar:?]
at org.jruby.internal.runtime.RubyRunnable.run(RubyRunnable.java:105) [jruby-complete-9.2.7.0.jar:?]
at java.lang.Thread.run(Thread.java:748) [?:1.8.0_231]
[2020-02-02T23:21:07,713][FATAL][logstash.runner ] An unexpected error occurred! {:error=>java.lang.IllegalStateException: java.lang.NullPointerException, :backtrace=>["org.logstash.execution.WorkerLoop.run(org/logstash/execution/WorkerLoop.java:85)", "java.lang.reflect.Method.invoke(java/lang/reflect/Method.java:498)", "org.jruby.javasupport.JavaMethod.invokeDirectWithExceptionHandling(org/jruby/javasupport/JavaMethod.java:440)", "org.jruby.javasupport.JavaMethod.invokeDirect(org/jruby/javasupport/JavaMethod.java:304)", "usr.share.logstash.logstash_minus_core.lib.logstash.java_pipeline.start_workers(/usr/share/logstash/logstash-core/lib/logstash/java_pipeline.rb:239)", "org.jruby.RubyProc.call(org/jruby/RubyProc.java:295)", "org.jruby.RubyProc.call(org/jruby/RubyProc.java:274)", "org.jruby.RubyProc.call(org/jruby/RubyProc.java:270)", "java.lang.Thread.run(java/lang/Thread.java:748)"]}

Does logstash not support

" >= ", " <= " ? I beleive the error is there. I tried to simply put it to >,< symbols, still says the same.

I also tried

mutate { convert => { "score" => "integer" } }

with both >=, <= and simply a >,< which is still the same error.

Please help me out.

Thanks.
Katara

Badger · February 3, 2020, 1:02pm

That may indicate that the field time_in_days does not exist. The ruby code has a conditional that determines whether it creates the field, so sometimes it will not.

katara · February 3, 2020, 1:50pm

@Badger,
Yes, I'm trying to get the age of tickets on condition,
That the closed_at is empty, meaning it is not closed yet. So for all tickets that aren't closed, age must be calculated.

There are both closed and non - closed tickets in my data set.
So there must be columns with the time_in_days for atleast half of my data set.
How do I approach this?

Badger · February 3, 2020, 4:42pm

Wrap all these conditionals in a conditional that checks the field exists

if [time_in_days] {

system · March 2, 2020, 4:42pm

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.