Top10 in percent with lookup

[spotypoti1]

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!

[somesoni2 · Accepted solution]

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]

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]

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

[DalJeanis]

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."**")