topic Re: SPL subquery for table in Splunk Search

SPL subquery for table

Anandkalhore — Sat, 24 Apr 2021 18:47:24 GMT

Hi,

Need help.

I want to run a query to identify if errors are increased over 10%.

Data is :

Servername errorcode1 errorcode2 count

Abcd.1.1.1000

Pqrs.1.2.1100

If errorcode2 value 1 txns are exceeding 10% of average count of its historical(7 days) count then show alert. I need to do this for all types of available errors in a single query.

I could do it for a single error code.. but i want query for all error code at once.

Index=abcd errorcode2 in (1) earliest=-1d@d latest=@d

|Stats coubt as t1 by errorcode2

|Table t1 | where t1 > 【 search index=abcd errorcode2 in (1) earliest=-8d@d latest=-2d@d

Bucket _time span=1d

|Stats count as total by _time

|Stats avg(total)as avgt

|Eval chk = 1.1* avgt

|Table chk | return $chk】

Kindky help me understand how can I compare complete table in where condition like we do in python pandas.

Re: SPL subquery for table

tscroggins — Sat, 24 Apr 2021 23:49:41 GMT

@Anandkalhore

Assuming one raw event per day per combination of Servername, errorcode1, and errorcode2:

Abcd.1.1.1000
Pqrs.1.2.1100

and field extractions:

Servername	errorcode1	errorcode2	count
Abcd	1	1	1000
Pqrs	1	2	1100

we can create labels for each value of errorcode1 and errorcode2:

index=foo sourcetype=bar earliest=-8d@d latest=@d | foreach errorcode* [ eval errorcode=mvappend(errorcode, "<<FIELD>>"."-".<<FIELD>>) ]

Servername	errorcode	errorcode1	errorcode2	count
Abcd	errorcode1-1 errorcode2-1	1	1	1000
Pqrs	errorcode1-1 errorcode2-2	1	2	1100

and split them into distinct events:

index=foo sourcetype=bar earliest=-8d@d latest=@d | foreach errorcode* [ eval errorcode=mvappend(errorcode, "<<FIELD>>"."-".<<FIELD>>) ] | mvexpand errorcode

Servername	errorcode	errorcode1	errorcode2	count
Abcd	errorcode1-1	1	1	1000
Abcd	errorcode2-1	1	1	1000
Pqrs	errorcode1-1	1	2	1100
Pqrs	errorcode2-2	1	2	1100

Finally, we can calculate the average by errorcode and return only events from yesterday with counts greater than 10% of the average of the prior 7 days:

index=foo sourcetype=bar earliest=-8d@d latest=@d | foreach errorcode* [ eval errorcode=mvappend(errorcode, "<<FIELD>>"."-".<<FIELD>>) ] | mvexpand errorcode | streamstats current=f global=f window=7 avg(count) as u by errorcode | where where _time>=relative_time(now(), "-1d@d") AND _time<relative_time(now(), "@d") AND count>(u*0.1) | table _time Servername errorcode1 errorcode2 count | dedup Servername errorcode1 errorcode2

Re: SPL subquery for table

Anandkalhore — Sun, 25 Apr 2021 07:01:10 GMT

Thank you, Tscroggins.

Streamstats is new for me. I tried executing query till streamstats (without where) but value of "u" is blanks in output. I will learn more about streamstats to know where am I going wrong.

Thank you so kuch for giving direction to solution.