show % increase over time

[hartfoml]

I have a search that shows me the 90 day trend of my Splunk license use.

index="_internal" source="*metrics.log" per_index_thruput NOT series="*summary" | eval SLA=### |convert timeformat="%m/%d - %a" ctime(_time) AS kdate| stats sum(kb) as kb_indexed by kdate SLA | eval GB = round(kb_indexed/1024/1024,2)| sort date_mday | table kdate GB SLA | rename kdate as "Date" | trendline sma10(GB) as Trend

First is this a good evaluation of license use?

Second The trend is so slight that the % increase is less than 5%.

I would like to show the % increase over 90 days for the trend line on the same graph as the line graph

[lpolo · Accepted solution]

1) you need to make sure that index=_internal has all the data to create the 90 days report you need.

2) SLA is your Splunk license in GB. Example below shows SLA=502. It means 502GB per day.

3) splunk_server should be your master license server or the splunk instance that you would like to trend in case you do not have a master license server.

4) This query should do what you need:

splunk_server=your_splunk_license_server_host index=_internal source="*license_usage.*" earliest=-90d@d latest=@d | eval GB=b/1024/1024/1024 | timechart span=1d sum(GB) as GB | eval SLA=502|eval %_License_used=GB*100/SLA

Since there is not a summary index to make it faster you need to create one. This is another approach using a schedule search to populate a summary index that will allow you to trend the license usage by source type, host and total license usage in a daily basis:

1) Create an index to be used by the search presented next.

2) create a scheduled search to run daily. the query of the scheduled search is the following:

splunk_server=your_splunk_license_server_host index=_internal source="*license_usage.*" earliest=-1d@d latest=@d | eval GB=b/1024/1024/1024  | stats sum(GB) as GB by h st|sort - GB

3) In the schedule search configuration select the index you created.

4) Then, back fill the summary index in case you need to.

5) Finally, You could use these queries to get trends by sourcetype, host and total license usage:

License usage trend:

index=your_sumary_index splunk_server=your_splunk_server_that_has_the_summary_index  |
timechart span=1d sum(GB) as GB |
eval License="502"|
eval Exceeded=if(License>GB,"0",GB-License)|
eval Date=strftime(_time, "%m/%d/%Y")|
table Date GB License Exceeded|
rename GB as "License Volume Used (GB)"|
rename Exceeded as "License Volume Exceeded by (GB)"|
rename License as "Max. License Volume (GB)"|sort - Date

Trend by source type:

index=your_sumary_index splunk_server=your_splunk_server_that_has_the_summary_index  |

timechart span=1d sum(GB) as GB by st

Trend by host:

index=your_sumary_index splunk_server=your_splunk_server_that_has_the_summary_index  |

timechart span=1d sum(GB) as GB by h

Thanks,
Lp

[hartfoml]

the math at the end of the search is simple and plan. I don't know who i didn't think of that. this is useful code thanks.

Only it takes too long to run. there has to be a summery index for license use somewhere that does not take so ling to run.