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.
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
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
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
hi cusello, the approaches you identified worked. thank you much!