Splunk Search

How to edit my search to find the status of tickets for my groups?

tomaszwrona
Explorer

Hello,

i have a data from ticketing system where events looks (more or less for the simplicity) like this:

date, ticketNumber, status, group

and for every change in the ticket a new Splunk event is generated, eg:

24.02.2017 09:50,0001,Assigned,G1
24.02.2017 10:00,0001,In progress,G1
24.02.2017 11:00,0001,Closed,G6
24.02.2017 11:30,0002,In Progress,G2
24.02.2017 11:45,0003,Pending,G3
24.02.2017 12:00,0003,Resolved,G3

Now i want to know which tickets are open (status assigned, pending or in progress) for my groups (G1-G5)
What i'm expecting to get is:

0002,In Progress,G2

But my search:

index=tickets group="G1" OR group="G2" OR group="G3" OR group="G4" OR group="G5" | dedup ticketNumber | search status="Assigned" OR status="Pending" OR status="In Progress" 

does not see the closures of ticket done by other groups and i am getting:

0001,In progress,G1
0002,In Progress,G2

How should i modify the search to meet my expectations?
I need somehow to filter the tickets before any calculation as there are lot of tickets and many groups. And also the groups which are closing tickets belonging to our groups are not defined, so it could be G6-GXX.

0 Karma
1 Solution

Richfez
SplunkTrust
SplunkTrust

From your description, I think are filtering up front but what you need to do is get some grouping on your events before you filter out what you don't want.

Here's one way using transaction. You'll notice I set up some data like yours (in fact, some of it IS yours - I added another dozen lines to get more variance, but it's essentially what you posted above) with fields ticketNumber, status and group.

source="answers_504590.txt" index="answers_504590" sourcetype="answers_504590" 
| rex field=_raw "[^,],(?<ticketNumber>\d+),(?<status>[^,]+),(?<group>\w+)"
| eval IsOriginallyMyGroup=if(status="Assigned" AND (group="G1" OR group="G2"), 1, NULL)
| eval ClosedByMyGroup=if((status="Resolved" OR status="Closed") AND (group="G1" OR group="G2"), 1, NULL)
| transaction ticketNumber
| where IsOriginallyMyGroup=1

Don't worry about the first two lines, they're me making my stuff look like yours. I should have added a header line to the text file and made the fields that way, but I forgot at data ingestion so I rexed up the field names.

Line 3 creates a new field called IsOriginallyMyGroup and sets it to 1 when it matches the criteria of having been Assigned at some point to groups G1 or G2. You'll have a BIT more stuff in there, you need to match 5 of them, but I cheated. 🙂 NOTE this is working on original events, not on any summarization of them, but one by one it flags individual events if it's an "Assigned" with the right groups.

Line 4 does a similar thing with a new field ClosedByMyGroup which I don't think you needed, but seemed easy enough to throw in. You can use it later like I use the IsOriginallyMyGroup we created before. If you wanted.

Line 5 creates a transaction out of each group based on the ticketNumber. This groups them together like you want.

Line 6 then tells Splunk to only give back those "sets" of events (= transactions) where one line in it defined that it was IsOriginallyMyGroup. This gives you back just the events - no matter who closed them - where they were assigned to your group.

You can run each piece (well, each piece plus all the preceding pieces) to see what exactly it does each time.

Here's another way using Stats.

source="answers_504590.txt" index="answers_504590" sourcetype="answers_504590" 
| rex field=_raw "[^,],(?<ticketNumber>\d+),(?<status>[^,]+),(?<group>\w+)"
| eval IsOriginallyMyGroup=if(status="Assigned" AND (group="G1" OR group="G2"), 1, NULL)
| eval ClosedByMyGroup=if((status="Resolved" OR status="Closed") AND (group="G1" OR group="G2"), 1, NULL)
| eval status_and_group=status."-".group
| stats count AS Steps, list(status_and_group) AS Step_and_Group BY ticketNumber 

Most of this is much like the first one, the difference being that I, in the second to last line, make a new field as a combination of the status and group fields. Take a look at the results, I found it hard to get it into a nice enough format. NOT that it is impossible, only that from what I'm imagining your usage is, I think you'll have to fiddle with the output quite a bit, then still not really get quite what you want.

Lastly, here's using a subsearch. This deserves explanation ahead of time. The subsearch (the entire bits between the [] signs), is essentially the same search as the inside search with only a couple of changes. What it does is create a list (table ...) of the ticketNumbers that have the right status (assigned) and group (in this case G1 or G2 - you'll want G1, G2, G3, G4, or G5). That list - the subsearch results - get returned into the search .... section that immediately precedes it, so the search ... ends up looking like search ticketNumber=0001 OR ticketNumber=0003 and so on, with all the matching ticketNumbers in it. So, now that that's been explained, let's see it all:

