# Sort tickets by age group
**URL:** https://discuss.elastic.co/t/sort-tickets-by-age-group/216186
**Category:** Logstash
**Created:** [January 23, 2020, 6:52am UTC](https://discuss.elastic.co/t/sort-tickets-by-age-group/216186 "2020-01-23T06:52:57Z")
**Posts on this page:** 13
**Page:** 1
### Author:  [@katara](https://discuss.elastic.co/u/katara)
#### Post date: [January 23, 2020, 6:52am UTC](https://discuss.elastic.co/t/sort-tickets-by-age-group/216186/1 "2020-01-23T06:52:57Z")
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:
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.
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.
---
### Author:  [@katara](https://discuss.elastic.co/u/katara)
#### Post date: [January 27, 2020, 5:47am UTC](https://discuss.elastic.co/t/sort-tickets-by-age-group/216186/2 "2020-01-27T05:47:54Z")
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
---
### Author:  [@katara](https://discuss.elastic.co/u/katara)
#### Post date: [January 28, 2020, 6:14am UTC](https://discuss.elastic.co/t/sort-tickets-by-age-group/216186/3 "2020-01-28T06:14:53Z")
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?
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [January 28, 2020, 3:38pm UTC](https://discuss.elastic.co/t/sort-tickets-by-age-group/216186/4 "2020-01-28T15:38:27Z")
> [@katara](#):
>
> Expected one of #, ( at line 36, column 17 (byte 1476) after ...\n\nif time\_in\_days
That is complaining about
```
if time_in_days = 0
```
The equality operator in logstash is ==, not =.
---
### Author:  [@katara](https://discuss.elastic.co/u/katara)
#### Post date: [January 30, 2020, 10:35am UTC](https://discuss.elastic.co/t/sort-tickets-by-age-group/216186/5 "2020-01-30T10:35:35Z")
@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
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [January 30, 2020, 5:43pm UTC](https://discuss.elastic.co/t/sort-tickets-by-age-group/216186/6 "2020-01-30T17:43:21Z")
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.
---
### Author:  [@katara](https://discuss.elastic.co/u/katara)
#### Post date: [January 31, 2020, 4:24am UTC](https://discuss.elastic.co/t/sort-tickets-by-age-group/216186/7 "2020-01-31T04:24:36Z")
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
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [January 31, 2020, 2:48pm UTC](https://discuss.elastic.co/t/sort-tickets-by-age-group/216186/8 "2020-01-31T14:48:37Z")
As I said in my previous post, the logical operator "and" has to be lowercase.
---
### Author:  [@katara](https://discuss.elastic.co/u/katara)
#### Post date: [February 3, 2020, 4:41am UTC](https://discuss.elastic.co/t/sort-tickets-by-age-group/216186/9 "2020-02-03T04:41:38Z")
@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
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [February 3, 2020, 1:02pm UTC](https://discuss.elastic.co/t/sort-tickets-by-age-group/216186/10 "2020-02-03T13:02:46Z")
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.
---
### Author:  [@katara](https://discuss.elastic.co/u/katara)
#### Post date: [February 3, 2020, 1:50pm UTC](https://discuss.elastic.co/t/sort-tickets-by-age-group/216186/11 "2020-02-03T13:50:07Z")
@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?
---
### Author:  [@Badger](https://discuss.elastic.co/u/Badger)
#### Post date: [February 3, 2020, 4:42pm UTC](https://discuss.elastic.co/t/sort-tickets-by-age-group/216186/12 "2020-02-03T16:42:08Z")
> [@katara](#):
>
> if [time\_in\_days] == 0 {
Wrap all these conditionals in a conditional that checks the field exists
```
if [time_in_days] {
```
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [March 2, 2020, 4:42pm UTC](https://discuss.elastic.co/t/sort-tickets-by-age-group/216186/13 "2020-03-02T16:42:15Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.