How can I compare the average of values across all events with specific values in events?

[daktapaal]

I have the following in my query

index=_internal source=*license_usage.log  | eval sizemb=b/1024/1024
timechart span=1d sum(sizemb) by host limit= 10 | appendpipe [stats avg(*) as *]

The last bit as we know adds the last row with the averages of all the days.. Is there a way I can get hold of this average value? someway I can keep this value in the eval variable.. I need this avg of cols to check entry of each day against the average… I need to compare the day1 entry with the average.. Is this possible?

dakT

[ankireddy007]

you can use join something like

index=_internal source=*license_usage.log| bucket span=1d _time | eval sizemb=b/1024/1024 | stats sum(sizemb) as sum_per_day by host _time|
join host [search index=_internal source=*license_usage.log| bucket span=1d _time | eval sizemb=b/1024/1024 | stats avg(sizemb) as host_avg by host]

[lguinn2]

What if you do it this way instead?

index=_internal source=*license_usage.log  
| eval sizemb=b/1024/1024
| timechart span=1d sum(sizemb) as mb by host limit= 10 
| eventstats avg(*) as avg*

[daktapaal]

great. Thanks

[lguinn2]

After some thought, here is what I would like in a report:

- Compute the daily license usage

- Report the average daily license usage for the past week

You can do that like this:

index=_internal source=*license_usage.log
| eval sizemb=b/1024/1024
| bucket _time span=1d
| stats sum(sizemb) as dailyMB by host _time
| stats avg(dailyMB) as avgVol by host

Run this search for -7d@d to @d to see the average for a week. Run it for -30d@d to @d to see the average license usage per day for the month, etc.

[daktapaal]

1) will that time chart give me avg of all seven days? or avg of each day:
2) is my query wrong?

[lguinn2]

For an average of mb per week, you might wamt tp do this

index=_internal source=*license_usage.log
| eval sizemb=b/1024/1024
| timechart span=7d avg(sizemb) as avgvolperweek by host limit= 10

[daktapaal]

I donna...I will check.. But then thinking about it, if all I want is an average of mb per week , then I could also keep it simple.. and create a new search :

index=_internal source=*license_usage.log

| eval sizemb=b/1024/1024
| avgvolperweek = avg(sizemb)

and run the search for -7d@d to @d

( i was trying to reuse an existing search that produced time chart to also get me average for comparison, but i guess its a bad idea)