Matching a search (with spath) and Inputlookup

[knadav]

Hi Guys,

I'm trying to match a result from one search to an Inputlookup.

The original search contains "spath" command because the source sends the logs in JSON format.

Here is the first search:

index="MyIndex" some search filters | spath "EmailAddr" | table "EmailAddr"

Here is the second search:

[| inputlookup all_identities.csv | fields email ]

The end goal is to take the "EmailAddr" from the first search and match it with the field "email" from the second search so only email addresses that are in the inputlookup will return from the search. 

The email address needs to be in both the search and the inputlookup.

I've tried to use the | eval email = spath(_raw,"email") command to place the "email" value in the eval field but that did not do the job.

I would really appreciate the community help on this.

Thanks! 

[knadav]

Hi inventsekar,

When trying to add the "EmailAddr" to the lookup command - I'm receiving the following error:

"Error in 'lookup' command: Could not find all of the specified lookup fields in the lookup table."

This is most likely because the field "EmailAddr" is not in the lookup but only in the base search.

How can I proceed? 

Appreciate your assistance! 

[inventsekar]

1. may we know your all_identities.csv field names please.

2. and what happens when you run this:

| makeresults | eval EmailAddr="UseMailidthatExistinUrinputlookup" 
| lookup all_identities.csv email as EmailAddr OUTPUT userid as UserName

3. i think the spath command needs some editing. pls check this once:

https://docs.splunk.com/Documentation/SplunkCloud/8.0.2007/SearchReference/spath#Basic_examples

PS - Karma points appreciated!

[knadav]

Hi inventskear, 

Unfortunately cannot share all the field names but i'm making the proper adjustments as we go 🙂 

When running the command you provided, i'm getting good results with the proper fields! 

When trying to add the two searches together i'm receiving the following alert: 

"Streamed search execute failed because: Error in 'lookup' command: Could not construct lookup 'all_identities.csv, email, as, EmailAddr, OUTPUT, identity, as, UserName'."

To work with the spath field I've used the following command:

| eval EmailAddr = spath(_raw,"EmailAddr")

How should I proceed? 

Thanks in advance!

[inventsekar]

The spath and eval looks not correct to me. Without the lookup, if you run the first part alone(spath and then add a "table EmailAddr" ) .. and see if it works.

As per understanding, spath should be...

| spath output=EmailAddr path=path.to.EmailAddr.inxml

From the error msg, it seems you added more comma. 

The lookup part alone:

"| lookup all_identities.csv email AS EmailAddr OUTPUT identity AS UserName"

[inventsekar]

Hi @knadav let us know if that spath issue and lookup are solved. let us know your final command, so it will be helpful to the new readers. if issue resolved, please accept it as solution. thanks. 

[knadav]

Hi inventsekar,

Can I use a regular lookup instead of using inputlookup?

I've tried to do the query you provided and had no success.

What should be the required-field and required-field-values values you wrote?

Thanks! 

[inventsekar · Accepted solution]

Hi @knadav 

Can I use a regular lookup instead of using inputlookup? /// Yes, the inputlookup is to "view" the contents of a lookup file. The regular "lookup" is to invoke field value lookups, which is exactly your use-case.

What should be the required-field and required-field-values values you wrote? // lets understand from the splunk documentation..

1. Lookup users and return the corresponding group the user belongs to

Suppose you have a lookup table specified in a stanza named usertogroup in the transforms.conf file. This lookup table contains (at least) two fields, user and group. Your events contain a field called local_user. For each event, the following search checks to see if the value in the field local_user has a corresponding value in the user field in the lookup table. For any entries that match, the value of the group field in the lookup table is written to the field user_group in the event.

... | lookup usertogroup user as local_user OUTPUT group as user_group

let me assume that, your lookup all_identities.csv got two fields: userid and email. so, now from the first search you get email id as EmailAddr, you will match it with your inputlookup csv file and then by using OUTPUT (or OUTPUTNEW), you will list down the userid as UserName. hope its clear now.

index="MyIndex" some search filters | spath "EmailAddr" | table EmailAddr 
| lookup all_identities.csv email as EmailAddr OUTPUT userid as UserName

[knadav]

After a few adjustments - This worked like a charm!

Appreciate it  

[inventsekar]

Hi @knadav all you need is the "lookup" command (please edit it as per your field names and values)

index="MyIndex" some search filters | spath "EmailAddr" | table EmailAddr 
| lookup all_identities.csv email EmailAddr OUTPUT required-field as required-field-values 

lookup command reference:

https://docs.splunk.com/Documentation/Splunk/8.0.6/SearchReference/Lookup