topic How to find out tickets moved from one queue to other in Splunk Search

How to find out tickets moved from one queue to other

architkhanna — Tue, 14 Jun 2022 16:24:16 GMT

I have a real time Splunk index pushing records into two source types. Source type 1 holds fields including assignmentgroup, manager name , entity etc. Source type 2 hold fields including ticketnumber , assignmentgroup,priority etc.

Sourcetype 2 has tickets updates coming in and each ticket can move from one assignmentgroup to another assignmentgroup which may or may not be present in Source type 1

I would like to find out how many tickets are there in Sourcetype 2 which moved out from assignmentgroups of Sourcetype 1? In other words, how many tickets are present in Sourcetype 2 whose assignmentgroup doesnt belong to the assignmentgroup present in Source type 1.

Any leads would be helpful.
TIA!

Just an update, this sourcetype 1 is actually pushed to a lookup file (that has same collumns as in Source type 1, Hence , I intend to use this lookup in the search query)

Re: To find out tickets moved from one queue to other

gcusello — Tue, 14 Jun 2022 06:24:03 GMT

Hi @architkhanna,

let me understand: you want to find those ticketnumbers that changed assignmentgroup (both the new and the old assignmentgroup in in sourcetype2) and the new assignmentgroup isn't in sourcetype1, is this correct?

If this is your need, please try something like this:

index=your_index sourcetype=sourcetype2 | stats dc(assignmentgroup) AS dc_assignmentgroup values(assignmentgroup) AS assignmentgroup BY ticketnumber | where dc_assignmentgroup>1 | mvexpand assignmentgroup | search NOT [ search index=your_index sourcetype=sourcetype1 | dedup assignmentgroup | fields assignmentgroup ] | table ticketnumber assignmentgroup

In few words:

I found the tickenumbers that changed assignment group
I found the ones not present in sourcetype1

Ciao.

Giuseppe

Re: To find out tickets moved from one queue to other

architkhanna — Tue, 14 Jun 2022 07:29:10 GMT

Hello @gcusello ,

Thank you for your response, I think we are very close to the solution here .I have updated the question to clear out the confusion if any

And I used the below query ::

| search NOT [ search |lookup lookpuname assignmentgroup AS assignmentgroup_raw OUTPUT assignmentgroup manager | dedup assignmentgroup | fields assignmentgroup ]
| table ticketnumber assignmentgroup

Using this, I do not get anything in the table but the event counts, rest all is blank

Re: To find out tickets moved from one queue to other

gcusello — Tue, 14 Jun 2022 07:43:28 GMT

Hi @architkhanna,

the subsearch is wrong, but wher do you have events of sourcetype1: in an index or in a lookup?

if in an index, my first search is correct, if instead they are in a lookup, please try something like this:

Ciao.

Giuseppe

Re: To find out tickets moved from one queue to other

architkhanna — Tue, 14 Jun 2022 09:32:36 GMT

Hi @gcusello ,
To clear it , sourcetype 1 is in lookup and sourcetype 2 is in index.

I tried using your last search and it did return ticketnumber and assignmentgroup this time , however, when I picked assignmentgroup from this table and searched in sourcetype 1, it actually has a mix of ticket that has assignmentgroup moved but there are scenarios where none of the assignmentgroup from this result are present in sourcetype 1 at all .

So something seems to be missing here.

Re: To find out tickets moved from one queue to other

gcusello — Tue, 14 Jun 2022 09:34:23 GMT

Hi @architkhanna,

maybe I didn't understood:

I'm searching for tickets in the index (sourcetype2) that changed assignmentGroup and aren't in the lookup, is this the result you want or a different one?

For this reason you canot find them in the lookup.

Ciao.

Giuseppe

Re: To find out tickets moved from one queue to other

architkhanna — Tue, 14 Jun 2022 09:54:25 GMT

Hi @gcusello

