Trying to build a license utilization report based on licenses used vs. the number of licenses purchased.

[mangelastro]

We need a license usage report based on data in a log file. I built a search that extracts the following data,

_time as date in %Y-%m-%d format, 2018-01-05 (January 5th 2018)
The next field is License, which can be a value of “Rational Quality Manager”
Next field is “Jazzrole” such as “Analyst” or “Quality Professional”
Next field is Tokens which is either “OUT” or “IN” since license tokens can be checked out and checked back in
Then “TokensInOut” is the number of tokens “checked out”
I also created a field using stats to sum the # of tokens checked out for a particular license by day called TotalTokensOut

We need to determine by day the number of tokens “checked out” for a particular license such as Rational Quality Manager and
Jazz role “Quality Professional” against the number of license tokens available which is a hard coded number such as 890 or Quality Professional or 1000 for Doors Analyst.

so the table looks like:

We’d like to build a report that includes LicenseUtilization by date, license type, jazz role, and figure the % utilized for a particular license

Any ideas how we can build this report to take the sum for each tokens used by date and license and determine the % utilized based on hard coded values such as the ones above? 890 for Quality Professional and 1000 for DOORS Analyst?

I’m very new to Splunk so any help would be appreciated.

Here’s the search info for the report

index=_* OR index=* sourcetype=lmgrd Tokens=*OUT | convert timeformat="%Y-%m-%d" ctime(_time) AS date | convert `num(TokensInOut) | stats sum(TokensInOut) as TotalTokensOut by date, License, Jazzrole`

which displays

Date License JazzRole TotalTokensOut

Thanks!

[somesoni2 · Accepted solution]

You can do like this

index=_* OR index=* sourcetype=lmgrd Tokens=*OUT | convert timeformat="%Y-%m-%d" ctime(_time) AS date | convert `num(TokensInOut)` | stats sum(TokensInOut) as TotalTokensOut by date, License, Jazzrole
| eval TotalTokens=case(Jazzrole="Quality Professional",890", Jazzrole="Doors Analyst",1000,...other roles)
| eval TokenUtilization=(TotalTokens-TotalTokensOut)*100/TotalTokens

It would be easier if you can put the TotalTokens for a Jazzrole in a lookup table, say Jazzrole_tokens.csv with field Jazzrole and TotalTokens. Once you set it up, you can replace the line 3 of above search (eval TotalTokens) with this

| lookup Jazzrole_tokens.csv Jazzrole OUTPUT TotalTokesn

[mangelastro]

Thanks! that worked great. Had to switch some things around as far as the math but its working great.