topic Re: Display Matches Between Query and Lookup File in Splunk Search

Display Matches Between Query and Lookup File

IRHM73 — Thu, 06 Apr 2017 07:13:50 GMT

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

Re: Display Matches Between Query and Lookup File

cmerriman — Thu, 06 Apr 2017 14:39:52 GMT

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())

Re: Display Matches Between Query and Lookup File

IRHM73 — Fri, 07 Apr 2017 06:00:08 GMT

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

Re: Display Matches Between Query and Lookup File

IRHM73 — Fri, 07 Apr 2017 06:00:38 GMT

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