topic Re: Display ticket status and count in Splunk Search

Display ticket status and count

nischal45 — Thu, 20 May 2021 22:05:00 GMT

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!

Re: Display ticket status and count

richgalloway — Fri, 21 May 2021 00:24:51 GMT

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"

Re: Display ticket status and count

nischal45 — Fri, 21 May 2021 01:41:55 GMT

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.

Re: Display ticket status and count

richgalloway — Fri, 21 May 2021 12:19:36 GMT

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.