Azure Log Analytics Query for Day of the week-CodePudding

I am trying to build a graph in log analytics workspace to find out what day of the week has the most log ins.

I have this query

SigninLogs
| where ResultType ==  0
| summarize SuccessfulSigns=count() by bin(TimeGenerated,1d)

But I was wanting to do like a bar graph where you see how many log ins were on Sundays, Mondays Etc for the 90 days its querying.

CodePudding user response：

You could use dayofweek with format_timespan, something like

let Days = datatable(day_number:int, day:string)
[
1, "Monday",
2, "Tuesday",
3, "Wednesday",
4, "Thursday",
5, "Friday",
6, "Saturday",
7, "Sunday",
];

SigninLogs
| where ResultType ==  0
| summarize SuccessfulSigns=count() by day_number=toint(format_timespan(dayofweek(bin(TimeGenerated,1d)), "d"))
| join Days on day_number
| project SuccessfulSigns, day

That returns the day as integer 1=Monday, 2=Tuesday, 3=Wednesday and maps this to the datatable day_number.

day         SuccessfulSigns
Wednesday   135