Splunk Search

Accessing results from search after timecharting

marrette
Path Finder

Hi all,

I'm trying to write a query that pulls up some data, time charts it, then calculates a percentage based on how much it found based on the 'known' maximum. This all fairly straight forward except the known maximum must be calculated on the number of hosts found in the orginal search.

Firstly I came up with:

index=main host=hosts0*p "serviceAvailable=true" | 
timechart partial=false span=15m count by host  | 
foreach * [eval <<FIELD>> = '<<FIELD>>' * 100 / ( (15 * 60) / (10 / 2 ) )]

The search works fine and gives good results until I try to link the number of hosts the search found with the mathamatics at the end. Ideally I would like to be able to do the following (the end of the third line it counts the number values in the host field):

index=main host=hosts0*p "serviceAvailable=true" | 
timechart partial=false span=15m count by host  | 
foreach * [eval <<FIELD>> = '<<FIELD>>' * 100 / ( (15 * 60) / (10 / stats dc(host) ) )]

But of course this isn't valid syntax!

Is there any way to get the count of the hosts this late in the query? I'm thinking some sort of sub-search but I can't seem to find anything that works.

Thanks
Eddie

Tags (1)
0 Karma
1 Solution

somesoni2
Revered Legend

Give this a try

 index=main host=hosts0*p "serviceAvailable=true" | 
 timechart partial=false span=15m count by host  | 
 eval hostcount=0 |
 foreach hosts0* [eval hostcount=hostcount+1] |
 foreach hosts0* [eval <<FIELD>> = '<<FIELD>>' * 100 / ( (15 * 60) / (10 / hostcount ) )] |
 fields - hostcount

View solution in original post

tpeveler_splunk
Splunk Employee
Splunk Employee

Here is an approach that uses an eval statement with a subsearch to get the overall number of hosts to use in the foreach statement that calculates the percentage.

I also included limit=0 in the timechart command so that all hosts are included in the results.

index=main host=hosts0*p "serviceAvailable=true"
| timechart limit=0 partial=false span=15m count by host
| eval numhosts= [ search index=main host=hosts0*p "serviceAvailable=true" | stats dc(host) as numhosts | return $numhosts ]
| foreach hosts0*p [ eval <<FIELD>> = '<<FIELD>>' * 100 / ( (15 * 60) / (10 / numhosts ) ) ]

somesoni2
Revered Legend

Give this a try

 index=main host=hosts0*p "serviceAvailable=true" | 
 timechart partial=false span=15m count by host  | 
 eval hostcount=0 |
 foreach hosts0* [eval hostcount=hostcount+1] |
 foreach hosts0* [eval <<FIELD>> = '<<FIELD>>' * 100 / ( (15 * 60) / (10 / hostcount ) )] |
 fields - hostcount
Get Updates on the Splunk Community!

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...

Reminder! Splunk Love Promo: $25 Visa Gift Card for Your Honest SOAR Review With ...

We recently launched our first Splunk Love Special, and it's gone phenomenally well, so we're doing it again, ...