Splunk Search

How to generate a search to compare license usage for an index?

a212830
Champion

Hi,

We want to track our Top N users of license by index, and then compare it to yesterday (and possibly alert on major changes). Not sure how to do that... any suggestions?

0 Karma

sloshburch
Splunk Employee
Splunk Employee

See my comment under your question. With that understanding in mind:

While you can use other sources within the _internal index, the real challenge comes in when the search history never contained a reference to the index. For example, how do you discern the index searched if it wasn’t part of the search query? For example, tags, eventtypes, datamodels, and macros would show search strings in the audit that don’t expand the definitions of those knowledge objects.

Sorry to be a buzzkill. I’ve seen this question come up a number of times with varying answers depending on the desired goal.

You might be able to use joins and lookups to correlate tags or macros (etc) back to the their definitions BUT those definitions change over time and there might be circular references (tags that call macros that call tags - even if different ones) so there's some challenges in how to write such a search. Perhaps the community has another approach?

0 Karma

sloshburch
Splunk Employee
Splunk Employee

To clarify, I think @212830 is looking for determining if data indexed by Splunk is actually getting "used" where "used" is defined as searched by a variety of users or by quantity of searches (but maybe less users). This goes to determining the value the indexed data is bringing.

0 Karma

dbcase
Motivator

This might be a little off topic but instead of using an index by user to see how valuable the data is, I use a dashboard by user query.

index="_internal" source=/opt/splunk/var/log/splunk/splunkd_ui_access.log  "en-US/app" | rex "(?i)^[^\-]*\-\s+(?P<user>[^ ]+)" | rex "en-US/app/(?<app>[^/]+)/(?<dashboard>[^?/\"\s]+)"| search app="search" dashboard!="job_management" dashboard!="dbinfo" dashboard!="*en-US" dashboard!="search" dashboard!="home" dashboard!="alert*" dashboard!="dashboards" dashboard!="reports" dashboard!="report" dashboard!="@go" dashboard!="%40go" dashboard!="field_extractor" dashboard!="pivot" dashboard!="test2"  dashboard!="test3"  dashboard!="test5" user!="-"|bucket _time span=1d | stats dc(dashboard) as c by dashboard user _time |  chart  useother=f limit=0 count by dashboard user|sort -count|rename dashboard as "Dashboard Name"
0 Karma

dbcase
Motivator

If you just need by index this query works

 index=_internal source="*license_usage.log*"|where idx!="NULL"| timechart limit=0 span=1d sum(eval(round(b/1024/1024/1024,5))) by idx
0 Karma

rjthibod
Champion

If you can track indexing by hosts instead of user, then you can use the built-in usage data with the following:

index=_internal source=*license_usage.log* type="Usage" idx=<YOUR_INDEX> 
| timechart span=1d limit=0 eval(round(sum(b)/1024/1024,3)) as MB by h

If you need to track indexing by user, then you would have to look at the raw data lengths.

index=<YOUR_INDEX>
| timechart span=1d limit=0 sum(eval(len(_raw)/1024/1024)) by <USER_FIELD>

Without getting into worrying about days of the week and patterns of behavior, you can then compare between days with the following, where you will see where there is a 50% change in volume

 | untable _time user mb
 | streamstats window=1 current=f last(mb) as prev_mb by user
 | eval perc_diff = (abs(prev_mb - mb)/mb)*100
 | search perc > 50 prev_mb > 0
0 Karma

a212830
Champion

Sorry, user has nothing to do with, I want by index. We'll associate the index to a bu internally via dbx.

0 Karma

marcmuher
Explorer

To see how much each source is putting through, try this search- change the sourcetype as needed.

sourcetype=WinEventLog:*
| fields _raw, _time, host
| eval evt_bytes = len(_raw)
| timechart span=1d sum(eval(evt_bytes/1024/1024)) AS TotalMB by host

To see how big your indexes are, try this search:

| rest /services/data/indexes | eval perc=(currentDBSizeMB * 100 / maxTotalDataSizeMB ) | table title currentDBSizeMB maxTotalDataSizeMB perc

0 Karma

sloshburch
Splunk Employee
Splunk Employee

I used to do it this way but recently learned that this won't be 100% accurate because

  1. it assumes that len and the license counter measure the same (they don't, len measures characters while the license counter measures bytes)
  2. it assumes that there's no delay or lag in indexing. _indextime is not the same as _time. Sometimes forwarders get backed up and an item may be indexed some time after what it's _time value is.
0 Karma

rjthibod
Champion

Can you please clarify what you mean in your original post by "Top N users by index"? What do you mean "users"?

0 Karma

a212830
Champion

Just topn index by volume. We'll associate the index name internally with a bu.

0 Karma

rjthibod
Champion

Ok then, how about his? Your "N" can be set by changing the value "limit=N" in the timechart command.

index=_internal source=*license_usage.log* type="Usage"
| timechart span=1d limit=10 eval(round(sum(b)/1024/1024,3)) as mb by idx
| untable _time idx mb
| streamstats window=1 current=f last(mb) as prev_mb by idx
|eval perc_diff = (abs(prev_mb - mb)/mb)*100
| eval marker = if(perc > 50 AND prev_mb > 0, 1, 0)
0 Karma

rjthibod
Champion

That query should give you the field marker the day when a given index changed by 50% percent or more as compared to the previous day. You could overlay that information with a chart show the daily totals, or you could filter down to just that field and setup alerts.

0 Karma

dbcase
Motivator

Hi A212830,

This query worked for me. It doesn't compare to yesterday but you could easily do that with the timewrap app.

index=_internal source="*license_usage.log*"|where idx!="NULL"| timechart limit=0 span=1d sum(eval(round(b/1024/1024/1024,5))) by idx
0 Karma

dbcase
Motivator

Whups sorry that doesn't do the user part. Lemme dig on that

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...