Splunk Enterprise

comparing field values across sources/indexes

gdang
New Member

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.

0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

Sukisen1981
Champion

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

0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

gdang
New Member

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

0 Karma
Get Updates on the Splunk Community!

Announcing Scheduled Export GA for Dashboard Studio

We're excited to announce the general availability of Scheduled Export for Dashboard Studio. Starting in ...

Extending Observability Content to Splunk Cloud

Watch Now!   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to leverage ...

More Control Over Your Monitoring Costs with Archived Metrics GA in US-AWS!

What if there was a way you could keep all the metrics data you need while saving on storage costs?This is now ...