topic Re: How to count the number of license pool violations using REST... in Security

How to count the number of license pool violations using REST...

a212830 — Wed, 22 Mar 2017 02:02:16 GMT

Hi,

I have the search below, which provides license pool violations information. I want to take this information and generate a report that shows only the violations for the past 7 days. Unfortunately, the "Warning Days - (Soft)/Hard" field contains the dates, and it puts them all into one event. Is there anyway to break these out?

Search:

| rest splunk_server=local /services/licenser/messages 
| where (category=="license_window" OR category=="pool_over_quota") AND create_time >= now() - (30 * 86400) 
| rename pool_id AS pool 
| eval warning_day=if(category=="pool_over_quota","(".strftime(create_time,"%B %e, %Y").")",strftime(create_time-43200,"%B %e, %Y")) 
| fields pool warning_day 
| join outer pool 
    [ rest splunk_server=local /services/licenser/slaves 
    | mvexpand active_pool_ids 
    | eval slave_name=label 
    | eval pool=active_pool_ids 
    | fields pool slave_name 
    | stats values(slave_name) as "members" by pool] 
| join outer pool 
    [ rest splunk_server=local /services/licenser/pools 
    | eval pool=title 
    | eval quota=if(isnull(effective_quota),quota,effective_quota) 
    | eval quotaGB=round(quota/1024/1024/1024,3) 
    | fields pool stack_id, quotaGB] 
| stats first(pool) as "Pool" first(stack_id) as "Stack ID" first(members) as "Current Members" first(quotaGB) as "Current Quota (GB)" values(warning_day) AS "Warning Days - (Soft)/Hard" by pool 
| fields - pool 
| where Pool = "FMD License"

Here's the output:

Re: How to count the number of license pool violations using REST...

mtranchita — Wed, 22 Mar 2017 11:52:00 GMT

Might not be the best way but first thing that comes to mind is mvexpand.

http://docs.splunk.com/Documentation/Splunk/6.5.2/SearchReference/Mvexpand

Re: How to count the number of license pool violations using REST...

sloshburch — Wed, 22 Mar 2017 12:09:34 GMT

I think stats command will count the values of a multivalue field. Start with that?

Re: How to count the number of license pool violations using REST...

niketn — Wed, 22 Mar 2017 12:21:13 GMT

@a212830 can you add count(warning_day) as Count to the final stats query in your example?

Re: How to count the number of license pool violations using REST...

woodcock — Thu, 23 Mar 2017 23:17:52 GMT

Add this to your search:

| mvexpand "Warning Days - (Soft)/Hard"
| eval _time = coalesce(strptime('Warning Days - (Soft)/Hard', "(%B %d %, %Y)"), strptime('Warning Days - (Soft)/Hard', "%B %d %, %Y"))
| eval daysAgo = (now() - _time) / (24 *60 * 60)
| search daysAgo <= 7
| stats values("Warning Days - (Soft)/Hard") AS "Warning Days - (Soft)/Hard" BY Pool

Re: How to count the number of license pool violations using REST...

somesoni2 — Fri, 24 Mar 2017 02:02:48 GMT

The Warning days are being calculated in the first query from the field create_time. You can just filter the events there to show only the warning that have occurred in last 7 days, like this (line 3 added)

| rest splunk_server=local /services/licenser/messages 
 | where (category=="license_window" OR category=="pool_over_quota") AND create_time >= now() - (30 * 86400) 
 | rename pool_id AS pool 
 | where create_time>=relative_time(now(),"-7d@d")
 | eval warning_day=if(category=="pool_over_quota","(".strftime(create_time,"%B %e, %Y").")",strftime(create_time-43200,"%B %e, %Y")) 
 | fields pool warning_day 
 | join outer pool 
     [ rest splunk_server=local /services/licenser/slaves 
     | mvexpand active_pool_ids 
     | eval slave_name=label 
     | eval pool=active_pool_ids 
     | fields pool slave_name 
     | stats values(slave_name) as "members" by pool] 
 | join outer pool 
     [ rest splunk_server=local /services/licenser/pools 
     | eval pool=title 
     | eval quota=if(isnull(effective_quota),quota,effective_quota) 
     | eval quotaGB=round(quota/1024/1024/1024,3) 
     | fields pool stack_id, quotaGB] 
 | stats first(pool) as "Pool" first(stack_id) as "Stack ID" first(members) as "Current Members" first(quotaGB) as "Current Quota (GB)" values(warning_day) AS "Warning Days - (Soft)/Hard" by pool 
 | fields - pool 
 | where Pool = "FMD License"

Re: How to count the number of license pool violations using REST...

a212830 — Sun, 26 Mar 2017 14:12:53 GMT

Thanks. Didn't like the eval: " Error in 'eval' command: The arguments to the 'strptime' function are invalid."