source="answers_504590.txt" index="answers_504590" sourcetype="answers_504590" 
| rex field=_raw "[^,],(?<ticketNumber>\d+),(?<status>[^,]+),(?<group>\w+)" 
| search 
    [ search source="answers_504590.txt" index="answers_504590" sourcetype="answers_504590" 
    | rex field=_raw "[^,],(?<ticketNumber>\d+),(?<status>[^,]+),(?<group>\w+)"
    | search status=Assigned AND (group=G1 OR group=G2) 
    | table ticketNumber
] 
| eval IsOriginallyMyGroup=if(status="Assigned" AND (group="G1" OR group="G2"), 1, NULL) 
| eval ClosedByMyGroup=if((status="Resolved" OR status="Closed") AND (group="G1" OR group="G2"), 1, NULL) 
| transaction ticketNumber

Again, first two lines just set up my data to look like yours. Then I pipe that into a search that has a subsearch fill in parts of it, as I explained above. You'll see the first two subsearch lines are AGAIN just me making my data look like yours. 🙂

Then the subsearch ends. The rest is nearly exactly like the Transaction example I posted first so you can read that to find out how it works, but it's pretty straightforward. But notice now you don't need the trailing search/where, because you've filtered them effectively with that subsearch.


So which should you use?

Well, stats will be faster - possibly FAR faster - if you have a lot of indexers. But it is IMO going to be the hardest to get "looking" right.

The transaction way (the first one) isn't as efficient, but probably gives you the nicest output with the least effort. It's the slowest, probably.

The subsearch method has some really big limitations, but ones that probably won't matter in your limited-event case. The subsearch is limited on how many items it will return, I believe 10,000. In "general" use this can be a big problem. In this particular case, I think it's probably not an issue at all. I mean, you'll have what, a few hundred of these max? That should be fine. Also, by using the subsearch in there, you limit how much stuff your transaction has to do making it somewhat better. This is, I think, the best method (assuming less than 10,000 items), but your opinion may differ.

Anyway, happy splunking, let us know how this goes!

View solution in original post

Richfez
SplunkTrust
SplunkTrust

From your description, I think are filtering up front but what you need to do is get some grouping on your events before you filter out what you don't want.

Here's one way using transaction. You'll notice I set up some data like yours (in fact, some of it IS yours - I added another dozen lines to get more variance, but it's essentially what you posted above) with fields ticketNumber, status and group.

source="answers_504590.txt" index="answers_504590" sourcetype="answers_504590" 
| rex field=_raw "[^,],(?<ticketNumber>\d+),(?<status>[^,]+),(?<group>\w+)"
| eval IsOriginallyMyGroup=if(status="Assigned" AND (group="G1" OR group="G2"), 1, NULL)
| eval ClosedByMyGroup=if((status="Resolved" OR status="Closed") AND (group="G1" OR group="G2"), 1, NULL)
| transaction ticketNumber
| where IsOriginallyMyGroup=1

Don't worry about the first two lines, they're me making my stuff look like yours. I should have added a header line to the text file and made the fields that way, but I forgot at data ingestion so I rexed up the field names.

Line 3 creates a new field called IsOriginallyMyGroup and sets it to 1 when it matches the criteria of having been Assigned at some point to groups G1 or G2. You'll have a BIT more stuff in there, you need to match 5 of them, but I cheated. 🙂 NOTE this is working on original events, not on any summarization of them, but one by one it flags individual events if it's an "Assigned" with the right groups.

Line 4 does a similar thing with a new field ClosedByMyGroup which I don't think you needed, but seemed easy enough to throw in. You can use it later like I use the IsOriginallyMyGroup we created before. If you wanted.

Line 5 creates a transaction out of each group based on the ticketNumber. This groups them together like you want.

Line 6 then tells Splunk to only give back those "sets" of events (= transactions) where one line in it defined that it was IsOriginallyMyGroup. This gives you back just the events - no matter who closed them - where they were assigned to your group.

You can run each piece (well, each piece plus all the preceding pieces) to see what exactly it does each time.

Here's another way using Stats.

source="answers_504590.txt" index="answers_504590" sourcetype="answers_504590" 
| rex field=_raw "[^,],(?<ticketNumber>\d+),(?<status>[^,]+),(?<group>\w+)"
| eval IsOriginallyMyGroup=if(status="Assigned" AND (group="G1" OR group="G2"), 1, NULL)
| eval ClosedByMyGroup=if((status="Resolved" OR status="Closed") AND (group="G1" OR group="G2"), 1, NULL)
| eval status_and_group=status."-".group
| stats count AS Steps, list(status_and_group) AS Step_and_Group BY ticketNumber 

Most of this is much like the first one, the difference being that I, in the second to last line, make a new field as a combination of the status and group fields. Take a look at the results, I found it hard to get it into a nice enough format. NOT that it is impossible, only that from what I'm imagining your usage is, I think you'll have to fiddle with the output quite a bit, then still not really get quite what you want.

Lastly, here's using a subsearch. This deserves explanation ahead of time. The subsearch (the entire bits between the [] signs), is essentially the same search as the inside search with only a couple of changes. What it does is create a list (table ...) of the ticketNumbers that have the right status (assigned) and group (in this case G1 or G2 - you'll want G1, G2, G3, G4, or G5). That list - the subsearch results - get returned into the search .... section that immediately precedes it, so the search ... ends up looking like search ticketNumber=0001 OR ticketNumber=0003 and so on, with all the matching ticketNumbers in it. So, now that that's been explained, let's see it all:

