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!

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...

Want to Reduce Costs, Mitigate Risk, Improve Performance, or Increase Efficiencies? ...

Splunk Lantern is Splunk’s customer success center that provides advice from Splunk experts on valuable data ...