Splunk Search

Lookup instead of join

Janani_Krish
Path Finder

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.

Labels (1)
Tags (1)
0 Karma
1 Solution

gcusello
Legend

@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

View solution in original post

gcusello
Legend

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

0 Karma

gcusello
Legend

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 

0 Karma

Janani_Krish
Path Finder

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.

0 Karma

gcusello
Legend

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

0 Karma

Janani_Krish
Path Finder

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

0 Karma

gcusello
Legend

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

0 Karma

Janani_Krish
Path Finder

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"


0 Karma

gcusello
Legend

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

0 Karma

Janani_Krish
Path Finder

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.

0 Karma

gcusello
Legend

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

0 Karma

Janani_Krish
Path Finder

Hi @gcusello 

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

Janani_Krish_0-1614933101774.png

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

Janani_Krish_1-1614933339057.png

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.

0 Karma

gcusello
Legend

@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
Path Finder

HI @gcusello 

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

0 Karma

Janani_Krish
Path Finder

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.

0 Karma

Janani_Krish
Path Finder

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

Janani_Krish
Path Finder

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.

0 Karma
Get Updates on the Splunk Community!

Welcome to the Future of Data Search & Exploration

You have more data coming at you than ever before. Over the next five years, the total amount of digital data ...

What’s new on Splunk Lantern in August

This month’s Splunk Lantern update gives you the low-down on all of the articles we’ve published over the past ...

This Week's Community Digest - Splunk Community Happenings [8.3.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...