How to get a daily report of license usage by index across all indexers?

[lesterw]

All of my indexers rely on one Splunk license server. I would like a daily report that shows me for each of the last 7 days how much license was consumed for each different index name. For example:

Server1 indexes: cisco, juniper, _internal
Server2 indexes: apple, metrovpn, _internal
License Server indexes: smalldata, _internal

Ideally, this would be a stacked bar chart with colors for each index name and the total showing the total license consumed (in GB) for that day. So the graph would have 7 days. But I will settle for any report as long as it can give me the sum total (in GB) for each day and a breakdown by index name of the amount of license consumed per day. The _internal databases ould be summed up into a single total per day.

I played around with the searches but I can never find the detail I want, event at the License Server itself. Any help would be appreciated!

[miteshvohra]

Have a look at the Fire Brigade App. You might find a lot of useful panels which can be re-used to fit your purpose. It comes with a TA to gather info from across other Splunk instances too.

[mbrenton]

This is what I use now. I took crash1011's work and ran with it (thank you!)

Create a new dashboard and edit source, paste this into the XML field.

<form>
  <label>License Usage</label>
  <fieldset submitButton="false" autoRun="true">
    <input type="time" searchWhenChanged="true" token="field1">
      <label></label>
      <default>
        <earliest>-7d@d</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <chart>
        <title>Daily License Usage by Index</title>
        <search>
          <query>index=_internal source=*license_usage.log type=Usage  | rename idx AS index  | timechart span=1d eval(round(sum(b)/1024/1024/1024,2)) AS "Total GB Used" by index</query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.text">Date</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.text">License Usage</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.enabled">false</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">column</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisStart</option>
        <option name="charting.legend.placement">right</option>
        <option name="charting.axisLabelsY.majorUnit">10</option>
        <option name="charting.axisY.maximumNumber">60</option>
        <option name="charting.axisY.minimumNumber">0</option>
      </chart>
    </panel>
  </row>
  <row>
    <panel>
      <chart>
        <title>Total Daily License  Usage</title>
        <search>
          <query>index=_internal source=*license_usage.log type=Usage  | timechart span=1d eval(round(sum(b)/1024/1024/1024,2)) AS "Total GB Used"</query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.text">Date</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.text">GB</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.enabled">false</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">column</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisStart</option>
        <option name="charting.legend.placement">right</option>
        <option name="wrap">true</option>
        <option name="rowNumbers">false</option>
        <option name="dataOverlayMode">none</option>
        <option name="charting.axisLabelsY.majorUnit">25</option>
      </chart>
    </panel>
    <panel>
      <table>
        <title>Daily License Usage by Index Stats</title>
        <search>
          <query>index=_internal source=*license_usage.log type=Usage earliest=-7d@d  | rename idx AS index  | timechart span=1d eval(round(sum(b)/1024/1024/1024,2)) AS "Total GB Used" by index</query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
        </search>
        <option name="wrap">true</option>
        <option name="rowNumbers">false</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="count">10</option>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <chart>
        <title>License Usage by Host</title>
        <search>
          <query>index=_internal source=*license_usage.log type=Usage | stats sum(b) AS bytes by h | eval GB= round(bytes/1024/1024/1024,2) | fields h GB | rename h as host | sort -GB</query></query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.enabled">false</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">pie</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisStart</option>
        <option name="charting.legend.placement">right</option>
      </chart>
    </panel>
    <panel>
      <chart>
        <title>License Usage by Sourcetype</title>
        <search>
          <query>index=_internal source=*license_usage.log type=Usage | stats sum(b) AS bytes by st | eval GB= round(bytes/1024/1024/1024,2) | fields st GB | rename st as Sourcetype | sort -GB</query></query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
        </search>
        <option name="charting.axisLabelsX.majorLabelStyle.overflowMode">ellipsisNone</option>
        <option name="charting.axisLabelsX.majorLabelStyle.rotation">0</option>
        <option name="charting.axisTitleX.visibility">visible</option>
        <option name="charting.axisTitleY.visibility">visible</option>
        <option name="charting.axisTitleY2.visibility">visible</option>
        <option name="charting.axisX.scale">linear</option>
        <option name="charting.axisY.scale">linear</option>
        <option name="charting.axisY2.enabled">false</option>
        <option name="charting.axisY2.scale">inherit</option>
        <option name="charting.chart">pie</option>
        <option name="charting.chart.bubbleMaximumSize">50</option>
        <option name="charting.chart.bubbleMinimumSize">10</option>
        <option name="charting.chart.bubbleSizeBy">area</option>
        <option name="charting.chart.nullValueMode">gaps</option>
        <option name="charting.chart.sliceCollapsingThreshold">0.01</option>
        <option name="charting.chart.stackMode">default</option>
        <option name="charting.chart.style">shiny</option>
        <option name="charting.drilldown">all</option>
        <option name="charting.layout.splitSeries">0</option>
        <option name="charting.legend.labelStyle.overflowMode">ellipsisStart</option>
        <option name="charting.legend.placement">right</option>
      </chart>
    </panel>
    <panel>
      <chart>
        <title>License Usage by Source</title>
        <search>
          <query>index=_internal source=*license_usage.log type=Usage | stats sum(b) AS bytes by s | eval GB= round(bytes/1024/1024/1024,2) | fields s GB | rename s as Source | sort -GB</query></query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
        </search>
        <option name="charting.chart">pie</option>
        <option name="charting.axisY2.enabled">undefined</option>
      </chart>
    </panel>
  </row>
  <row>
    <panel>
      <table>
        <title>License Usage by Host Stats</title>
        <search>
          <query>index=_internal source=*license_usage.log type=Usage | stats sum(b) AS bytes by h | eval GB= round(bytes/1024/1024/1024,2) | fields h GB | rename h as host | sort -GB</query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
        </search>
        <option name="wrap">true</option>
        <option name="rowNumbers">false</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="count">10</option>
      </table>
    </panel>
    <panel>
      <table>
        <title>License Usage by Sourcetype Stats</title>
        <search>
          <query>index=_internal source=*license_usage.log type=Usage | stats sum(b) AS bytes by st | eval GB= round(bytes/1024/1024/1024,2) | fields st GB | rename st as Sourcetype | sort -GB</query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
        </search>
        <option name="wrap">true</option>
        <option name="rowNumbers">false</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="count">10</option>
      </table>
    </panel>
    <panel>
      <table>
        <title>License Usage by Source Stats</title>
        <search>
          <query>index=_internal source=*license_usage.log type=Usage | stats sum(b) AS bytes by s | eval GB= round(bytes/1024/1024/1024,2) | fields s GB | rename s as Sourcetype | sort -GB</query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
        </search>
        <option name="wrap">true</option>
        <option name="rowNumbers">false</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="count">10</option>
      </table>
    </panel>
  </row>
