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

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

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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...