"I'm searching for tickets in the index (sourcetype2) that changed assignmentGroup and aren't in the lookup, is this the result you want or a different one? " --> No

I would like to find out how many tickets are there in Sourcetype 2 which moved out from assignmentgroups present in Sourcetype 1(lookup)?

How I validate is something Im mentioning below:::

After using your query result , If I pick up any ticket and search it in sourcetype 2 (since it has all historic data as well ,because hops are made from one assignmentgroup to other) I will see list of changes that happened on that ticket. Ideally few assignmentGroup should be matching from this list to the lookup(Sourcetype 1) and few should not. But I see cases where none are matching, which is something wrong.

I hope I'm clear this time.

Re: To find out tickets moved from one queue to other

gcusello — Tue, 14 Jun 2022 11:03:27 GMT

Hi @architkhanna,

ok, the exact opposite of what I understood!

please try this:

index=your_index sourcetype=sourcetype2 | stats dc(assignmentgroup) AS dc_assignmentgroup values(assignmentgroup) AS assignmentgroup values(manager_name) AS manager_name BY ticketnumber | where dc_assignmentgroup>1 | mvexpand assignmentgroup | lookup lookupname assignmentgroup OUTPUT manager_name | eval status=if(manager_name="*","IN","OUT") | stats dc(status) AS dc_status values(status) AS status BY ticketnumber | where dc_status>1 | stats count

Ciao.

Giuseppe

Re: To find out tickets moved from one queue to other

architkhanna — Wed, 15 Jun 2022 06:47:35 GMT

Hi @gcusello

I'm really not sure why have you used manager in your example this time. That is not be used anywhere in logic or for validation.
And since nothing comes in > 1 clause , hence no results.

I believe We just need to tweak our query you shared before this.

Re: To find out tickets moved from one queue to other

gcusello — Wed, 15 Jun 2022 06:52:20 GMT

Hi @architkhanna,

the logic of the first halp I suppose is clear, the logic of the second half is to check if the one assignedGroup is in the lookup and if the second one isn't in.

If it doesn't work, please try this:

index=your_index sourcetype=sourcetype2 | stats dc(assignmentgroup) AS dc_assignmentgroup values(assignmentgroup) AS assignmentgroup values(manager_name) AS manager_name BY ticketnumber | where dc_assignmentgroup>1 | mvexpand assignmentgroup | lookup lookupname assignmentgroup OUTPUT manager_name | eval status=if(isnull(manager_name),"OUT","IN") | stats dc(status) AS dc_status values(status) AS status BY ticketnumber | where dc_status>1 | stats count

Ciao.

Giuseppe

Re: To find out tickets moved from one queue to other

architkhanna — Wed, 15 Jun 2022 07:08:12 GMT

Thank you @gcusello

I do get some count now which is quite low than what I expected. I will validate it and let you know how it went.

Just so you know , the dc_status gives either 1 or 2 value in the end and a ticket in sourcetype 2 can have as much as 10 hops between assignmentgroups plus sourcetype 1/ lookup can have duplicate entrues as well. I hope that is being handled in your last query.
Although we need not have to worry about how many of these 10 hops it goes out ofassignmentgroup which is not present in sourcetype 1 /lookup1. Even if it does goes out once, it can be considered in my final count.

Thank you!

Re: To find out tickets moved from one queue to other

gcusello — Wed, 15 Jun 2022 08:03:41 GMT

Hi @architkhanna,

as I said, I'd like to show you an approach to solve search requirements, not the final solution also because I cannot test your searches on your data.

I hope that I reached my objective.

If my answer solves your need, please accept it of the other people of Community.

Ciao. and happy splunking.

Giuseppe

P.S.: Karma Points are appreciated 😉

Re: To find out tickets moved from one queue to other

architkhanna — Wed, 15 Jun 2022 08:26:43 GMT

@gcusello Absolutely it helped.
I get what I was looking for.
Thank you so much for your patience and quick response.