SPL subquery for table

[Anandkalhore]

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.

[Anandkalhore]

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.

[tscroggins]

@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