summing data from multiple fields and calculating a percentage



  • Avatar
    Kevin Sumo

    Hi Jamie,

    Here is how I would go about this query. 

    | parse regex "^(?<hostname>\S+)"
    | parse regex "(?<src_ip>\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})\s-\s-"
    | parse regex "\"(?<method>GET|PUT|POST|DELETE)\s"

    | parse regex "(?<url>\/[\w\-\.\/]+)(?:\/?|\/?[#?\s]+\S+)\s" nodrop
    | parse regex "HTTP\/1\.[01]\"\s(?<status>(?:2|4|5)\d\d)\s(?:[\d|\-]+)\s(?:\d+)"
    | if (status matches "2*", 1, 0) as success
    | if (status matches "4*", 1, 0) as client_error
    | if (status matches "5*", 1, 0) as server_error
    | sum(success) as success, sum(client_error) as client_error, sum(server_error) as server_error
    | success + client_error + server_error as total
    | client_error + server_error as total_errors
    | (total_errors/total)*100 as percent_errors


    1. We can reduce the parsing expressions to just a single expression to first capture the status codes.
    2. We then create additional fields based on the status codes, which contain either a 1 or a 0 if the status code matches as a success, client_error or server_error
    3. We can then sum up the counts of each of these fields to get the count of each status code type. 
    4. Then we can perform additional math operations on those aggregates to get the total, error total and the percentage of errors. 

    I hope this is what you were looking for. 

    Comment actions Permalink
  • Avatar
    PN Rudra

    Very helpful. Thanks!

    Comment actions Permalink
  • Avatar
    Kevin Sumo

    Hi SaiKiran,

    Your question seems unrelated to the original topic of this post so might be good to post this as a separate topic. Just from looking at the results set and your query I am not sure about the use of a transaction statement in this query.

    Does the following query get you what you need? Looks like you just want a count of the different codes from your messages. 

    | parse "{\n \"code\": \"*\"," as code nodrop
    | parse "publishError:*," as code nodrop
    | count by code
    | where code != "null"
    | sort by _count

    Comment actions Permalink

Please sign in to leave a comment.