Splunk Search

Automated an Alert with a complicated search query

phamxuantung
Path Finder

Hi, I want make a report(or Alert) each month to count the Total transaction success in 1 month and compare it to 3 months before it, if it exceed 200%. For example: count the Total transaction success of January compare to last year Oct-Sep.

My code is for the specific example above is

 

 

index=index1 earliest=10/1/2020:00:00:00 latest=1/31/2021:24:00:00
|search RESPONSE_CODE="0"
|stats count AS Total count(eval(date_month="october" OR date_month="november" OR date_month="december")) AS Total_3MONTHS count(eval(date_month="january"))AS MONTH1 BY MERCHANT_CODE
|eval 3MONTHS_AVG = round(Total_3MONTHS/3,2)
|eval RATE = round((MONTH1/3MONTHS_AVG)*100,2)
|search RATE>=200
|table MERCHANT_CODE, MONTH1, RATE

 

 

1. I want to make it automatically send me an email at the start of the month about the last month search without me manually change the time range and search term
2. Sometime, the 3MONTHS_AVG=0 due to there aren't any transactions on those 3 months. It lead to the RATE don't show as well because "divide by 0".

If anyone have the solution to these problems, I would really appreciate. Thank you

Labels (2)
0 Karma
1 Solution

bowesmana
Champion

@phamxuantung 

My bad, it was comparing last month against last 3. This will do the last month compared to the 3 months BEFORE the last, i.e. for today, compare January to Oct-Dec.

index=index1 earliest=-4mon@mon latest=@mon RESPONSE_CODE="0"
| bin _time span=1mon
| stats count AS MonthTotal BY MERCHANT_CODE, _time
| eval lastMonthStart=relative_time(now(), "-mon@mon")
| stats values(eval(if(_time >= lastMonthStart, MonthTotal, null()))) as MONTH1 avg(eval(if(_time >= lastMonthStart, null(), MonthTotal))) as 3MONTHS_AVG by MERCHANT_CODE
| eval RATE = if(isnull(MONTH1), "NoSales", round((MONTH1/'3MONTHS_AVG')*100,2))
| search RATE>=200 OR RATE="NoSales"
| table MERCHANT_CODE, MONTH1, RATE

The second stats will

- calculate the MONTH1 only if there are sales in the previous month (January)

- calculate the 3MONTHS_AVG as the average of the first 3 months

Then do the rest as before.

 

View solution in original post

bowesmana
Champion

@phamxuantung 

It's actually quite simple using states and relative date settings.

Your search can be like this

index=index1 earliest=-3mon@mon latest=@mon RESPONSE_CODE="0"
| bin _time span=1mon
| stats count AS MonthTotal BY MERCHANT_CODE, _time
| stats latest(MonthTotal) as MONTH1 latest(_time) as latestMonth sum(MonthTotal) as Total avg(MonthTotal) as 3MONTHS_AVG by MERCHANT_CODE  
| eval lastMonthStart=relative_time(now(), "-mon@mon")
| eval RATE = if(latestMonth>=lastMonthStart, round((MONTH1/'3MONTHS_AVG')*100,2), "NoSales")
| search RATE>=200 OR RATE="NoSales"
| table MERCHANT_CODE, MONTH1, RATE

 So this

1. searches from -3 months to -1 month and 'SNAPS' to the start/end of month. As long as this is run iany time n the month after the 3 month period the earliest and latest will be correct.

2. Calculates time buckets of 1 month and calculates the monthly totals for each merchant code, as well as the last month a sale was made (latestMonth)

3. Takes the latest month as last month and sums the 3 months for the total and takes the 3 monthly average - NB this is the AVERAGE over the period - if there are no sales in one month then the average will ignore that month.

4. It calculates the first day of the last month and then calculates the rate only if the last month had sales. Otherwise it sets rate to "NoSales"

5. The test for rate>=200 or NoSales then filters.  You can change what you want to do with those no sales months.

Hope this helps.

If you schedule this each month on the 1st, it will run happily without needing to change anything.

 

phamxuantung
Path Finder

Thank you for your detailed explanation, but your search only span on three months and calculate the latest month compare to the average of latest 3 months including that latest month. For example: On February[2], it will compare January[1] to the average of last year (November[11]+December[12]) and January[1]. Am I understand it right?

If so, my range of search is different, it span on 4 months, and compare latest month to the 3 months before it. For example: On February[2], it will compare January[1] to the average of last year (October[10]+November[11]+December[12])

0 Karma

bowesmana
Champion

@phamxuantung 

My bad, it was comparing last month against last 3. This will do the last month compared to the 3 months BEFORE the last, i.e. for today, compare January to Oct-Dec.

index=index1 earliest=-4mon@mon latest=@mon RESPONSE_CODE="0"
| bin _time span=1mon
| stats count AS MonthTotal BY MERCHANT_CODE, _time
| eval lastMonthStart=relative_time(now(), "-mon@mon")
| stats values(eval(if(_time >= lastMonthStart, MonthTotal, null()))) as MONTH1 avg(eval(if(_time >= lastMonthStart, null(), MonthTotal))) as 3MONTHS_AVG by MERCHANT_CODE
| eval RATE = if(isnull(MONTH1), "NoSales", round((MONTH1/'3MONTHS_AVG')*100,2))
| search RATE>=200 OR RATE="NoSales"
| table MERCHANT_CODE, MONTH1, RATE

The second stats will

- calculate the MONTH1 only if there are sales in the previous month (January)

- calculate the 3MONTHS_AVG as the average of the first 3 months

Then do the rest as before.

 

View solution in original post

phamxuantung
Path Finder

The time span is correct, but the only problem I found with your code is the

 

 

avg(eval(if(_time >= lastMonthStart, null(), MonthTotal))) as 3MONTHS_AVG

 

 

This calculate the average of all values, but if 1 month don't have transaction, then avg will only divide by 2 instead of 3. I checked by changing the eval to values.

Edit: I found the solution, I replace your code with this

 

 

sum(eval(if(_time<lastMonthTotal,MonthTotal,null()))) AS THREE_MONTHS

 

 

 and then calculate the average by divide to 3 manually on a separate eval. Then continue like normal.

Thank you so much.

0 Karma