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 "Start" and * has "processing" and * has "PortalUserLoggedInEvent"
| 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
Answer
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