</form>

[landen99]

You should streamline all those panels using a base search:

<search id="base">

[crash1011]

<form>
  <label>Detailed License Summary</label>
  <fieldset submitButton="false" autoRun="true">
    <input type="time" searchWhenChanged="true" token="field1">
      <label></label>
      <default>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <title>License Usage by Day - Last 7 Days</title>
        <search>
          <query>index=_internal source=*license_usage.log type=Usage earliest=-7d| eval GB=b/1024/1024/1024 |timechart span=1d sum(GB) as "Total GB Used"</query>
          <earliest>-7d@h</earliest>
          <latest>now</latest>
        </search>
        <option name="wrap">true</option>
        <option name="rowNumbers">false</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="count">10</option>
      </table>
    </panel>
    <panel>
      <table>
        <title>License Usage by Sourcetype</title>
        <search>
          <query>index=_internal source=*license_usage.log type=Usage | stats sum(b) AS bytes by st | eval MB= round(bytes/1024/1024,1) | fields st MB | rename st as Sourcetype | sort -MB</query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
        </search>
        <option name="wrap">true</option>
        <option name="rowNumbers">false</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="count">10</option>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <table>
        <title>License Usage by Source</title>
        <search>
          <query>index=_internal source=*license_usage.log type=Usage | stats sum(b) AS bytes by s | eval MB= round(bytes/1024/1024,1) | fields s MB | rename s as Sourcetype | sort -MB</query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
        </search>
        <option name="wrap">true</option>
        <option name="rowNumbers">false</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="count">10</option>
      </table>
    </panel>
    <panel>
      <table>
        <title>License Usage by Host</title>
        <search>
          <query>index=_internal source=*license_usage.log type=Usage | stats sum(b) AS bytes by h | eval MB= round(bytes/1024/1024,1) | fields h MB | rename h as host | sort -MB</query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
        </search>
        <option name="wrap">true</option>
        <option name="rowNumbers">false</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="count">10</option>
      </table>
    </panel>
  </row>
