Lookup instead of join

[Janani_Krish]

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.

[gcusello]

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:

sourcetype = "email" [ | inputlookup tci | rename indicator AS SenderAddress | fields SenderAddress ]
| dedup SenderAddress 
| stats count by SenderAddress

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

Ciao.

Giuseppe

[gcusello]

Hi @Janani_Krish,

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

Anyway, tryto filter it:

sourcetype = "email" [ | inputlookup WHERE tci type=emailaddress | rename indicator AS SenderAddress | dedup SenderAddress | fields SenderAddress ]
| stats count by SenderAddress

Ciao.

Giuseppe 

[Janani_Krish]

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.

[gcusello]

Hi @Janani_Krish,

sorry, there'a an error of mine!

please try this:

sourcetype = "email" [ | inputlookup tci WHERE type=emailaddress | rename indicator AS SenderAddress | dedup SenderAddress | fields SenderAddress ]
| stats count by SenderAddress

Ciao.

Giuseppe

[Janani_Krish]

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

[gcusello]

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

[Janani_Krish]

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"

[gcusello]

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

[Janani_Krish]

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.

[gcusello]

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

[Janani_Krish]

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-...

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.

[gcusello · Accepted solution]

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

[Janani_Krish]

HI @gcusello 

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

[Janani_Krish]

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.

[Janani_Krish]

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.

[ITWhisperer]

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

[Janani_Krish]

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.