topic Re: Lookup instead of join in Splunk Search

Lookup instead of join

Janani_Krish — Tue, 02 Mar 2021 10:44:37 GMT

Currently I am using below query to run my search to get the common event in tc and email,

|inputlookup tc | search type=emailaddress| rename indicator as SenderAddress |dedup SenderAddress | table SenderAddress| union [ search sourcetype = "email"| dedup SenderAddress| table SenderAddress ] | stats count by SenderAddress | where count > 1 | table SenderAddress

But since my search is heavy, I am getting the below status in job,
No results found. Try expanding the time range.
No matching fields exist.
[subsearch]: The search auto-finalized after it reached its time limit: 30 seconds.

Hence I am directed to use lookup command after googling, and I tried the following query but it is not working,
sourcetype = "email" |rename SenderAddress as indicator |dedup indicator
|lookup tci indicator |stats count by indicator

Please suggest.

Re: Lookup instead of join

ITWhisperer — Tue, 02 Mar 2021 11:26:53 GMT

In what way is it not working? Given that you dedup indicator, won't your counts always be 1?

Re: Lookup instead of join

gcusello — Tue, 02 Mar 2021 11:30:19 GMT

Hi @Janani_Krish,

what's you need: filter events from the index matching the lookup or other?

Anyway, if you need to filter your data matching the lookup you can use something like this:

One additional hint: use always the index in the main search to have quicker searches!

Ciao.

Giuseppe

Re: Lookup instead of join

Janani_Krish — Wed, 03 Mar 2021 08:06:25 GMT

Hi @gcusello

I am getting the below issue when I am querying like that,
[subsearch]: Subsearch produced 4815473 results, truncating to maxout 10000.

Hence I tried to use lookup. Is there any other way to overcome this.

Re: Lookup instead of join

gcusello — Wed, 03 Mar 2021 08:11:59 GMT

Hi @Janani_Krish,

I didn't understand that you lookup is so huge!

Anyway, tryto filter it:

Ciao.

Giuseppe

Re: Lookup instead of join

Janani_Krish — Wed, 03 Mar 2021 08:13:44 GMT

Hi @ITWhisperer ,

Not working in the sense I am not getting the desired result. My intention is to do inner join and get the common indicator set between sourcetype=email and inputlookup=tc. But If I am using sub search in my query it is either truncating the result or getting timed out.

I just tested with dedup and without dedup to setup any logic.

Re: Lookup instead of join

Janani_Krish — Wed, 03 Mar 2021 08:27:47 GMT

Hello @gcusello

I am getting timeout error If I filter with Address like below,
[subsearch]: Subsearch produced 1924551 results, truncating to maxout 10000.

It is still huge.

Re: Lookup instead of join

gcusello — Wed, 03 Mar 2021 14:20:22 GMT

Hi @Janani_Krish,

sorry, there'a an error of mine!

please try this:

Ciao.

Giuseppe

Re: Lookup instead of join

Janani_Krish — Thu, 04 Mar 2021 04:57:54 GMT

Hi @gcusello

I tried the below queries,
Query 1:
sourcetype = "email" [ | inputlookup tci WHERE type=emailaddress | rename indicator AS SenderAddress | dedup SenderAddress | fields SenderAddress ]
| stats count by SenderAddress

For which I am getting the job status as,
[subsearch]: Subsearch produced 4815473 results, truncating to maxout 10000.

I hope so because of having subsearch and hence tried,
Query 2: sourcetype="email" | rename SenderAddress as indicator |lookup tci indicator output type|where isnotnull(type)|dedup indicator|table indicator FromIP dest action RecipientAddress Subject Received

I am getting the desired result for the second query. But the only thing is I am not able to filter tci lookup. For example If I want to lookup tci which has tag!="sample", I tried something like below but is not working,

sourcetype="email" | rename SenderAddress as indicator |lookup tci indicator output type|where isnotnull(type)|where tag!="sample"|dedup indicator|table indicator FromIP dest action RecipientAddress Subject Received

Re: Lookup instead of join

gcusello — Thu, 04 Mar 2021 07:55:33 GMT

Hi @Janani_Krish,

if the first search doesn't run this means that also filtering your lookup for type=emailaddress you have too many results.

Anyway, why do you say that the second one deosn't run? what's the error?

you could tryto invert the two where conditions

| where tag!="sample" | where isnotnull(type)

or put them in one condition using AND (in this case you have to use it explicitly)

| where isnotnull(type)AND tag!="sample"

Ciao.

Giuseppe

Re: Lookup instead of join

Janani_Krish — Fri, 05 Mar 2021 04:23:11 GMT

Hi @gcusello ,

I dont know what is the error there I cant even mention the name tag.name in output field. It is returning error,

