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 k...

KQL

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

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?

Forum Discussion

Getting a count from a watchlist off one or more columns