Splunk Search

Top10 in percent with lookup

spotypoti1
Engager

This is my first attempt to create a "bigger" splunk search. I tried it the last two weeks but am stuck now. Hopefully you can help here!

I connected two csv hosts thru a lookup. The lookup creates fields that are named "look" in the hosts A and B.

I want to show the top 10 values from host B in percent of host A.

Example:

"host" ... "look"

A..............LP1
A..............LP4
A..............LP2
A..............LP1
B..............LP1
A..............LP4
B..............LP4
A..............LP4
B..............LP4
...

The Search should return a chart with the ratio "count LP (B)"/"count LP (A)" in percent for the top 10 LP* in host "B".

The Example should show:
LP4 66%
LP1 33%

I was able to return the top10 of host B. But how do i get done with the math?

index=all [search index=all host=B| top 10 look| table look| format]

Thank you!

Tags (3)
0 Karma
1 Solution

somesoni2
Revered Legend

Try like this

index=all [search index=all host=B| top 10 look| table look| format]
| chart count over look by host
| eval ratio=round('B'*100/'A')
| table look ratio

View solution in original post

somesoni2
Revered Legend

Try like this

index=all [search index=all host=B| top 10 look| table look| format]
| chart count over look by host
| eval ratio=round('B'*100/'A')
| table look ratio

DalJeanis
Legend

I'd add (host=A OR host=B) to the initial search line 1, just in case there are a bunch of them.

Also need to deal with the case where A is 0.

pradeepkumarg
Influencer

Append this search after your table command

| eventstats count by host,look | dedup host look | eventstats sum(count) as total by look | search host=B | eval percentage = (count/total)*100 | table look percentage | sort -percentage | head 10
0 Karma

DalJeanis
Legend

Couple of quick upgrades to your code.

1) Since you are only wanting one record per host and look, these commands

| eventstats count by host,look | dedup host look

...would be more effectively written as...

| stats count by host,look

2) We don't know how many hosts there might be, so we have to make sure to limit the results to only host A and B.

3) Since the initial search already limited the results to a maximum of 10 "looks", the final head command is unneeded.

4) The percentage requested was the ratio "count LP (B)"/"count LP (A)" in percent for the top 10 LP in host "B"*. So, we need to divide by A rather than the total, and we need to make sure to deal with the potential that there are no events for an LP in A, and thus avoid dividing by zero.

So, here's the modified code...

index=all (host=A OR host=B) 
    [search index=all host=B| top 10 look| table look| format] 
| stats count by host,look 
| eval {host}=count 
| stats sum(*) as * by look 
| eval A=if(A>0,A,1) 
| eval percentage = round(100*B/A,2) 
| table look B A percentage 
| sort -percentage 
| eval percentage=if(A>0,percentage,percentage."**")
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...