Forum Discussion
I am trying to create a watchlist that displays specific alerts from different business units
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?
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
- 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, ComputerUpperGive it a try if it will accept this in your syntax.
- Thank you very much for the prompt response Louis. Can you please explain what each line of code does because if I present it to our SOC team they would ask me to explain what each line of code does as we are all new to Sentinel so I would really appreciate your help with it.
Thank you so much
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- CliveWatson
Microsoft
caitlin2250
union - is case insensitivelet 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_
