Display ticket status and count

[nischal45]

Need help with a query please:
I have ticket data where the life cycle is Assigned, Work in Progress, Fixed, Closed and the ticket is assigned to our group ABC. I want to display only the tickets which are assigned and in progress to our group ABC.

My end goal is to show ABC group's ticket count which are not fixed and closed. I have the below query so far:

TICKET         STATUS                         GROUP
TIC12345   Assigned                        ABC
                       Work in Progress         ABC
                       Fixed                                DEF
                      Closed                              DEF

index=* source=* group=ABC
| stats latest(status) as l_status latest(group) as l_group by TICKET
| search NOT l_status in("Fixed", "Closed")

Result:
TICKET          STATUS                  GROUP
TIC12345  Work in Progress   ABC

I was able to get the data however, I'm also getting ticket which are closed(because it is initially assigned to ABC and later it was closed by DEF). Appreciate your help! 

[richgalloway · Accepted solution]

If more than one group can work on a ticket then the group name cannot be in the base query.  Filter by group name after filtering on status.

index=foo source=bar
| stats latest(STATUS) as STATUS, latest(GROUP) as GROUP by TICKET
| search STATUS IN ("Assigned", "Work in Progress") AND GROUP="ABC"

[nischal45]

I agree but the amount of data i’m dealing with is in millions and was trying to save some computing power.

Do you think this query would take lot of processing power if  i’m searching for ~11 months of  data.

[richgalloway]

I understand wanting (or needing) to reduce the number of events looked at, but we shouldn't throw away data we need for the query to work.  How long it will take to process millions of events depends on how many indexers are doing the processing and how well distributed the data is.