Saving and using tabular functions in Sentinel

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3141570%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3ESaving%20and%20using%20tabular%20functions%20in%20Sentinel%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3141570%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EHi%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20define%20a%20function%20which%20takes%20in%20a%20list%20of%20IP%20ranges%2C%20then%20uses%20a%20watchlist%20of%20IP%20ranges%20and%20returns%20only%20the%20distinct%20IPs%20which%20belong%20to%20one%20of%20these%20ranges.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EHere%20is%20the%20body%3A%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3Elet%20Watchlist%20%3D%20_GetWatchlist(%22guestNetworkRanges%22)%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%7C%20extend%20crossJoin1%20%3D%201%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3Elet%20isInGuestRange%20%3D%20(distinctIPs%3A(IP%3Astring))%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%7B%26nbsp%3B%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BWatchlist%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%20join(distinctIPs%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%20extend%20crossJoin2%20%3D%201)%20on%20%24left.crossJoin1%20%3D%3D%20%24right.crossJoin2%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%20extend%20isGuest%20%3D%20ipv4_is_in_range(IP%2C%20IPrange)%20%2F%2F%20bool%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%7C%20where%20isGuest%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%7D%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EisInGuestRange(distinctIPs)%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI%20am%20unsure%20how%20to%20save%20the%20tabular%20function%20as%20the%20parameter%20is%20tabular%20(which%20is%20not%20one%20of%20the%20offered%20options%3F)%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F346540i64FB2A24525A5C68%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22misstaek_0-1644393856713.png%22%20alt%3D%22%5C%26quot%3Bmisstaek_0-1644393856713.png%5C%26quot%3B%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20tried%20without%20any%20parameter%20(which%20complains%20that%20the%20function%20is%20not%20taking%20any%20parameter%20when%20used%20in%20a%20query)%2C%20putting%20distinctIP%20as%20a%20dynamic%20parameter%2C%20or%20just%20saying%20that%20IP%20(row%20of%20distinctIP)%20is%20a%20string%20(both%20solution%20return%20the%20following%20error)%3A%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F346541i262427846BDEDDE3%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22misstaek_1-1644394024915.png%22%20alt%3D%22%5C%26quot%3Bmisstaek_1-1644394024915.png%5C%26quot%3B%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20could%20find%20documentations%20where%20it%20is%20shown%20how%20to%20define%20a%20tabular%20function%2C%20but%20not%20how%20to%20save%20it%2C%20which%20is%20where%20I%20feel%20the%20problem%20is%20in%20this%20case.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EAny%20advice%20on%20how%20to%20handle%20tabular%20function%20saving%3F%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3141570%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EMicrosoft%20Sentinel%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
New Contributor

Hi

I am trying to define a function which takes in a list of IP ranges, then uses a watchlist of IP ranges and returns only the distinct IPs which belong to one of these ranges.

 

Here is the body:

 

let Watchlist = _GetWatchlist("guestNetworkRanges")

| extend crossJoin1 = 1;

let isInGuestRange = (distinctIPs:(IP:string))

{  

    Watchlist 

    | join(distinctIPs

    | extend crossJoin2 = 1) on $left.crossJoin1 == $right.crossJoin2 

    | extend isGuest = ipv4_is_in_range(IP, IPrange) // bool

    | where isGuest

}

;

isInGuestRange(distinctIPs)

 

I am unsure how to save the tabular function as the parameter is tabular (which is not one of the offered options?)

misstaek_0-1644393856713.png

I tried without any parameter (which complains that the function is not taking any parameter when used in a query), putting distinctIP as a dynamic parameter, or just saying that IP (row of distinctIP) is a string (both solution return the following error):

misstaek_1-1644394024915.png

I could find documentations where it is shown how to define a tabular function, but not how to save it, which is where I feel the problem is in this case.

 

Any advice on how to handle tabular function saving?

 

Thanks in advance!

1 Reply

It appears you cannot save a function that accepts tabular parameters,
they can only be defined within the query itself.

You should look at the "Watch listing by IP ranges: the mv-apply operator" section of the blog below, it shows how you could compare a bunch of IPs to a bunch of IP ranges.
https://techcommunity.microsoft.com/t5/microsoft-sentinel-blog/approximate-partial-and-combined-look...

 

This is another example of how to implement mv-apply. (the base logic is pretty much the same)

https://techcommunity.microsoft.com/t5/microsoft-sentinel-blog/using-external-data-sources-to-enrich...

 

I have used this successfully to filter IPs that match any of the Azure service tags ranges.