How to run a license report on certain index/sourcetype combinations?

[a212830]

Hi,

I need to run a report in MB for the last 7 days on a specific index and sourcetype combinations. I have this as a reference, but it filters on pool.

index=_internal source=*license_usage.log* type="Usage" | eval h=if(len(h)=0 OR isnull(h),"(SQUASHED)",h) | eval s=if(len(s)=0 OR isnull(s),"(SQUASHED)",s) | eval idx=if(len(idx)=0 OR isnull(idx),"(UNKNOWN)",idx) | bin _time span=1d | stats sum(b) as b by _time, pool, s, st, h, idx  | search pool="ECS Licensing" | timechart span=1d sum(b) AS volumeB by st fixedrange=false  | join type=outer _time [search index=_internal [`set_local_host`] source=*license_usage.log* type="RolloverSummary" earliest=-7d@d  | search pool="ECS Licensing" | eval _time=_time - 43200 | bin _time span=1d | stats latest(poolsz) AS  "pool size" by _time] | fields - _timediff  | foreach * [eval <<FIELD>>=round('<<FIELD>>'/1024/1024/1024, 3)]

[niketn]

Try the Meta Woot app for Splunk from Splunkbase.

[harsmarvania57 · Accepted solution]

Hi @a212830,

Please try

index=_internal source=*license_usage.log* type="Usage" | stats sum(b) AS Bytes by idx,st | eval MB=(Bytes/1024)/1024 | rename idx AS Index, st AS Sourcetype | table Index, Sourcetype, MB

Above query will give you license usage by Index and Sourcetype.

If you want to run report against specific Index and Sourcetype then you can run

index=_internal source=*license_usage.log* type="Usage" idx=<Index Name> st=<sourcetype> | stats sum(b) AS Bytes by idx,st | eval MB=(Bytes/1024)/1024 | rename idx AS Index, st AS Sourcetype | table Index, Sourcetype, MB

If you want to count license usage on per day basis then you can include | bin _time span=1d in your query so your query will be

index=_internal source=*license_usage.log* type="Usage" idx=<Index Name> st=<sourcetype> | bin _time span=1d | stats sum(b) AS Bytes by idx,st | eval MB=(Bytes/1024)/1024 | rename idx AS Index, st AS Sourcetype | table Index, Sourcetype, MB

I hope this helps.

Thanks,
Harshil

[a212830]

Thanks. It's giving me a total for each unique pair, but not by day.

[harsmarvania57]

Please try index=_internal source=*license_usage.log* type="Usage" | bin _time span=1d| stats sum(b) AS Bytes by idx,st | eval MB=(Bytes/1024)/1024 | rename idx AS Index, st AS Sourcetype | table Index, Sourcetype, MB

EDIT: Updated query.

[a212830]

Same thing - there is no date breakdown when I list it for 7 days.

[harsmarvania57]

I have updated query in my earlier comment, can you please try that?

If that will not work then try this one index=_internal source=*license_usage.log* type="Usage" | bin _time span=1d| stats sum(b) AS Bytes by idx,st,_time| eval MB=(Bytes/1024)/1024 | rename idx AS Index, st AS Sourcetype | table _time,Index, Sourcetype, MB

EDIT: Updated query