In three related events, how do you avoid the one without a field value?

[mrovirab]

Hello,

I have a tricky question.

I'm trying to count tickets by providers we have. I am using the parent and subtasks to check to which team we are sending a subtask + using the service to know the provider.

I'm stuck in cases like these ones.

3 events which are

- the parent task .1 with no to_team and no provider

- subtask 1 with one to_team

- subtask num 2 with a provider (different to the team above)

Now I have the three of them counted as Provider1 (subtask num1), Provider2 (subtask number 2) and Other (parent task). However, what I need is to avoid counting the parent task if there's a subtask with the needed information.

There are some parent task with no information that have to be in "Other" section because they need to be counted, but just when there's no subtask attached.

Is it possible?

I have tried subsearches but I cannot achieve one that works.

Thank you in advance.

[ITWhisperer]

Can you provide more detailed examples of the events you are dealing with and how they inter-relate?

[mrovirab]

The number of events inter related is something I cannot say because it depends on if one ticket has been delegated or not.

Selected Fields

Interesting Fields

• index 24
• Product 19
• Service 100+
• created_at 100+
• from_team_name 12
• from_team_type 3
• Provider 38
• redirection_type 4
• sub_task 100+
• task_number 100+
• ticket_number 100+
• urgency 5

This are the fields I'm using to find out how many tickets we have handled for each Product looking at Provider .

I remove duplicates with | dedup ticket_number,Product because one ticket can have more than one product related. In the example above I'm counting three events with the same ticket number but with three different products one being "Other", which can make sense. The issue is that one of those events is the parent task that appears in Other because in that event the Provider field is empty. In those situations I would like to skip the parent task as it's not true there's no identified product, its subtask has one, but it' not populated in the event for the parent task.

What I was thinking at the beginning is to use one of the subtask value Provider and bring that to the parent task event. Then, when executing the dedup it wouldn't be counted because two events will have the same Product and Provider. However, I don't know how to actually build a subsearch for that.

[ITWhisperer]

How do you know which subtask event is related to which parent task event?

Can you provide some sample events (anonymised appropriately)?

[mrovirab]

Hi,

These are the values for the example, anonymised enough but with useful values.

ticket_number	task_number	parent_task	sub_task	Product	Provider
tic3624	tic3624.1.2	tic3624.1	tic3624.1.2	A	a
tic3624	tic3624.1.1	tic3624.1	tic3624.1.1	B	b
tic3624	tic3624.1	tic3624.1		Other

All three events have the same ticket_number and same parent_task. And this will happen with other cases like this one.

It gives the count of 1 ticket for Product A, one ticket for Product B and one ticket for Other, but this last part not being completely true. If we look at the event is correct, because there's no Provider, but it was something we delegated.

Filtering out all the events without sub_task will not work because some of the task are not delegated that need to be counted.

[ITWhisperer]

Does this work for you?

| stats values(Provider) as Provider by ticket_number
| stats count by Provider

[mrovirab]

Hello,

I tried and in this particular case it works, but not for all the cases.

I have discovered that some of them are missing the Provider field but they should be counted, hence I need to use another field that indicates to which team we have send the case.

Then the issue is when the Product is other for a parent task that has subtask with a Provider or a destination team.

This is getting too much complicated maybe 😞

[ITWhisperer]

Can you provide an anonymised example of this case too please?

[mrovirab]

Hi,

I hope this fits the request, not sure if I'm adding too short info.

These are the values for the example, anonymised enough but with useful values.

row #	ticket_number	task_number	parent_task	sub_task	Product	Provider	to_team
1	tic3624	tic3624.1.2	tic3624.1	tic3624.1.2	A	a	bcn
2	tic3624	tic3624.1.1	tic3624.1	tic3624.1.1	B	b
3	tic3624	tic3624.1	tic3624.1		Other
4	tic3625	tic3625.1	tic3625.1		A	a
5	tic3626	tic3626.1	tic3626.1		Other
6	tic3626	tic3626.1	tic3626.1	tic3626.1.1	A		bcn
7	tic3627	tic3627.1	tic3627.1		Other

I have a long eval checking both Provider and to_team fields, always giving priority to the provider, but checking the team in case there's a subtask delegated. Then, in the example above, if only the Other (rows 3 & 5) should not be counted but because there are subtask. At the end, the Other is there in case there are tickets that can not be identified (i.e. last row)

Thanks

[ITWhisperer · Accepted solution]

Not sure if works for you since it count Product rather than Provider

| stats values(Product) as Product by ticket_number
| eval _Products=mvfilter(Product!="Other")
| eval Product=coalesce(_Products,Product)
| stats count by Product

[mrovirab]

I have checked some examples and it seems to work! 🙂

In addition, the other count has decreased from 530 to 379 and it fits for me to still have a big number, but the decrease thinking of all the duplicated events between parent tasks and sub tasks.

Can you please explain me a little bit what those functions do? To learn more.

Thank you!

[ITWhisperer]

``` Gather all the unique values of Product into a multi-value field called Product ```
| stats values(Product) as Product by ticket_number
``` Create a copy of the multi-value field without the "Other" product value ```
| eval _Products=mvfilter(Product!="Other")
``` Overwrite Product with the filtered version if it is not null ```
``` _Products will be null if the only Product for the ticket is Other ```
| eval Product=coalesce(_Products,Product)
``` Count the occurrences of each Product (including Other) ```
| stats count by Product