Forum Discussion

Aaron_Platek's avatar
Aaron_Platek
Copper Contributor
Jan 18, 2023

Getting a count from a watchlist off one or more columns

I am trying to pull a count of two different stats off one column and then two different stats off another column. I am curious if this is possible and if I can be pointed in the right direction. I know I can use the "let" operator as well as the "join" and "union" operator but when I try and place them in a workbook the data comes out horrible.

 

One a union it has 0 data in the work book but in the logs it shows data. In the join command the data shows huge graphs that are maxed as a sum.

 

So I am curious if I could use the "case" command or "strcat" to pull more than one count from one column and then more than one column from the second column or even third and place them in rows?

example:

API call1       56

API call2       70

API call3       120

KQL

2 Replies

  • Clive_Watson's avatar
    Clive_Watson
    Bronze Contributor
    Jan 19, 2023
    I'm not sure what the data looks like or what you want the output to be - is the "example" how the data looks or the result you want? Do you have a query so far? Or can you provide a datatable, like this?

    let example_ = datatable (TimeGenerated:datetime, Api:string, Value:int)
    [
    datetime(1/19/2023),"API call1",56
    ,datetime(1/19/2023),"API call2",70
    ,datetime(1/19/2023),"API call3",120
    ];
    example_
    • Aaron_Platek's avatar
      Aaron_Platek
      Copper Contributor
      Jan 20, 2023
      so basically, the watchlist looks like this."

      TimeGenerated SearchKey (useless) Cisco Mandiant VirusTotal
      X -1 45 15
      X -1 80 10
      X 13 -1 0
      X 33 65 5

      So, what I need to do is come up with is:
      -Take the -1 out of cisco and come up with a count
      -Come up with an account of all Mandiant
      -Come up with all account for VirusTotal

      I have something right now, with using recursive tabling but when trying to make a workbook and using a graph it comes out as one block:

      let Day = (
      SecurityAlert
      | where TimeGenerated >= ago(1d)
      | where DisplayName has "<Name>"
      | summarize Day_Count=count()
      by DisplayName
      );
      let Week = (
      SecurityAlert
      | where TimeGenerated >= ago(7d)
      | where DisplayName has "<Name>"
      | summarize Week_Count=count()
      by DisplayName
      );
      let Month = (
      SecurityAlert
      | where TimeGenerated >= ago(31d)
      | where DisplayName has "<Name>"
      | summarize Month_Count=count()
      by DisplayName
      );
      let Year = (
      SecurityAlert
      | where TimeGenerated >= ago(365d)
      | where DisplayName has "<Name>"
      | summarize Year_Count=count()
      by DisplayName
      );
      let DW = (
      Day
      | join kind = inner (
      Week
      ) on DisplayName
      );
      let WM = (
      DW
      | join kind = inner (
      Month
      ) on DisplayName
      );
      let MY = (
      WM
      | join kind = inner (
      Year
      ) on DisplayName
      );
      MY
      | project-away DisplayName1
      , DisplayName2
      , DisplayName3


      Is there a way I can do this without recursive tabling?