My three searches with outputcsv are working when run manually, but why are results not written to CSV as scheduled searches?

[aputz]

We are looking to perform heavy calculations within Splunk from multiple sources. Currently the formula we are working with is something like: result = ( constant_value * ( avg_1 + avg_2 + 1 ) ) where there will be multiple results. I have found the limitation of eval only accepting one field within an algebraic request so I've broken this endeavor into multiple searches. The issue that I am running into is that while I can manually run my tiers of searches and get the results I expect, it does not work when I attempt to automate the searches.

First I perform this search:

index=first_index | dedup 1 finding IP | stats avg(first_number_average) as avg by IP | outputcsv first_average.csv

This ensures that I only return the most recent finding for each IP and I generate a table of averages broken out by IP and write to a .csv file (which is monitored to be ingested into an index).

Second I perform the search:

index=second_index | dedup 1 different_finding_type IP | stats avg(second_number_average) as avg by IP | outputcsv second_average.csv

This search looks at another set of data and returns the most recent different_finding_type for each IP and then generates another table of averages by IP and gets the same treatment of output to CSV and re-indexed.

Finally I schedule a search to spit out the result with the search:

index=csv_monitoring_index IP=w.x.y.z | stats sum(avg) as avgpt1 by IP Address | eval result=(0.2*(avgpt1+1))  | table IP result | outputcsv result_IP.csv

I have found performing these searches manually results in correctly outputting CSV files and indexing the results for the following step to utilize. I find that when placed into staggered scheduled searches the .csv files are not being written to, despite the searches resulting in findings when input manually. I saw that the final search can not be summary indexed due to the eval that occurs after the stats command.

That is the setup and the wall I seem to have run up against is a limitation on being able to pipe to outputcsv for a scheduled saved search when the commands produce the intended results when performed manually.

Any help is appreciated, or insight into possible ways of performing calculations a little more involved than what the current eval capability provides.

[sjohnson_splunk]

Since you are specifying the file name and not the lookup name (defined by transforms.conf) are you sure that all the saved searches are running in the same app context? If not they will output the lookup files to different app/lookup directories.

If you define the table name and make it global it won't matter what app context the searches run under as they will update the correct lookup file when you outputlookup

[aputz]

While this wasn't an issue for the initial query this did help me to double-check and correct getting things up and running after an environment migration so thank you!

[somesoni2]

Here is how I'll do the same.

1) Run the first search and save the result directly to csv_monitoring_index using summary indexing. Benefit- no additional license usage OR monitoring to be setup.
2) Do the same with second search. Now These two searches looks independent so you can have them schedule to run at the same time.
3) Run the third search slightly after first two searches are completed.

[hsesterhenn_spl · Accepted solution]

Hi,

Why don't you use "| inputlookup result_ip.csv" as a forth scheduled search and do summarize on this?

Another option to merge the searches might be using a "subsearch" and maybe streamstats/eventstats...

HTH,

Holger

[aputz]

Sorry for the delay in response, some environment migrations occurred and I was not able to investigate some of the suggestions immediately. Thank you very much for the responses, everyone.

I received a fantastic suggestion with an implementation of "append" from Splunk support so that the query is able to be accomplished in a single command:

index=first_index | dedup 1 finding IP | stats avg(first_number_average) as avg by IP | append [search index=second_index | dedup 1 different_finding_type IP | stats avg(second_number_average) as avg by IP ] | fillnull value=0 | stats sum(avg) as result by IP | eval var1= result+1) | append [search index=main | dedup 1 IP lookup_value | stats avg(lookup_value) as var1 by IP] | stats avg(var1) by IP

Having the solution utilize many searches was just a result of not knowing how to accomplish it in one-go.
I am now having difficulty with the last segment attempting multiply the result with a constant that is dependent on the host-IP. While I have confirmed the search within the final append to produce a "lookup_value" for each IP is returning the desired results I do not believe it is successfully utilizing the value with the final calculation. Instead of the average between the first var1 (value pre-append search) and the second var1 (from the append search) I am instead getting half of the initial var1 value.
I expected to have to double the result (I wanted to multiply the two variables together, an average of the two values being half of what I wanted) but for the value to come back as merely half of the first value means I don't think the second value is getting returned from the last append search.