Re: How to count the number of license pool violations using REST...

a212830 — Sun, 26 Mar 2017 15:07:18 GMT

Figured out the strptime issue, so it now looks like this and appears to work:

| rest splunk_server=local /services/licenser/messages 
| where (category=="license_window" OR category=="pool_over_quota") AND create_time >= now() - (30 * 86400) 
| rename pool_id AS pool 
| eval warning_day=if(category=="pool_over_quota","(".strftime(create_time,"%B %e, %Y").")",strftime(create_time-43200,"%B %e, %Y")) 
| fields pool warning_day 
| join outer pool 
    [ rest splunk_server=local /services/licenser/slaves 
    | mvexpand active_pool_ids 
    | eval slave_name=label 
    | eval pool=active_pool_ids 
    | fields pool slave_name 
    | stats values(slave_name) as "members" by pool] 
| join outer pool 
    [ rest splunk_server=local /services/licenser/pools 
    | eval pool=title 
    | eval quota=if(isnull(effective_quota),quota,effective_quota) 
    | eval quotaGB=round(quota/1024/1024/1024,3) 
    | fields pool stack_id, quotaGB] 
| stats first(pool) as "Pool" first(stack_id) as "Stack ID" first(members) as "Current Members" first(quotaGB) as "Current Quota (GB)" values(warning_day) AS "Warning Days - (Soft)/Hard" by pool 
| fields - pool 
| where Pool = "FMD License" 
| mvexpand "Warning Days - (Soft)/Hard" 
| eval _time = coalesce(strptime('Warning Days - (Soft)/Hard', "(%B %d, %Y)"), strptime('Warning Days - (Soft)/Hard', "%B %d,  %Y")) 
| eval daysAgo = (now() - _time) / (24 *60 * 60) 
| search daysAgo <= 7 
| stats values("Warning Days - (Soft)/Hard") AS "Warning Days - (Soft)/Hard" BY Pool |mvexpand "Warning Days - (Soft)/Hard"  |stats count

Re: How to count the number of license pool violations using REST...

a212830 — Sun, 26 Mar 2017 15:09:48 GMT

Sooooooooo, now taking it to the next level, is there a way to do a search for each pool, and have this report on each one, in a similar fashion, rather than a search for each pool?

Re: How to count the number of license pool violations using REST...

woodcock — Sun, 26 Mar 2017 17:32:36 GMT

Like this (BE SURE TO NOTE THAT I CHANGED STUFF IN THE MIDDLE, TOO!):

| rest splunk_server=local /services/licenser/messages 
| where (category=="license_window" OR category=="pool_over_quota") AND create_time >= now() - (30 * 86400) 
| rename pool_id AS pool 
| eval warning_day=if(category=="pool_over_quota","(".strftime(create_time,"%B %e, %Y").")",strftime(create_time-43200,"%B %e, %Y")) 
| fields pool warning_day 
| appendpipe 
    [ rest splunk_server=local /services/licenser/slaves 
    | mvexpand active_pool_ids 
    | eval slave_name=label 
    | eval pool=active_pool_ids 
    | fields pool slave_name 
    | stats values(slave_name) as "members" by pool] 
| appendpipe 
    [ rest splunk_server=local /services/licenser/pools 
    | eval pool=title 
    | eval quota=if(isnull(effective_quota),quota,effective_quota) 
    | eval quotaGB=round(quota/1024/1024/1024,3) 
    | fields pool stack_id, quotaGB] 
| stats first(pool) as "Pool" first(stack_id) as "Stack ID" first(members) as "Current Members" first(quotaGB) as "Current Quota (GB)" values(warning_day) AS "Warning Days - (Soft)/Hard" by pool 
| fields - pool 

| rename COMMENT AS "search Pool = FMD License"

| mvexpand "Warning Days - (Soft)/Hard" 
| eval _time = coalesce(strptime('Warning Days - (Soft)/Hard', "(%B %d, %Y)"), strptime('Warning Days - (Soft)/Hard', "%B %d,  %Y")) 
| eval daysAgo = (now() - _time) / (24 *60 * 60) 
| search daysAgo <= 7 
| stats count BY Pool
| eventstats sum(count) AS TotalAllPools

Be sure to click Accept to close the question.

Re: How to count the number of license pool violations using REST...

a212830 — Sun, 26 Mar 2017 18:33:56 GMT

Thanks. Accepted answer.

Re: How to count the number of license pool violations using REST...

sloshburch — Tue, 28 Mar 2017 12:42:27 GMT

Hey kids, don't be shy with the 'pow' function of eval.

round(quota/1024/1024/1024,3)

could be:
round( quota / pow( 1024 , 3 ) , 3 )

Whichever is easier to read.

Anyway, this is amazing! Great work!