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