Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community

I am trying to create a watchlist that displays specific alerts from different business units

Copper Contributor

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

20 Replies

@caitlin2250 

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. 

@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?

Hi Eric. Thank you very much for the direction. Much appreciated
Hi Gary, Thanks for confirming that the code looks correct. I do not get any error. Yes it's just missing data that I am finding difficult to add on to the code. I would like for example for the Watchlist to include severs also not from the same business unit and be able to identify specific alerts from those servers also distinctively. At the moment I have only been able pull out information from servers within one business unit with this Watchlist and it does not meet the requirement, Hope I have explained it better. Thank you very much for taking the time to respond. Very much appreciated
Hi Caitlin
Just thinking out loud here:
Could it be possible that the computers in your watchlist are lowercase and in the logs are uppercase (or a mix). And there for now having a match and not showing the data.
Could a tolower() be a solution when setting up the join?
Hi Luis thanks for the reply, maybe I wasn’t with my explanation
I need assistance in developing a KQL query that would add the extra property for Heartbeats.
The additional property would be the Team that manages the VM.
Let's say I have 100 VMs managed by five teams.
When I run the Heartbeat KQL query, I would like to see the team or business unit name that manages the VM.
My approach is to use a Watchlist with two columns. One column would be the Computer, and another column would be the name of the Team or business unit that owns the VM. So I would have five teams in the column Team or business unit and the list of computers assigned to each Team or business unit.
The Computer will be a search key.
The query I am planning to use is
Heartbeat
| lookup kind=leftouter _GetWatchlist('UNIT')
on $left.Computer == $right.SearchKey
After the query is complete, I would like to save it as a function.
Is it the right approach, or you could suggest something better?
Many Thanks
Caitlin

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

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

@caitlin2250 

This should work.   I did note I couldn't use "Team" as a column name but "Team_" worked. 
Screenshot 2021-06-30 083631.png

Watchlist used:
Screenshot 2021-06-30 083738.png

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

 

Hi Clive
Thank you very much for the code provided. That is very helpful indeed. I will add information need from my end to the code and feedback. Much appreciated
Hello Louis,
Thank you for the suggestion regarding case insensitive. I am afraid I am new to KQL so how do I incorporate that in this query below

Heartbeat
| lookup kind=leftouter _GetWatchlist('DEV1')
on $left.Computer == $right.SearchKey
| project UNIT, Computer

Will really appreciate you help with it. Look forward to hearing from you.
Thanks
Caitlin

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

Hello Jbub_Arbala, Thanks for the reply I have tried your query and the only problem I am seeing in the result is that it is only devices with uppercase naming in the result of the query. What I am looking for is to get both whether the name of the server is in uppercase or lowercase.
Thanks and look forward to hearing from you
Caitlin

@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_


 

Hello Clive thanks for the reply. Could you please explain what this query does so that I can understand and tweak it to suit what I am trying to achieve. Look forward to hearing from you

Kind regards
Caitlin

@caitlin2250 

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.  

Screenshot 2021-07-04 090433.png
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
Screenshot 2021-07-04 091655.png

Hello Luis. Your example works for me for my requirement due to simplicity but can you please explain what each line of code does so that I can have a clear understanding of it. Look forward to hearing from you. Thanks Caitlin

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.