comparing field values across sources/indexes

[gdang]

I have two different datasets as follows:
dataset A - there is a field called TicketNo

dataset B - there are two fields as follows:

DESCRIPTION - a text field that sometimes just contains ticket number but sometimes it also includes ticket number and other information in it.

EFFORT - tracks the number of hours spent resolving the ticket.

For each ticket X in dataset A, I need to find all corresponding records in dataset B if the DESCRIPTION contains the ticket number X. If there is a match, grab the EFFORT.

Example:
dataset A ->

TicketNumber
T1
T2
T30

dataset B ->
DESCRIPTION EFFORT
T1 2
T20 1
T1 3
T2 1

The result I expect is this..
For ticket T1 found in dataset A, two records in dataset B are found and the total effort is 5.
For ticket T2 found in dataset A, one record in dataset B is found and the corresponding effort is 1.
For ticket T30 found in dataset A, no matches found in B.

[Sukisen1981]

Here is what you need to do :
Split description effort field into 2 fields, having the ticket number and effort, you will have to use regex or sub string the ticket number and effort
Now have a query , something like this
index="dataset a" | chart count by ticket number | join _ticket number [search index="dataset b"| chart sum(effort) by ticket number]

you will get something like this:
Ticket number , count , sum(effort)
now, a final tuning:
index="dataset a" | chart count by ticket number | join _ticket number [search index="dataset b"| chart sum(effort) by ticket number] | fields Ticket number,sum(effort)| rename sum(effort) AS Effort

[gcusello · Accepted solution]

Hi gdang,
there are two ways:
the more efficient is to extract TicketNo from Description of DatasetB using a regex (if you share some example I can help you to create it) and use this value to filter TicketNos of datasetA, something like this:

your_DatasetA [ search your_DatasetB | rex field=Description "your_regex" | fields TicketNo  ]
| ....

The second way (less efficient) is to use asterisks in search

your_DatasetB [ search your_DatasetA | eval Description="*"+TicketNo+"*" | fields Description
| ....

(I'm not sure that the second one solves your problem, but I share it)
Bye.
Giuseppe

[gdang]

hi cusello, the approaches you identified worked. thank you much!