How do you combine one REST search with one of the _internal license_usage.log?

[wrangler2x]

The REST search

| REST /services/data/indexes 
| search NOT title=_* NOT title=splunklogger NOT title=firedalerts NOT title=os NOT title=history
| sort title
| streamstats count as Row
| eval State=case(disabled==0, "Enabled", disabled==1, "Disabled <<===")
| eval "Retention Days"=frozenTimePeriodInSecs/86400
| fields Row title State "Retention Days" 
| rename title AS Index

I actually added a couple of other fields to this search that came from a lookup table (one describes what's in the index and the other who to contact about systems in it) but I'm trying to keep it simple for this question. 🙂

The _internal license_usage.log search

index=_internal source="/opt/splunk/var/log/splunk/license_usage.log" | rename idx AS Index
| eval MB=round(b/1024/1024,4)
| stats sum(MB) as MB by Index
| fieldformat MB=tostring(MB, "commas")
| addtotals MB row=f col=t

So, basically, what I want is to add to the results of the first search an additional column (MB) from the second search, by index row, with a total at the bottom of the MB column.

@MuS I've seen your many posts about combining searches without using a subsearch. I could not figure out how to make that work in this case. Is there a way? If not, then I still need help, because I never use subsearches!

[MuS · Accepted solution]

Hi wrangler2x,

this is actually a valid example to use a sub search, as long as the REST search is used in the subsearch. The reason why it should be used in the subsearch it is a generating search and it returns only a small amount of results.

Based on your example try this:

index=_internal source="/opt/splunk/var/log/splunk/license_usage.log" 
| rename idx AS Index 
| eval MB=round(b/1024/1024,4) 
| stats sum(MB) as MB by Index 
| fieldformat MB=tostring(MB, "commas") 
| addtotals MB row=f col=t 
| appendcols 
    [| REST /services/data/indexes 
    | search NOT title=_* NOT title=splunklogger NOT title=firedalerts NOT title=os NOT title=history 
    | sort title 
    | streamstats count as Row 
    | eval State=case(disabled==0, "Enabled", disabled==1, "Disabled <<===") 
    | eval "Retention Days"=frozenTimePeriodInSecs/86400 
    | fields Row title State "Retention Days" 
    | rename title AS Index ]

Just use a table to sort the fields to your liking in the end.

Hope this helps ...

cheers, MuS

[wrangler2x]

This works, but the snag I ran into is that I have an index that I use for test imports, and as I have not used it in a while, there are no license metrics for it in the license_usage.log. Consequently, the REST returns more rows due to this. I'd love to have a way for the index names in the REST search to be discarded if they don't match the ones coming back from the license usage search, but I could not figure out a way to do that. What I did do in the end was to modify the search after the REST call to discard those specific indexes. Here is the modified search, with the duplicative title column added so I could see where the mismatches were.

index=_internal source="/opt/splunk/var/log/splunk/license_usage.log" NOT idx=_* NOT idx=*summary*
 | rename idx AS Index 
 | stats sum(b) as sumb by Index
 | eval MB=sumb/1024/1024, Comment = "License usage"
 | appendcols 
     [| REST /services/data/indexes
     | search NOT title=_* NOT title=splunklogger NOT title=firedalerts NOT title=history NOT title=*summary* NOT title=sos NOT title=temp_index
     | eval State=case(disabled==0, "Enabled", disabled==1, "Disabled <<===") 
     | eval "Retention Days"=frozenTimePeriodInSecs/86400 
     | fields title State "Retention Days" ]
 | streamstats count as Row
 | fieldformat MB=tostring(MB, "commas") `comment("rounds to two decimal places")`
 | eval GB=round(MB/1024, 4)
 | addtotals GB MB row=f col=t labelfield=Index label="Totals  ==========>"
 | table Row Index title GB MB "Retention Days" State

Thanks for your help!