How to compare the counts of certain fields to find a ratio in a search?

[flytrinity]

We have a server access log with format like below.

10:30:30.269 HostID=1001, ClientConnectionStatus=Connecting,...
10:30:32.269 HostID=1004, ClientConnectionStatus=Connecting,...
10:30:33.269 HostID=1004, ClientConnectionStatus=Connected,...
10:30:50.269 HostID=1001, ClientConnectionStatus=Connecting,...
10:31:10.269 HostID=1001, ClientConnectionStatus=Connecting,...
10:31:11.269 HostID=1001, ClientConnectionStatus=Connected,...

So a host is trying to connect to server and if it fails it will re-try it every 20sec (in this example, host 1001 has tried three times and eventually connected at 3rd attempt while host 1004 was able to connect by its 1st attempt). We'd like to have a search/report to count the ratio of connected/connecting attempts per HostID, for example 1001 is 1/3 and 1004 is 1 in this case. Was wondering what's the best way of constructing this?

Thank you!

[gkanapathy · Accepted solution]

sourcetype=mydata 
| stats count(eval(ClientConnectionStatus=="Connecting")) AS connecting_count
        count(eval(ClientConnectionStatus=="Connected")) AS connected_count
  by HostID
| eval ratio=connected_count/connecting_count

[flytrinity]

Grt thanks for the quick answer, gkanapathy! I will have a try when i back to office later. Thanks very much.

[upmangaurav]

Hi Gerald, I am getting the second count(here 'connected_count') as 0 with a similar query. Could you please suggest an alternative to this approach if you have any in mind?