Can you help me create a search that helps me find currently open tickets?

[cocomaster]

Hi guys,

Tickets can have states:

em7_state = Open
em7_state = In Progress
em7_state = Closed

Tickets are stored in the following format:

date,time,em7_state,em7_description,em7_ticket_id
date,time,em7_state,em7_description,em7_ticket_id
date,time,em7_state,em7_description,em7_ticket_id
date,time,em7_state,em7_description,em7_ticket_id
date,time,em7_state,em7_description,em7_ticket_id

So it might happen, that a ticket gets created with status open:

2018-07-01,00:00:01,Open,em7_description,em7_ticket_id

Then it gets updated (to In Progress) at

2018-09-03,20:00:01,In
Progress,em7_description,em7_ticket_id

And it is not closed until today.

How do I search for tickets that are currently open ?

If i do a simple search like:

index=xxxx (em7_state = "Open" OR
em7_state = "In Progress") | dedup em7_ticket_id

Then my search would be bound to the timeframe selected — let's say last 24hrs; thus, tickets created earlier won't show up (because there was no change in em7_state logged).

Thanks for your input

[renjith_nair]

@cocomaster ,

index=xxxx |stats latest(em7_state) as status by em7_ticket_id
|where status="Open" OR status="In Progress")

However, this is also time dependent as you mentioned. If you want to limit your searches only to a certain time period, then you need to push the status more often regardless of there is a change or not (count to license). Another approach is to use a lookup or summary index which is populated regularly with the latest status (scheduled search) and use them in the dashboards.

[cocomaster]

Thanks very much,is there any chance that i can add additional column like em7_ticket_time to this result?

[renjith_nair]

@cocomaster ,yes ofcourse.

 index=xxxx |stats latest(em7_state) as status,latest(em7_ticket_time ) as  em7_ticket_time  by em7_ticket_id
 |where status="Open" OR status="In Progress")