Splunk Search

Display ticket status and count

nischal45
Engager

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! 

Labels (3)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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"
---
If this reply helps you, Karma would be appreciated.

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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"
---
If this reply helps you, Karma would be appreciated.
0 Karma

nischal45
Engager

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Demo Day: Strengthen Your SOC with Splunk Enterprise Security 8.1

Today’s threat landscape is more complex than ever. Security operation centers (SOCs) are overwhelmed with ...

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...