Forum Discussion

Knighthell's avatar
Knighthell
Copper Contributor
May 05, 2024

KQL how to save query as functions witch parameters ?

Hi 

 I have written this query, and I saved it as a function and entered the parameters as shown in the figure. I need to understand where I am going wrong. If I call the function and input the parameters, the result is an error.

 

let login = (startDate: datetime, endDate: datetime, accountNameFilter: string = "", groupName: string = "") {
SigninLogs
| where TimeGenerated between (startDate .. endDate)
| extend user_1 = tolower(UserPrincipalName)
| join kind=inner (
    IdentityInfo 
    | extend user_2 = tolower(AccountUPN)
  )
  on $left.user_1 == $right.user_2
| where (ResultType == "0" or ConditionalAccessStatus has "success")
| mv-expand GroupMembership 
| where GroupMembership has groupName
| project-away user_1, user_2
| distinct AccountDisplayName, TimeGenerated, AppDisplayName
| extend Day = startofday(TimeGenerated)
| extend TimeBin = bin(TimeGenerated, 1h)
| summarize last_login = max(TimeGenerated), first_login = min(TimeGenerated), day = dcount(Day) by AccountDisplayName
| where (accountNameFilter == "" or AccountDisplayName has accountNameFilter)
| order by last_login desc
| render barchart kind=unstacked
};
login

 

 

 

 

 
 

 

 

KQL
  • User9864's avatar
    User9864
    Copper Contributor
    May 17, 2024

    HelloKnighthell ,

    You are calling your function without any parameters, and your login() function has some mandatory parameters.

    You need to call login() with the parameters saved in the GUI for it to work:

    // inner function
let login = (startDate: datetime, endDate: datetime, accountNameFilter: string = "", groupName: string = "") {
SigninLogs
| where TimeGenerated between (startDate .. endDate)
| extend user_1 = tolower(UserPrincipalName)
| join kind=inner (
    IdentityInfo 
    | extend user_2 = tolower(AccountUPN)
  )
  on $left.user_1 == $right.user_2
| where (ResultType == "0" or ConditionalAccessStatus has "success")
| mv-expand GroupMembership 
| where GroupMembership has groupName
| project-away user_1, user_2
| distinct AccountDisplayName, TimeGenerated, AppDisplayName
| extend Day = startofday(TimeGenerated)
| extend TimeBin = bin(TimeGenerated, 1h)
| summarize last_login = max(TimeGenerated), first_login = min(TimeGenerated), day = dcount(Day) by AccountDisplayName
| where (accountNameFilter == "" or AccountDisplayName has accountNameFilter)
| order by last_login desc
| render barchart kind=unstacked
};
// main
// args called below should matches params saved in the GUI
login(startDate, endDate, accountNameFilter, groupName)

     

Resources