Calculating Kill / Death ratio in a game

[khourihan_splun]

I'd like to calculate K/D ratio for the game Insurgency.

I have two searches that can calculate #kills and number of deaths #killer

I'd like to calculate the ration of K v. D's.

index=insurgency  sourcetype="insurgency" killed 
| rex "killed \"(?<killed>.*?)<" 
| rex ":\s+\"(?<killer>.*?)<" 
| stats count by killer

I'd like the calculate the ration of Kills/Deaths. Any suggestions?

[somesoni2 · Accepted solution]

Try this:

  index=insurgency sourcetype=insurgency | rex "killed \"(?<killed>.*?)<" 
   | rex ":\s+\"(?<killer>.*?)<"  | stats count by killer killed | appendpipe [ stats sum(count) as deaths by killed | rename killed as user ]
    | appendpipe [ stats sum(count) as kills by killer | rename killer as user ]| stats sum(deaths) as deaths sum(kills) as kills by user |fillnull value=0
    | eval ratio=if(deaths=0, kills,kills/deaths)

OR

 index=insurgency sourcetype=insurgency | rex "killed \"(?<killed>.*?)<" 
       | rex ":\s+\"(?<killer>.*?)<"  | eval Temp=killed.",Death ".killer.",Kill" | makemv Temp| table Temp| mvexpand Temp| rex field=Temp "(?<User>.*),(?<Action>.*)" | chart count over User by Action | eval ratio=if(Death=0, Kill,Kill/Death)

[khourihan_splun]

both these queries worked. thanks somesoni2! and thanks Lisa !!

[khourihan_splun]

Hey Lisa,
Thanks, here's what that yielded:

[lguinn2]

Why not

index=insurgency  sourcetype="insurgency" killed 
| rex "killed \"(?<killed>.*?)<" 
| rex ":\s+\"(?<killer>.*?)<" 
| stats count by killer killed
| appendpipe [ stats count as deaths by killed | rename killed as user ]
| appendpipe [ stats count as kills by killer | rename killer as user ]
| stats sum(deaths) as deaths sum(kills) as kills by user
| eval ratio=kills/deaths

There may be a better way, but I just can't think of it.

[khourihan_splun]

your correction fixed it. nice job thanks!

[yannK]

Splunk all the GAMES LOGS !!!!!!

[lguinn2]

Found a typo - I have updated the answer above!

[khourihan_splun]

Thanks Lisa,
Sadly this didn't work. I will show the output of the search in the next box.