Search code examples
azurekqlazure-sentinel

KQL query construction with parameters and default values

I have a table defined in log analytics workspace that has src_ip, dst_ip, protocol and dst_port columns.

I am defining a workbook in Sentinel that allows user to input certain values for columns stated above. User may or may not enter values for these parameters.

This means, my kql query should account for default values in case, user does not provide input.

let src_ip_input = '{src_ip}';
let dst_ip_input = '{dst_ip}';
let dst_port_input = '{dst_port}';
let protocol_input = '{protocol}';

let src_ip_flag = iff(isempty(src_ip_input) , '*', format_ipv4(src_ip_input));
let dst_ip_flag = iff(isempty(dst_ip_input) , '*', format_ipv4(dst_ip_input));
let dst_port_flag = iff(isempty(dst_port_input), -1, toint(dst_port_input));
let protocol_flag = iff(isempty(protocol_input), -1, toint(protocol_input));

table('Flow_Events_CL')
| where src_ip == ?
| summarize count() by bin(TimeGenerated, 1h)

How to complete this query such that all 4 parameters are accounted for in case when user provides and may not provide?

In case user does not provide inputs for any fields stated above, the behavior should be return all rows else filter based on what user has provided.

I tried a query like the below but noticed that iff only accepts scalar values,

let flow_events = iff(({dst_port} == "All"), table('Flow_Events_CL'), table('Flow_Events_CL') | where dst_port in~ ({dst_port}))
flow_events
| summarize traffic_count = count() by dst_port
| order by traffic_count
| limit 5

Then I tried,

let flow_count_for_all_ports = (port:int = 65536) {
    table('Flow_Events_CL')
    | summarize traffic_count = count() by dst_port
    | order by traffic_count
    | limit 5
};

let flow_count_for_specific_ports = (port:int) {
    table('Flow_Events_CL')
    | where dst_port == port
    | summarize traffic_count = count() by dst_port
    | order by traffic_count
    | limit 5
};

let dst_port_selected = '{dst_port}';


let dst_port_flag = iff((isempty(dst_port_selected)), -1 , toint(dst_port_selected)); // if -1, return for all ports, else specific port

union (flow_count_for_all_ports() | where dst_port_flag == -1), (flow_count_for_specific_ports(toint(dst_port_selected)) | where dst_port_flag > 0);

This may work for one input, but when 4 input params are considered, then I cannot write a query like this.

let src_ip_input = '{src_ip}';
let dst_ip_input = '{dst_ip}';
let dst_port_input = '{dst_port}';
let protocol_input = '{protocol}';

let src_ip_flag = isempty(src_ip_input);
let dst_ip_flag = isempty(dst_ip_input);
let dst_port_flag = isempty(dst_port_input); 
let protocol_flag = isempty(protocol_input); 

table('Flow_Events_CL')
| where (src_ip_flag or src_ip == format_ipv4(src_ip_input)) and (dst_ip_flag or  dst_ip == format_ipv4(dst_ip_input)) and (dst_port_flag or dst_port == toint(dst_port_input)) and (protocol_flag or proto == toint(protocol_input)) 
//| project src_ip, dst_ip, dst_port, proto
| summarize count() by bin(TimeGenerated, 1h)

This seemed to work for me, is this correct?

Solution

  • If your method worked and gave you the results you wanted then it worked :)

    Personally I would have approached it like this.

    let src_ip_input = ''; //Example IP, eg 192.168.1.10, leave blank for any
let dst_ip_input = '172.16.1.10'; //Example IP, eg 172.16.1.10, leave blank for any
let dst_port_input = '80'; //Example port, eg 80, leave blank for any
let protocol_input = ''; //Example protocol, eg TCP, leave blank for any
let Faux_Flow_Events_CL = materialize(
range TimeGenerated from ago(2h) to ago(1h) step 1s //Generate random testing data
| extend src_ip = strcat('192.168.1.', toint(rand(255))) //Only 192.168.1.0/24 in testing
| extend dst_ip = strcat('172.16.1.', toint(rand(255))) //Only 172.16.1.0/24 in testing
| extend dst_port = iif(rand(2) == 1, '443', '80') //Only HTTP and HTTPS in testing
| extend proto = iif(rand(2) == 1, 'TCP', 'UDP') //Only TCP or UDP in testing
);
Faux_Flow_Events_CL
| where src_ip == src_ip_input or isempty(src_ip_input)
| where dst_ip == dst_ip_input or isempty(dst_ip_input)
| where dst_port == dst_port_input or isempty(dst_port_input)
| where proto == protocol_input or isempty(protocol_input)
    TimeGenerated src_ip dst_ip dst_port proto
    2024-04-13T06:26:12.9148302Z 192.168.1.99 172.16.1.10 80 UDP
    2024-04-13T06:31:19.9148302Z 192.168.1.24 172.16.1.10 80 TCP
    2024-04-13T06:46:26.9148302Z 192.168.1.251 172.16.1.10 80 UDP
    2024-04-13T06:48:34.9148302Z 192.168.1.213 172.16.1.10 80 TCP
    2024-04-13T06:51:33.9148302Z 192.168.1.73 172.16.1.10 80 UDP
    2024-04-13T07:01:18.9148302Z 192.168.1.134 172.16.1.10 80 TCP
    2024-04-13T07:11:14.9148302Z 192.168.1.109 172.16.1.10 80 UDP