Splunk Search

Is there a way to read values from more than one lookup table at the same time?

email2vamsi
Explorer

I want to display the result in a graph based on the results of the following two join searches.
I can store these values in two lookup tables temporarily.
Is there a way to read values from more than one lookup table at the same time?
OR any other option in this situation?
I may have to add more searches like these in future.

index="index1" sourcetype="production-response" | eval running_ok = if(response_status="Reponse test success","2","0") |sort 0 - _time| join running_ok
[search index="index1" sourcetype="production-monitor" | eval running_ok = if(monitor_status="Monitor running","2","0")|sort 0 - _time ] | stats count(eval(running_ok="0")) AS result  |  eval redCount = if(result >2,result,0)| eval greenCount = if(result <=2, result, 0)

index="index2" sourcetype="sql-production-response"| eval running_ok = if(sql_monitor="sql process running","2","0") |sort 0 - _time| join running_ok
 [search index="index2" sourcetype="sql-production-monitor" | eval running_ok = if(sql_response="sql process running","2","0")  |sort 0 - _time ] | stats count(eval(running_ok="0")) AS result  |  eval redCount = if(result >2,result,0)| eval greenCount = if(result <=2, result, 0)
0 Karma

aaraneta_splunk
Splunk Employee
Splunk Employee

@email2vamsi - Did one of the answers below help provide a solution your question? If yes, please click “Accept” below the best answer to resolve this post. If no, please leave a comment with more feedback. Thanks.

0 Karma

woodcock
Esteemed Legend

As others have mentioned, you need to avoid using subsearches which will cut off files after (at most) 50.5K lines (events). There is a hackish way to exceed this limit, which is to exploit a side-effect of the appendpipe command (which does not use subsearches) like this (doing a full join using the values from the myfield field:

| inputlookup lookup1.csv
| appendpipe [ | inputlookup lookup2.csv ]
| appendpipe [ | inputlookup lookup3.csv ]
| appendpipe [ | inputlookup lookup4.csv ]
| appendpipe [ | inputlookup lookup5.csv ]
| stats values(*) AS * BY myfield

sundareshr
Legend

In general, you should try and avoid 'sub-searches (join, append, appendcols etc) because they are not the best at performance and more importantly have limitations (10-50K events only). To work around you may consider storing in temp lookup tables, but that is not a good idea either. You best option would be to

1) Get rid of sub-searches to improve performance of your searches. Here is a good post by @MuS on ways you can achieve that.
https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo...

2) Use accelerated data models with tstats http://docs.splunk.com/Documentation/Splunk/6.5.0/Knowledge/Acceleratedatamodels

Either approach will vastly improve performance without any limitations introduced by sub-searches.

http://docs.splunk.com/Documentation/Splunk/6.5.1/Search/Aboutsubsearches

0 Karma

email2vamsi
Explorer

Thank you.

|eval final = if(running_ok==" " OR running_ok==1,1,0)

I want to assign final=1 when (running_ok=="No results found." OR running_ok==1).

running_ok==1 works fine. But it does not work when running_ok=="No results found."
Please let me know what needs to be used on the RHS when search returns no values.
0 Karma

gcusello
Esteemed Legend

Hi email2vamsi,
if you want to read two lookups one after one, you can try

| inputlookup lookup1.csv | append [ | inputlookup coolup2.csv ]

If you want to join them using a common field

| inputlookup lookup1.csv | join myfield [ | inputlookup coolup2.csv ]

Bye.
Giuseppe

0 Karma

email2vamsi
Explorer

Thank you.
As shown below,can this lookup be done for more than two? If i want to use five lookups.

| inputlookup lookup1.csv | append [ | inputlookup loolup2.csv ]|append [ | inputlookup loolup3.csv ]

| inputlookup lookup1.csv | join myfield [ | inputlookup loolup2.csv ] | join myfield [ | inputlookup loolup3.csv ]
0 Karma

gcusello
Esteemed Legend

Why not?
I used three lookups in append to a search.
So in your first example you can append as many lookups as you want.

About the second one, I ask you sorry but there is a simpler way to join two (or more) lookups: you don't need to use join (that is also slower!) you can do something like this:

 | inputlookup lookup1.csv | lookup lookup2.csv  myfield OUTPUT myfield1 myfield2 ... | lookup lookup3.csv  myfield OUTPUT myfield4 myfield5 ... | ....

Bye.
Giuseppe

0 Karma
Get Updates on the Splunk Community!

Observability Highlights | November 2022 Newsletter

 November 2022Observability CloudEnd Of Support Extension for SignalFx Smart AgentSplunk is extending the End ...

Avoid Certificate Expiry Issues in Splunk Enterprise with Certificate Assist

This blog post is part 2 of 4 of a series on Splunk Assist. Click the links below to see the other ...

Using Machine Learning for Hunting Security Threats

REGISTER NOW Seeing the exponential hike in global cyber threat spectrum, organizations are now striving more ...