How to calculate the top 5 license usage by indexes (Average value) for the last 30 days?

[bsantosh]

Hi, I would like to calculate the average of top 5 indexes by license usage for the last 30 days.
Note: there is a separate license one each for prod. and pre-prod. env.
Example: "test1-prod" (index for prod. env.) and "test1-preprod" (index for pre-prod. env.).
Need to addup the pre-prod and prod license into a single index and showup in top 5.

Ex:
- Calculate the average license usage for last 30 days for 'test1-prod' index
- Calculate the average license usage for last 30 days for 'test1-preprod' index
- Calculate the average license usage of both the indexes combined.
- Show the top 5 indexes (prod. +preprod.) license usages for the last 30 days

Required Output should be something like below:

Top 5 Indexes by License Usage:

Indexes           Avg.License usage (in GB)
test1                25
test2                21
test3                15
test5                10
test4                  5

test1 ---> avg.(test1-prod + test1-preprod)
test2 ---> avg.(test2-prod + test2-preprod)
test3 ---> avg.(test3-prod + test3-preprod)
test4 ---> avg.(test4-prod + test4-preprod)
test5 ---> avg.(test5-prod + test5-preprod)

Let me know if you need any further clarification.

Thanks

[bsantosh]

Thanks for the quick help. Its working.

[somesoni2 · Accepted solution]

Give this a try (Run from your License server. If you forward your license server logs to your indexer[recommended] then it can be run from any search head)

index=_internal source=*license_usage.log type="RolloverSummary" 
| bucket span=1d _time 
| stats sum(b) as usage by _time idx
| eval idx=replace(idx,"-(pro|preprod)","") 
| stats sum(usage) as usage by _time idx
| stats avg(usage) as usage by idx
| sort 5 -usage | eval usage=round(usage/1024/1024/1024,2)