Forum Discussion

LukeI91's avatar
LukeI91
Copper Contributor
Jun 11, 2021

Partial lookup and case using watchlist

Hi,

 

I'm new to KQL and I cannot wrap my head around this. Maybe I have the wrong approach.

 

We have prefixes in our device names which contains the office location of the device. So xxBE*, would be the Belgium office, xxES* would be the Spain office etc.

 

I'm now trying to make a KQL query where the office name gets appended to the DeviceLogonEvents. I have been able to do that without watchlist using the case operator

| extend Office = case(DeviceName contains "hu", "Hungary", DeviceName contains "jp"...

but this gets really long, really quickly.

 

I'm wondering if there is an easier way to do this using watchlist, but I cannot figure it out.

I currently have a watchlist with 2 columns, the prefixes and the office names. From what I'm reading online, I need to do a join or a lookup. I can't do a join currently, because I only have the prefixes and not the full device names. And I have absolutely no idea how to do the lookup.

 

Can anyone nudge me on the right track? Is watchlists even the way to do this?

2 Replies

  • GaryBushey's avatar
    GaryBushey
    Bronze Contributor
    Jun 11, 2021

    LukeI91 Can you extract the prefixes out of the DeviceName using something like

    | extract prefix = substring(DeviceName,0,4)

    and then do a join on that and the data you have in the watchlist?   Are all the prefixes the same length?

    • LukeI91's avatar
      LukeI91
      Copper Contributor
      Jun 14, 2021
      Hi Gary,

      That is a great suggestion, thank you!
      There is only 1 prefix that is 1 character longer but for now I can use your trick twice. Thanks a lot!