Forum Discussion
kusto query user login count by day/date range
Hello,
I am working on a requirement to do the following:
- Need count of an user how many times they logged in by day for all 30 days or date range
i have come up with a query to query off of the pageViews table..How do I modify this query so that when an entry gets added to the pageViews table and url = "/" then that is counted as a login. curently, I am assuming that a session along with a userAuthenticated Id was enough but when i login and logout of app, once i log in again it is not creating a new entry in the table.//count of logins by unique sessions and date 1 a
pageViews
| extend DateOnly = format_datetime(timestamp, 'yyyy-MM-dd')
| where timestamp between (datetime(2024-01-30) .. datetime(2024-02-02))
|project user_AuthenticatedId, timestamp, session_Id, DateOnly
| distinct session_Id, user_AuthenticatedId, DateOnly
//where user_AuthenticatedId == '00479872'
|summarize logins = count() by user_AuthenticatedId, endofday(todatetime(DateOnly))
| extend CentralTime = datetime_utc_to_local(Column1, "US/Central")
|project user_AuthenticatedId, CentralTime = format_datetime(CentralTime, 'yyyy-MM-dd'), logins
| where user_AuthenticatedId != '' and user_AuthenticatedId !startswith "T"
| order by CentralTime, logins desc
Thanks in advance.
2 Replies
- Clive_WatsonBronze ContributorHello, you have said: "but when i login and logout of app, once i log in again it is not creating a new entry in the table."
If there is no data for the re-login in the Table then you won't be able to query for it. Or have I misunderstood? - sameersalunkeCopper ContributorSigninLogs | where UserId="UserObjectId" | where TimeGenerated >=startdate and TimeGenerated <= enddate | summarize count()