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

[email2vamsi]

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)

[aaraneta_splunk]

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

[woodcock]

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]

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

[email2vamsi]

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.

[gcusello]

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

[email2vamsi]

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 ]

[gcusello]

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