</form>

[landen99]

You should streamline all those panels using a base search:

<search id="base">

[hexx]

Probably with something like:

index=_internal source=*license_usage.log type=Usage earliest=-7d
| eval MB = b/1024/1024
| rename s AS source
| timechart span=1d sum(b) AS "Total MB used" by source

That being said, be aware that 'source' (or 's' in this case) is typically a field with high cardinality, which has two consequences:

• If we see too many distinct active sources over the logging period of license_usage.log (1min), we will account only for the first 1000.
• Performing a split-by with a high cardinality field typically doesn't yield results that are viz-friendly.

[yannK]

if you want per index and indexer, look at the fields "i" and "idx".
(the indexer will be the GUID, so you may need a way to do the mapping of the GUID to the splunk server name)

index=_internal source=*license_usage.log* type=Usage earliest=-7d
 | bucket _time span=1d 
 | stats sum(b) AS bytes by _time i idx
 | eval GB=bytes/(1024/1024/1024)

[athorat]

Hi

I had a query which used to get the right data before the upgrade.
index=_internal source=license_usage.log type=Usage | timechart span=1d sum(b) as bytes | eval GB = round(bytes/1024/1024/1024,5) | fields _time GB

It was a single search head then version 6.1
Now its a 3 server cluster and and the current version is 6.3
all the above queries mentioned in this thread does not work either.

License Master is still on 6.1
Current architecture : 3 search head, 4 indexers , 2 deployment servers and 100+ universal forwarders.

Thanks for looking into this.

[lesterw]

Thank you! I noticed that the *license_usage.log does have the source in it as "s="... How might I accomplished a stacked daily bar chart using the source? For example, for each day, the bar would have color-coded bands for each of the sources ("s="). The height of the bar would be the total GB consumed that day.

[hexx]

The only reliable source for license usage is the $SPLUNK_HOME/var/log/splunk/license_usage.log file on your license master instance, and unfortunately it does not split usage by index.

There is, however, a sampled record of kilobytes indexed for the top 10 most active indexes every 30s in metrics.log. Of course if you have less than 10 active indexes, this is not an issue.

So, for a report showing daily license usage over the past 7 days, you would run:

index=_internal source=*license_usage.log type=RolloverSummary earliest=-7d
| eval GB = b/1024/1024/1024
| eval _time = _time - 43200
| timechart span=1d sum(GB) AS "Total GB used"

For a report showing estimated daily volume indexed (whether it counted against your license quota or not) over the past 7 days, you would run:

index=_internal (host=indexer1 OR host=indexer2 OR host=license_master) source=*metrics.log group=per_index_thruput earliest=-7d
| timechart span=1d sum(eval(kb/1024)) AS "MB indexed" by series

You should be able to exclude internal indexes from that last search by using a negative match expression such as:

NOT (series=_* OR series=*summary)

If you have any summary indexes, make sure to include them in this expression as well.