Query:
sourcetype="email*" | rename SenderAddress as indicator |lookup tci indicator output type,"tag.name" as tag|where isnotnull(type)|dedup indicator|table indicator tag

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

But I am able to filter tag.name field in tci lookup as below,
|inputlookup tci|search tag.name="*sample"

Re: Lookup instead of join

gcusello — Fri, 05 Mar 2021 07:30:02 GMT

Hi @Janani_Krish,

did you tried to manually rename your lookup field to see if this is the problem?

I'm not sure that you can have a dot inside a field name in lookup.

Ciao.

Giuseppe

Re: Lookup instead of join

Janani_Krish — Fri, 05 Mar 2021 08:02:16 GMT

Hi @gcusello

I can rename my field like below,
|inputlookup tci|search indicator="*"|rename tag.name as tag|table indicator tag

Also I looked into definition of tci lookup where I could see in supported field column it is, "tag".
So I ran the query as below,
sourcetype="ms:o365*" | rename SenderAddress as indicator |lookup tci indicator output type,rating,tag|where isnotnull(type)|dedup indicator|table indicator tag

Now I am not getting any error, but my tag column is empty.

Then I tried running the below query without renaming tag.name field since tag field was supported in earlier query,
|inputlookup tci|search indicator="*"|table indicator tag
Here it says, I can rename my field like below,
|inputlookup tci|search indicator="*"|rename tag.name as tag|table indicator tag

Also I looked into definition of tci lookup where I could see in supported field column it is, "tag".
So I ran the query as below,
sourcetype="ms:o365*" | rename SenderAddress as indicator |lookup tci indicator output type,rating,tag|where isnotnull(type)|dedup indicator|table indicator tag

Now I am not getting any error, but my tag column is empty.

Then I tried running the below query without renaming tag.name field sice tag field was supported in earlier query,
|inputlookup tci|search indicator="*"|table indicator tag
Here it says, No field match the criteria.I can rename my field like below,
|inputlookup tci|search indicator="*"|rename tag.name as tag|table indicator tag

Also I looked into definition of tci lookup where I could see in supported field column it is, "tag".
So I ran the query as below,
sourcetype="ms:o365*" | rename SenderAddress as indicator |lookup tci indicator output type,rating,tag|where isnotnull(type)|dedup indicator|table indicator tag

Now I am not getting any error, but my tag column is empty.

Then I tried running the below query without renaming tag.name field sice tag field was supported in earlier query,
|inputlookup tci|search indicator="*"|table indicator tag
Here it says, No matching fields exist.

Re: Lookup instead of join

Janani_Krish — Fri, 05 Mar 2021 08:07:06 GMT

Hi @gcusello

Sorry for my previous post. It had repetitions,

I can rename my field like below,
|inputlookup tci|search indicator="*"|rename tag.name as tag|table indicator tag

Also I looked into definition of tci lookup where I could see in supported field column it is, "tag".
So I ran the query as below,
sourcetype="ms:o365*" | rename SenderAddress as indicator |lookup tci indicator output type,rating,tag|where isnotnull(type)|dedup indicator|table indicator tag

Now I am not getting any error, but my tag column is empty.

Then I tried running the below query without renaming tag.name field since tag field was supported in earlier query,
|inputlookup tci|search indicator="*"|table indicator tag

Here it says, No matching fields exist.

Re: Lookup instead of join

gcusello — Fri, 05 Mar 2021 08:11:26 GMT

Hi @Janani_Krish,

only for test:

manually change the field name from tag.name into tag
reload the data in the lookup
rerun the search.

Ciao.

Giuseppe

Re: Lookup instead of join

Janani_Krish — Fri, 05 Mar 2021 08:38:18 GMT

Hi @gcusello

The field itself is mentioned only "tag" in definition. PFB the image,

But when I run |inputlookup tci|search indicator="*" the results are like below with tag.name field,

My cases is same as described in post,
https://community.splunk.com/t5/All-Apps-and-Add-ons/Lookup-command-doesn-t-support-dot-notation-in-field-name/m-p/500643

Also I tried,
sourcetype="ms:o365*" | rename SenderAddress as indicator |lookup tci indicator output type,rating,tag as tag.name|where isnotnull(type)|dedup indicator|table indicator tag.name

Still getting empty field.

Is there any other way where I can rename my field manually except getting into lookup definitions.

Re: Lookup instead of join

gcusello — Fri, 05 Mar 2021 09:04:22 GMT

I @Janani_Krish,

have you the field name "tag" in both the lookup kv Store (file collections.conf) and the lookup definition (file transforms.conf)?

Ciao.

Giuseppe

Re: Lookup instead of join

Janani_Krish — Mon, 03 May 2021 12:37:23 GMT

HI @gcusello

Finally they have renamed the field in config files also and now it is working. Thanks.