[SOLVED] kql sorting on date only sunday is specific all other days unknown

kql sorting on date only sunday is specific all other days unknown

I think im doing something wrong only sunday is shown in the chart, All other days are unknown, I'm not a kql expert and don't seed what i'm doing wrong...

Hopefully someone can explain, so that I can learn something about it.

union isfuzzy=true
    availabilityResults,
    requests,
    exceptions,
    pageViews,
    traces,
    customEvents,
    dependencies
| where * has "Login"
| extend DayOfWeek = dayofweek(timestamp) 
| extend DayName = case(
    DayOfWeek == 0, "Zondag",
    DayOfWeek == 1, "Maandag",
    DayOfWeek == 2, "Dinsdag",
    DayOfWeek == 3, "Woensdag",
    DayOfWeek == 4, "Donderdag",
    DayOfWeek == 5, "Vrijdag",
    DayOfWeek == 6, "Zaterdag",
    "Onbekend"
)
| extend Day = format_datetime(timestamp, 'yyyy-MM-dd')
| summarize Login = count() by Day, DayName
| order by Day asc
| render columnchart kind=stacked

Solution

the function dayofweek returns a timespan since the last sunday, meaning for sunday you get a 0, but for all other dates, you get a timespan in days.

As you compare this timespan with == integer, the comparison fails and all other days are marked as "Onbekend".

To fix this, you can do a toint(DayOfWeek/1d), so your script should look like

union isfuzzy=true
    availabilityResults,
    requests,
    exceptions,
    pageViews,
    traces,
    customEvents,
    dependencies
| where * has "Start" and * has "processing" and * has "PortalUserLoggedInEvent"
| extend DayOfWeek = toint(dayofweek(timestamp)/1d) 
| extend DayName = case(
    DayOfWeek == 0, "Zondag",
    DayOfWeek == 1, "Maandag",
    DayOfWeek == 2, "Dinsdag",
    DayOfWeek == 3, "Woensdag",
    DayOfWeek == 4, "Donderdag",
    DayOfWeek == 5, "Vrijdag",
    DayOfWeek == 6, "Zaterdag",
    "Onbekend"
)
| extend Day = format_datetime(timestamp, 'yyyy-MM-dd')
| summarize Login = count() by Day, DayName
| order by Day asc
| render columnchart kind=stacked

See the official documentation for further details: https://learn.microsoft.com/en-us/kusto/query/day-of-week-function?view=microsoft-fabric#convert-timespan-to-integer