Jun 26 2021 03:21 PM
here is the query below. I would like to be able to determine which specific business unit server an alert was generated into Azure sentinel but I am unable to create a tag that includes a watchlist that provides the expected result. Please help
Heartbeat
| lookup kind=leftouter _GetWatchlist('MBSFQDN_01')
on $left.Computer == $right.SearchKey
| project UNIT, Computer
Jun 28 2021 10:02 AM - edited Jun 28 2021 10:03 AM
Hello! You've posted your question in the Tech Community Discussion space, which is intended for discussion around the Tech Community website itself, not product questions. I'm moving your question to the Azure Sentinel space- please post Azure Sentinel questions here in the future.
Jun 28 2021 12:44 PM
@caitlin2250 The code looks correct, what is the error you are getting or is it just missing data? You have all the fields you require in the watchlist, right?
Jun 29 2021 12:18 AM
Jun 29 2021 12:32 AM
Jun 29 2021 02:08 AM
Jun 29 2021 03:02 AM
Jun 29 2021 04:07 AM
@caitlin2250 Your code looks correct. If you want to save it as a function, you can easily do that through the Logs UI. There is no parameter (aka filter) so you will get the full list each time if that is your intention. Otherwise it looks like it will work just fine.
I would think about @LouisMastelinck comment about case sensitivity though. Granted using "=~" takes more processing so I would double check all the entries in the Heartbeat table to make sure they are in the case you are expecting.
Jun 29 2021 04:09 AM
@caitlin2250 I don't see any reason why you would only get the one business unit returned. If you could paste some of the entries from your watchlist (changing the data to protect your machine names of course), it may help.
Jun 30 2021 12:47 AM
@caitlin2250
This should work. I did note I couldn't use "Team" as a column name but "Team_" worked.
Watchlist used:
or
Heartbeat
| lookup kind=leftouter _GetWatchlist('UNIT')
on $left.Computer == $right.SearchKey
| summarize thoseInaTeam=make_set_if(Computer, isnotempty(Team_)), dcountif(Computer, isnotempty(Team_)), thoseNotInaTeam=make_set_if(Computer, isempty(Team_)), dcountif(Computer, isempty(Team_)) by Team_
Team_ | thoseInaTeam | dcountif_Computer | thoseNotInaTeam | dcountif_Computer1 |
---|---|---|---|---|
[] | 0 | ["TASARINT201201.fabrikamltd.co.uk","THAMLOCFKOM19.fabrikamltd.co.uk","TASARINT201601.fabrikamltd.co.uk","THAMLOCFKARC01.fabrikamltd.co.uk","THAMLOCPFKWVM01.fabrikamltd.co.uk","THAMLOCFKVMM19.fabrikamltd.co.uk","GENETEC201601.fabrikamltd.co.uk","RDS2019.fabrikamltd.co.uk","ATACENTER.fabrikamltd.co.uk","THAMLOCPFKWVM04.fabrikamltd.co.uk","VMRUBUNTU01","GENETEC201602.fabrikamltd.co.uk","WIN10MS-0.fabrikamltd.co.uk","WIN7.fabrikamltd.co.uk","VMW2019VM01.fabrikamltd.co.uk","powlo-signage","powloexpmegan","powloexpmeganc"] | 18 | |
DEV | ["thamlocfkubu01","THAMUKSOBS01"] | 2 | [] | 0 |
AKS_DEV | ["aks-agentpool-40245457-vmss000009","aks-agentpool-40245457-vmss00000a"] | 2 | [] | 0 |
AKS_PROD | ["aks-agentpool-40245457-vmss000001","aks-agentpool-40245457-vmss000000"] | 2 | [] | 0 |
PROD | ["vmrcentos01"] | 1 | [] | 0 |
Jun 30 2021 01:24 AM
Jun 30 2021 10:46 AM
Jun 30 2021 06:05 PM - edited Jun 30 2021 06:07 PM
Just as an example you could do something like below to change the Computer column to all upper case. (ComputerUpper just being what ever you want to name it.) You would need to to the same with your right column whatever that is if it also has lower case letters. You can use toupper or tolower, as long as both columns end up uniform.
Replacing the == with =~ wont work as the join only supports equalities.
Heartbeat
| extend ComputerUpper = toupper (Computer)
| lookup kind=leftouter _GetWatchlist('DEV1')
on $left.ComputerUpper == $right.SearchKey
| project UNIT, ComputerUpper
Jul 01 2021 03:34 AM
Jul 01 2021 03:57 AM
@caitlin2250
union - is case insensitive
let watchListUnit = (_GetWatchlist('UNIT') | project Team_, Computer);
watchListUnit
| union
(
Heartbeat
| distinct Computer
)
| summarize thoseInaTeam=make_set_if(Computer, isnotempty(Team_)), dcountif(Computer, isnotempty(Team_)), thoseNotInaTeam=make_set_if(Computer, isempty(Team_)), dcountif(Computer, isempty(Team_)) by Team_
Jul 03 2021 02:07 AM
Jul 04 2021 01:21 AM
I'm using a Union rather than a join or a lookup (like in the previous examples), which skips the need to have case equality (where both the data in the Watchlist and the Computer have to be upper or lowercase for a Join/lookup to match them).
The query is essentially the same (you can replace the summarize line with whatever suits your use case).
This server highlighted in the red box, is all upper case in the Heartbeat table, but in the unit watchlist I made the "tham" characters lowercase to prove that you can union a mix of upper/lower cases. The server is now in the "thoseInaTeam" column as thamUKSOBS01 was matched with THAMUKSOBS01 and it was recognised as a DEV team server, regardless of its case sensitivity.
I hope this helps.
fyi, there is a KQL course and the modules from the course you can access from within the portal (the modules have lots of great examples), see below.
Also Module 7 of the Azure Sentinel training Become an Azure Sentinel Ninja: The complete level 400 training - Microsoft Tech Community
Jul 05 2021 07:21 AM
Jul 06 2021 02:33 AM
Jul 06 2021 03:18 AM
Hi @caitlin2250
I don't have your dataset but I was thinking something like this:
Heartbeat
| extend ComputerUpper = toupper (Computer)
| lookup kind=leftouter _GetWatchlist('DEV1')
on tolower($left.ComputerUpper) == tolower($right.SearchKey)
| project UNIT, ComputerUpper
Give it a try if it will accept this in your syntax.