Forum Discussion

tkeller's avatar
tkeller
Copper Contributor
Dec 03, 2019

Expanding Alert Log Search Query

Hello -

We have an alert setup that does the following:

W3CIISLog
| where Computer == "W9" or Computer == "W10"
| summarize Hits=count() by cIP
| where Hits >= 600
| where cIP !startswith "10.0"
| order by Hits desc

And what I would like is for those results to then be piped into this query and the results returned:

W3CIISLog
| where cIP == "192.34.85.134"
| summarize count () by csUriStem
| order by count_ desc

Any help is greatly appreciated.

  • Glad I am helping, tkeller.

     

    Regarding the low threshold, I am not sure I understood what you meant, but:

     

    [I am also structuring the query better]

     

    1) If you want to exclude hits/URLs counts less than 50

     

    W3CIISLog
    | where (Computer == "W9" or Computer == "W10") and cIP !startswith "10.0"
    | summarize Hits=count() by cIP
    | where Hits >= 600

    join kind=inner (W3CIISLog | summarize UriHits = count() by cIP, csUriStem) on cIP

    project cIP, Hits, csUriStem, UriHits
    | where Hits > 50 and UriHits > 50
    order by Hits desc, UriHits desc
     

    2) If you want just the topmost 50 cIP:

     

    W3CIISLog
    | where (Computer == "W9" or Computer == "W10") and cIP !startswith "10.0"
    | summarize Hits=count() by cIP
    | where Hits >= 600

    | order by Hits desc

    | limit 50
    join kind=inner (W3CIISLog | summarize UriHits = count() by cIP, csUriStem) on cIP

    project cIP, Hits, csUriStem, UriHits
    order by Hits desc, UriHits desc
    // I don't think we can limit here further for UriHits results
     

     

  • tkeller, something like this?

     

    W3CIISLog
    | where Computer == "W9" or Computer == "W10"
    | summarize Hits=count() by cIP
    | where Hits >= 600
    | where cIP !startswith "10.0"

    | join kind=inner (W3CIISLog | summarize UriHits = count() by cIP, csUriStem) on cIP

    | project cIP, Hits, csUriStem, UriHits
    | order by Hits desc, UriHits desc
     
    • tkeller's avatar
      tkeller
      Copper Contributor

      hspintothat does indeed return part of the desired results, but it truncates them... I assume because of "Summarize"?  Is there a better results collector, and is there a way to better format the results?

      Thank you very much

      • hspinto's avatar
        hspinto
        Icon for Microsoft rankMicrosoft

        tkeller, what do you mean by truncating? Do you wanted to see more columns or more rows? Summarize indeed reduces the column span to what is specified in the aggregation - if you want more columns, you have to add them to the aggregation. Regarding row count, Log Analytics has a limit on the number of rows returned by a query (10,000).

         

        Can you elaborate more on the results format?

         

        Thank you!

Resources