How to apply the predict function for the most varying field?

[VatsalJagani]

I'm trying to do something like from my output I just need to apply predict function on most varying field. For example

index=_internal sourcetype=splunkd* | timechart count as Count by sourcetype | predict splunkd_access

I can use above query if I've to write query manually as I know splunkd_access is most varying field, but in my case I've to find most varying field by query (We can do this by using stdev command with eventstats). And then for field with highest stdev I need to apply predict function, I also want to remove other fields like splunkd, splunkd_ui_access, so it won't show up in the chart.

Two ways that I know to solve this problem is custom command and java script. But if possible I want to solve it query only.

[kamlesh_vaghela · Accepted solution]

Hi @VatsalJagani,

Can you please try following search?

index=_internal sourcetype=splunkd* 
| timechart useother=f count as Count by sourcetype where stdev in top1 
| foreach "*" 
    [ eval column_value='<<FIELD>>', column_name="<<FIELD>>" ] 
| table _time column_value column_name 
| predict column_value 
| eval {column_name}=column_value, "prediction({column_name})"='prediction(column_value)',
"lower95(prediction({column_name}))"='lower95(prediction(column_value))',
"upper95(prediction({column_name}))"='upper95(prediction(column_value))' 
| fields - column_value column_name "prediction(column_value)" 
"lower95(prediction(column_value))" "upper95(prediction(column_value))" "upper95(prediction())" "lower95(prediction())" "prediction()"

Here, to find most varying fields, I have used stdev- as you mentioned in question- in where clause after timechart. This will give 2 columns: _time and splunkd - it would be as per the events.

 | timechart useother=f count as Count by sourcetype where stdev in top1

Another challenge is to pass column name in predict command. For that I h ave used:

 | foreach "*"  [ eval column_value='<<FIELD>>', column_name="<<FIELD>>" ]

this will give me column_name- which will hold original column name- and column_value-which will hold value of that column.

Now we can use the predict command.

| predict column_value 

As we need only few columns with proper field names. I have evaled new columns:

| eval {column_name}=column_value, "prediction({column_name})"='prediction(column_value)',
 "lower95(prediction({column_name}))"='lower95(prediction(column_value))',
 "upper95(prediction({column_name}))"='upper95(prediction(column_value))'

and removing extra columns:

| fields - column_value column_name "prediction(column_value)" 
 "lower95(prediction(column_value))" "upper95(prediction(column_value))" "upper95(prediction())" "lower95(prediction())" "prediction()"

Please implement it and do required changes in search as per your requirement.

Let me know if any issue,

Thanks
Kamlesh

Happy Splunking

[VatsalJagani]

Hi @kamlesh_vaghela,
I work around your solution, this is amazing. I've also added something to make look it actually like predict function. See query below.

index=_internal sourcetype=splunkd* 
| timechart span=1m useother=f count as Count by sourcetype where stdev in top1 
| foreach "*" 
    [ eval column_value='<<FIELD>>', column_name="<<FIELD>>" ] 
| predict column_value future_timespan=14 
| filldown column_name 
| eval {column_name}=column_value, "prediction({column_name})"='prediction(column_value)',
    "lower95(prediction({column_name}))"='lower95(prediction(column_value))',
    "upper95(prediction({column_name}))"='upper95(prediction(column_value))' 
| eval _lower = "lower95(prediction(".column_name."))", _upper = "upper95(prediction(".column_name."))", _predicted = "prediction(".column_name.")" 
| fields - column_value column_name "prediction(column_value)" 
    "lower95(prediction(column_value))" "upper95(prediction(column_value))"