source="answers_504590.txt" index="answers_504590" sourcetype="answers_504590" 
| rex field=_raw "[^,],(?<ticketNumber>\d+),(?<status>[^,]+),(?<group>\w+)" 
| search 
    [ search source="answers_504590.txt" index="answers_504590" sourcetype="answers_504590" 
    | rex field=_raw "[^,],(?<ticketNumber>\d+),(?<status>[^,]+),(?<group>\w+)"
    | search status=Assigned AND (group=G1 OR group=G2) 
    | table ticketNumber
] 
| eval IsOriginallyMyGroup=if(status="Assigned" AND (group="G1" OR group="G2"), 1, NULL) 
| eval ClosedByMyGroup=if((status="Resolved" OR status="Closed") AND (group="G1" OR group="G2"), 1, NULL) 
| transaction ticketNumber

Again, first two lines just set up my data to look like yours. Then I pipe that into a search that has a subsearch fill in parts of it, as I explained above. You'll see the first two subsearch lines are AGAIN just me making my data look like yours. 🙂

Then the subsearch ends. The rest is nearly exactly like the Transaction example I posted first so you can read that to find out how it works, but it's pretty straightforward. But notice now you don't need the trailing search/where, because you've filtered them effectively with that subsearch.


So which should you use?

Well, stats will be faster - possibly FAR faster - if you have a lot of indexers. But it is IMO going to be the hardest to get "looking" right.

The transaction way (the first one) isn't as efficient, but probably gives you the nicest output with the least effort. It's the slowest, probably.

The subsearch method has some really big limitations, but ones that probably won't matter in your limited-event case. The subsearch is limited on how many items it will return, I believe 10,000. In "general" use this can be a big problem. In this particular case, I think it's probably not an issue at all. I mean, you'll have what, a few hundred of these max? That should be fine. Also, by using the subsearch in there, you limit how much stuff your transaction has to do making it somewhat better. This is, I think, the best method (assuming less than 10,000 items), but your opinion may differ.

Anyway, happy splunking, let us know how this goes!

niketn
Legend

You should filter required fields upfront and then do a dedup.

 index=tickets (group="G1" OR group="G2" OR group="G3" OR group="G4" OR group="G5") (status="Assigned" OR status="Pending" OR status="In Progress") 
| dedup ticketNumber 

infact you should try stats instead if you want meaningful information

 index=tickets (group="G1" OR group="G2" OR group="G3" OR group="G4" OR group="G5") (status="Assigned" OR status="Pending" OR status="In Progress") 
| stats count as StatusCount min(_time) as TicketCreatedTime max(_time) as currentStatusTime values(status) as Status by ticketNumber 
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Richfez
SplunkTrust
SplunkTrust

Please clarify:

You said "which tickets are open (status assigned, pending or in progress) for my groups (G1-G5)"
But then "my search ... does not see the closures of ticket done by other groups"

Right because you've filtered them out already.

Is what you want tickets started by your group, but group doesn't matter much otherwise? And do you have to separate out specifically the tickets started by other groups but closed by yours? That seems to be the other edge case you haven't mentioned.

0 Karma

tomaszwrona
Explorer

sure, i just need to get a list of open ticket currently being assigned to one of my groups

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...