How to find out tickets moved from one queue to other

[architkhanna]

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)

[gcusello]

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

[architkhanna]

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 ::

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 |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

[gcusello]

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:

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 [ | inputlookup lookpuname | fields assignmentgroup ]
| table ticketnumber assignmentgroup

Ciao.

Giuseppe

[architkhanna]

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.

[gcusello]

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

[architkhanna]

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.

[gcusello]

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

[architkhanna]

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.

[gcusello · Accepted solution]

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

[architkhanna]

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!

[gcusello]

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 😉

[architkhanna]

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