Splunk Search

Display Matches Between Query and Lookup File

IRHM73
Motivator

Hi, I wonder whether someone may be able to help me please.

I'm using the following query to successfully return a list of telephone numbers.

`field_wmf(misReceived)`
| fields detail.45BxData
| rex field=detail.Data "dayPhoneNumber\"\:\"(?<day>[^\"]+)\""
| rex field=detail.Data "eveningPhoneNumber\"\:\"(?<evening>[^\"]+)\""
| fillnull value="" day evening
| eval telno=day + ";" + evening
| makemv delim=";" telno
| mvexpand telno
| search telno="07*"
| rex mode=sed field=telno "s/ //g"]
| dedup telno 

I then have a lookup table of telephone numbers which I want to compare against my query.

Both field names are called tel no, and what I'd like to do is create another column called "Match" which will indicate a match between the query and lookup file by virtue of the letter "Y" but I'm not quite sure about how to go about this.

I just wondered whether someone could look at this please and offer some guidance on how I can go about about this.

Many thanks and kind regards

Chris

Tags (3)
0 Karma

IRHM73
Motivator

All,

I was able to resolve this with the following:

| lookup tellist.csv telno OUTPUT telno as Matched
| eval Matched=if(isnotnull(Matched), "Y", "N") 
| table telno Matched
0 Karma

cmerriman
Super Champion

Reading this, I'd probably go with an append to add in your lookup table, to add the entire table to the bottom.
something like this maybe?

 `field_wmf(misReceived)`
 | fields detail.45BxData
 | rex field=detail.Data "dayPhoneNumber\"\:\"(?<day>[^\"]+)\""
 | rex field=detail.Data "eveningPhoneNumber\"\:\"(?<evening>[^\"]+)\""
 | fillnull value="" day evening
 | eval telno=day + ";" + evening
 | makemv delim=";" telno
 | mvexpand telno
 | search telno="07*"
 | rex mode=sed field=telno "s/ //g"]
 | dedup telno 
 |append [inputlookup telno.csv|eval telno1=telno]
 |stats values(*) as * by telno
 |eval Match=if(isnotnull(telno1),"Y",null())
0 Karma

IRHM73
Motivator

Hi @cmerriman, thank you for coming back to me with this, but unfortunately it didn't work because it failed to create a filed called "Match", but I have managed to resolve this wuth:

| lookup tellist.csv telno OUTPUT telno as Match
| eval Match=if(isnotnull(Match), "Y", "N") 
| table telno Match

Many thanks and kind regards

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...