topic Re: How to edit my scheduled outputlookup search to NOT overwrite the existing lookup table if the new results are blank? in Reporting

How to edit my scheduled outputlookup search to NOT overwrite the existing lookup table if the new results are blank?

ishugupta — Fri, 20 Mar 2015 07:50:23 GMT

I completely refresh my outputlookup everyday using the search below. I have scheduled this outputlookup as cron job.

index = ind sourcetype= todaydata earliest = -1d@d latest = now | my search ...|table one two |outputlookup look.csv

But in case the data is blank for some reason in last one day, the search currently updates the table results to zero.

I do not want to append the data. I want to refresh it everyday (deleting the previous one and inserting the new one), but I want to put in a condition while inserting that if the number of today's results is zero, DO NOT overwrite the already existing table.

Re: How to edit my scheduled outputlookup search to NOT overwrite the existing lookup table if the new results are blank?

somesoni2 — Fri, 20 Mar 2015 19:44:26 GMT

I am assuming if your base search (index = ind sourcetype= todaydata earliest = -1d@d latest = now) is NOT showing any data, you want your lookup to remain same as yesterday's version and overwrite if there are any data available, then try this

index = ind sourcetype= todaydata earliest = -1d@d latest = now | head 1 | map search="search index = ind sourcetype= todaydata earliest = -1d@d latest = now | my search ...|table one two |outputlookup look.csv"

It will only execute the search mentioned in map command if earliest portion of the query returning 1 row and will skip the execution otherwise.

Re: How to edit my scheduled outputlookup search to NOT overwrite the existing lookup table if the new results are blank?

ishugupta — Fri, 20 Mar 2015 21:04:20 GMT

Thanks Somesoni , It is not working for me . The command within map search=" search ..." is providing me zero results .

Whenver I table the results in the internal query , they provide me zero results.

Re: How to edit my scheduled outputlookup search to NOT overwrite the existing lookup table if the new results are blank?

mbenwell — Mon, 28 Sep 2020 19:43:13 GMT

UPDATE: Splunk 7.1 has added a new outputlookup parameter "override_if_empty=". Set it to "false" to keep the lookup file if search results are empty. Note the default is 'true', the the behaviour is the same as before unless you tell it otherwise.

Pre-7.1 Method:
Here's an idea to try and deal with this issue:

Get the new data and apply a simple identifying field ('new' in this example) then append the existing lookup data with a different identifying field ('old' in the below example)

Use eventstats across all of the results to count the results for each identifying field.

Finally use a where clause to drop the old data if new data exists or keep the old data if there are no new results.

<search for new data>...  
| eval new=1  
| append [|inputlookup <lookup>  
| eval old=1]  
| eventstats count(old) as existing_count count(new) AS update_count  
| where (update_count>0 AND new=1) OR (isnull(update_count) AND old=1)  
| fields - update_count existing_count old new  
| outputlookup <lookup>

Re: How to edit my scheduled outputlookup search to NOT overwrite the existing lookup table if the new results are blank?

dominiquevocat — Thu, 16 Jul 2015 10:28:16 GMT

makes sense, works fine.

Re: How to edit my scheduled outputlookup search to NOT overwrite the existing lookup table if the new results are blank?

ppablo — Tue, 18 Aug 2015 19:15:07 GMT

Hi @ishugupta

Just wanted to follow up with this post, but did @mbenwell's answer below solve your question by chance?

Re: How to edit my scheduled outputlookup search to NOT overwrite the existing lookup table if the new results are blank?

judevine — Thu, 05 Jul 2018 07:06:09 GMT

Thanks mbenwell. You have my vote.
Your solution works well even in 2018 (as we still use Splunk 6.x).

Re: How to edit my scheduled outputlookup search to NOT overwrite the existing lookup table if the new results are blank?

judevine — Thu, 05 Jul 2018 07:06:53 GMT

Thanks mbenwell. You have my vote.
Your solution works well even in 2018 (as we still use Splunk 6.x).

Re: How to edit my scheduled outputlookup search to NOT overwrite the existing lookup table if the new results are blank

inventsekar — Thu, 08 Oct 2020 01:47:08 GMT

i was searching for the similar issue and after reading this solution i assumed the current Splunk version also works similar to this solution. Then i was reading the docs and learnt the new options available, so i thought to update other readers who may face similar situation like myself.

Until Splunk 6.5, the scheduled reports never had "Write to a CSV lookup file" option. Ref:

https://docs.splunk.com/Documentation/Splunk/6.5.0/Report/Schedulereports

From Splunk 6.6, the scheduled reports are having a "Write to a CSV lookup file" option. Ref:

https://docs.splunk.com/Documentation/Splunk/6.6.0/Report/Schedulereports#Define_a_Write_to_a_CSV_Lookup_File_action

so, if we are using Splunk 6.6 or newer versions, then, simply with scheduled reports, we can "append" the lookup files. thanks.

(PS - i have given around 500+ karma points so far, received badge for that, if an answer helped you, a karma point would be nice!. we all should start "Learn, Give Back, Have Fun")