Can you help me improve this alert for indexes that could cause a license violation?

[MikeBertelsen]

I have a nice alert for letting me know when one or more indexes that average less than a gig of licensing are approaching 2g for the day.

index=_internal source=*license_usage.log type=Usage 
|eval totalMB = b/1024/1024  | eval totalGB = totalMB /1024
|rename idx as index | search index=* AND (index!=abc* index!=acc* index!=cis* index!=aws*)  
|stats sum(totalGB) as "licenseusage" by index|where licenseusage>1.9
|table licenseusage, index

What would make this better is if it were dynamic for "AND (index!=abc* index!=acc* index!=cis* index!=aws*)". I know there are a group of indexes that will always be greater than 10g per day.

So how do I rewrite this piece of the search to exclude indexes that average over 10g for the past 20 days?

[mstjohn_splunk]

Hi @mikebertelsen

Did the answer below solve your problem? If so, please resolve this post by approving it! If your problem is still not solved, keep us updated so that someone else can help ya. Thanks for posting!

[Rob2520]

@MikeBertelsen You can make little tweaks to the below query
index=_internal source=*license_usage.log type="Usage" earliest=-20d@d latest=@d idx!=indexnames
| fields _time, pool, idx, b
| bin _time span=1d
| stats sum(b) as b by _time, pool, idx
| stats sum(b) AS Bytes by idx, _time
| stats avg(Bytes) AS avgbytes by idx
| eval avgGB=round(avgbytes/1024/1024/1024,3)
| fields idx, avgGB
| rename avgGB AS "Average" idx AS "Index"
| search Average < 10

[MikeBertelsen]

I get zero results with that query. I changed it a couple ways and tried it as follows and get zero results each time:
index=_internal source=license_usage.log type="Usage" earliest=-20d@d latest=@d
| fields _time, pool, idx, b
| bin _time span=1d
| stats sum(b) as b by _time, pool, idx
| stats sum(b) AS Bytes by idx, _time
| stats avg(Bytes) AS avgbytes by idx
| eval avgGB=round(avgbytes/1024/1024/1024,3)
| fields idx, avgGB
| rename avgGB AS "Average" idx AS "Index"
| search Average < 10

and

index=_internal source=license_usage.log type="Usage" earliest=-20d@d latest=@d
| fields _time, pool, idx, b
| bin _time span=1d
| stats sum(b) as b by _time, pool, idx
| stats sum(b) AS Bytes by idx, _time
| stats avg(Bytes) AS avgbytes by idx
| eval avgGB=round(avgbytes/1024/1024/1024,3)
| fields idx, avgGB
| rename avgGB AS "Average" idx AS "Index"
| search Average < 1

[MikeBertelsen]

Taking it a step farther I changed the query to:
index=_internal source=license_usage.log type="Usage" earliest=-20d@d latest=@d idx!=cisco*
| fields _time, pool, idx, b
| bin _time span=1d
| stats sum(b) as b by _time, pool, idx
| stats sum(b) AS Bytes by idx, _time
| stats avg(Bytes) AS avgbytes by idx
| eval avgGB=round(avgbytes/1024/1024/1024,3)
| fields idx, avgGB
| rename avgGB AS "Average" idx AS "Index"
| search Average < 10

Also:
index=_internal source=license_usage.log type="Usage" earliest=-20d@d latest=@d idx=cisco*
| fields _time, pool, idx, b
| bin _time span=1d
| stats sum(b) as b by _time, pool, idx
| stats sum(b) AS Bytes by idx, _time
| stats avg(Bytes) AS avgbytes by idx
| eval avgGB=round(avgbytes/1024/1024/1024,3)
| fields idx, avgGB
| rename avgGB AS "Average" idx AS "Index"
| search Average < 10

Because cisco* I would expect to get something back

[MikeBertelsen]

Getting closer.
I compared the query to one of my own and broke it down to almost nothing then found what was off.
The asterisk was missing.
source=*license_usage.log versus source=license_usage.log

Researching further because as I read the query I would expect it to exclude certain indexes that average over 10g and they get listed the same as those that average less than 2 gigs.

[MikeBertelsen]

Ok, obvious correction to that one. Currently reads like the original with two changes:
index=_internal source=*license_usage.log type=Usage earliest=-20d@d latest=@d idx!=indexnames
| fields _time, pool, idx, b
| bin _time span=1d
| stats sum(b) as b by _time, pool, idx
| stats sum(b) AS Bytes by idx, _time
| stats avg(Bytes) AS avgbytes by idx
| eval avgGB=round(avgbytes/1024/1024/1024,3)
| fields idx, avgGB
| rename avgGB AS "Average" idx AS "Index"
| search Average < 2

That gives me a list of the indexes that as coded average less than 2g a day.

Given that I want to turn it into an alert.
First I converted the | search Average < 2 to read | search Average > 2 and it gives me a list of those indexes that average more than 2g.

So the alert would need to trigger for today any index that exceeds 2g and is not on the list of those that average 2g or more.

Or I use the | search Average < 2 portion and trigger the alert when the index is on that list but today is over the 2g average.
Will work on this further tomorrow.

[Rob2520]

@MikeBertelsen if my answers helps please accept/vote so someone else can benefit from it.

[Rob2520]

My bad. My query is missing a * at source. source should be *license_usage.log