How to take static lookup entries to run searches for each row?

[gnangia]

I have a lookup file with 2 columns. I would like to take each row and then run a search query and show results including searches that don't have any results for that particular row. For starters, I have saved the lookup file as a saved report and have attempted to do a join but it only shows if any results are found. Here is my search query -

|inputlookup lookupFile.csv | join Application [search sourcetype=xxxx]

Any help would be appreciated.

Thanks

[DalJeanis]

You are going to need a join with type=left, to retain the unmatched items in the left query (in this case, the csv) that are not present in the right query (the search). You will also need (probably) max=0 to retain all individual results from the subquery, based on how it's set up now.

I'm not sure that's going to get you your desired results, though. Take a look at the map command instead.

[gnangia]

Thank you for your response. While using type=left I see all the rows even the ones with no count. However the values are empty. So what I did was to add | fillnull value=0 Applicationwhich does show me all the rows with the # 0 in lieu of blank which is good. However when I click on the number when greater than zero, it does not show me the event.

I then tried your map command as an alternative and it gives me the raw events for only things it can find and not based on the input file. Here is my query - | inputlookup iss_apps.csv | map search="search sourcetype=netcool_QA_app_logs AND Application=$Application$" maxsearches=200. Any additional advice?

Thanks

[DalJeanis]

Sorry, gnangia, I didn't notice your question.

What is the exact information that you are looking for? The map search you are using is going to return the individual events, and is quickly going to hit the 50K limit for events to return.

If you are looking for a summary report, then you should sum up the results for each application within the search. If you are looking for details, then maybe it would be wise to put a time limit within the search itself (for example earliest=-1d@d latest=-0d@d to get the prior day's results.)