How to run searches stored in lookup file?

[Taruchit]

Hello All,

I have a lookup file which stores a set of SPLs and it periodically gets refreshed.

How to build a search query such that it iteratively executes each SPL from the lookup file?

Any suggestions and ideas will be very helpful.

Thank you

Taruchit

[ITWhisperer]

My suggestion would be don't do it!

Questions to consider:

Would these SPL searches all be run as part of one search? - Consider using the map command

Would these SPL searches require separate report outputs or dashboard panels?

How would you expect it to behave if there was an error in one of the SPL searches?

Would there be a fixed / known number of entries in the lookup up file?

Are the SPL entries complete searches or parts to be inserted into a larger search?

[Taruchit]

Hello @ITWhisperer,

Thank you for your response and for sharing your inputs and questions.

I will try to describe the original task and hopefully in the process will be able to answer your questions.

I have a lookup file with two fields: fieldA, fieldB. The lookup file has distinct pairs of fieldA and fieldB.

I have an index and I need to run predict command to calculate event count for each pair of fieldA and fieldB listed in lookup file to forecast the value of last day by taking historical data of last 1 month span by a day. As the result, the query I used to compute the timeseries data for giving input to predict command is a timechart command.

I have to store the resulting forecasted value in the lookup for respective pairs of fieldA and fieldB. Then use the lookup for reading the its data in the dashboard and also display the actual value of event count for each distinct pair of fieldA and fieldB for comparison. 

The approach I tried so far: -

I tried to read the data from lookup file for each row, build the SPL for each row that uses timechart command to fetch timeseries data, then use it in predict command to compute the forecasted value; finally stored the SPL for each lookup in the third column of the lookup. The approach was to first build the required SPL for each row of unique pairs and then iteratively run the SPL from the lookup to compute the required values. However, I got stuck in reading the SPLs and storing their values back in the lookup.

I will now try to answer your specific questions: -

Ideally, I would want to execute these searches in one main search or iteratively get executed in the main search.

With regards to error in one of the SPL searches, the error is not anticipated because the search conditions and SPL keywords are same, only the values change based on the row that is currently being accessed in the lookup file.

The lookup file more or less may have fixed number of rows but it will periodically get updated by a different search that refreshes its data.

The SPL searches are complete search which can be executed independently on the search head to compute the predicted values.

Please share if you need any more details from my end for seeking your inputs.

Thank you

[Taruchit]

Hello @ITWhisperer,

I had used the below SPL for reading the values from lookup file and storing their corresponding SPL back in the lookup file which I wanted to use for computing the respective forecasted values.

|inputlookup table1.csv
|table fieldA, fieldB
|eval key="index=custom_index earliest=-4w@w latest-2d@d
orig_fieldA=".fieldA." orig_fieldB=".fieldB." 
|timechart span=1d avg(event_count) AS avg_event_count
|predict avg_event_count future_timespan=1
|tail 1
|fields prediction(avg_event_count)"
|outputlookup table1.csv

Shared the above SPL for your reference incase you could suggest any improvement in the approach for achieving the end result.

Thanking you in anticipation.