Calculate a value from two log queries

Follow
Avatar
andras nemes

Hello,

I have two queries from the same stream that both return the number of log messages that match the search criteria. First I want to get the number of incoming blobs as follows:

namespace=ns cluster=we container=project1
| where %"log.@m" matches "*About to handle incoming blob*"
| count as Incoming

Then I have another query to get the number of successfully handled blobs from the same stream. The only difference is in the "matches" clause:

namespace=ns cluster=we container=project1
| where %"log.@m" matches "*successfully handled blob*"
| count as Success

I'd like to calculate the ratio, i.e. Success / Incoming, but I can't find the right way to achieve that. I've tested subqueries, the metrics explorer and some other ideas that Google provided but with no success. Any pointers are welcome.

0

Comments

1 comment

Sort by Date Votes
  • Avatar
    Log -o- Music
    • Edited

    Hi Andras,

    Nested IFs

    namespace=ns cluster=we container=project1
    | if( %"log.@m" matches "*About to handle incoming blob*", "Incoming", if(%"log.@m" matches "*successfully handled blob*", "Success", "")) as result
    | where !isEmpty(result)
    | count by result)

    Explanations

    Nesting IF statements is like doing if/then/else. In this case:

    • if %"log.@m" matches the first string, the result field gets the value "Incoming"
    • Else, if it matches the second string, the ratio result gets the value "Success"
    • Else, the ratio result gets an empty value ("")

    Then the count by aggregates values of result by counting each value.
    You could then use something like a circle chart to visualize the repartitions of values ("visual ratio").

    If by ratio, you mean a numerical value, you need to use two separate IF statements and put the result in two different fields, so you can do a sum, then calculate the ratio with a division:

    namespace=ns cluster=we container=project1
    | if(%"log.@m" matches "*About to handle incoming blob*", "Incoming", "") as Incoming
    | if(%"log.@m" matches "*successfully handled blob*", "Success", "") as Success
    | where !isEmpty(Incoming) and !isEmpty(Success)
    | sum(Incoming) as incoming_count, sum(Success) as success_count
    | incoming_count / success_count as ratio

     

    Hope this helps.

    ~Log-o-Music

    1
    Comment actions Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post