Forum Discussion

Copper Contributor

Jun 26, 2021

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

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

caitlin2250

Copper Contributor

Jun 30, 2021

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

JBUB_Accelerynt

Brass Contributor

Jul 01, 2021

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

caitlin2250
Copper Contributor
Jul 01, 2021
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
- CliveWatson
  Former Employee
  Jul 01, 2021
  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_
  - caitlin2250
    Copper Contributor
    Jul 03, 2021
    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