Azure Analytics Query to group the date and events

Question

We have a huge data of analytics from past few months & I am looking for a query to group the events in the order of date.

Answer 1

Hello @Amrutha ,
Thanks for the ask and using the Microsoft Q&A platform .

I am assuming that you are refering to log analytics data . The below query shoulld help

SecurityEvent  
| project EventData, EventID, TimeGenerated   
| sort by TimeGenerated desc   

Update 9/15

You should use something like this ( please update your fields accordingly .

SecurityEvent
|project EventName=Account,Date=TimeGenerated
|summarize Occurance=count() by EventName,format_datetime(Date,'MM/dd/yyyy')
|order by Date desc

I think your query should be

customEvents
|project EventName=name ,Date=TimeGenerated
| where timestamp > startofday(datetime("2021-05-01")) and timestamp < endofday(datetime("2021-08-26"))
| summarize Occurance=count() by EventName,format_datetime(Date,'MM/dd/yyyy')
| order by Occurance desc
| where name <> "StartServiceLog"
| where name <> "PushInstallationLog"
| where name <> "StartSessionLog"
| where name == "Navigate The Hospital - Search Opened"
or name == "Navigate The Hospital - Welcome Alert - OK Clicked"
or name == "Navigate The Hospital - FloorChange Button Clicked"
or name == "Navigate The Hospital - Get Directions Clicked"
or name == "Selected Room is "

You can learn about more on how to query the data here

Please do let me know how it goes .
Thanks
Himanshu
Please do consider clicking on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Answer 2

Hi @HimanshuSinha-msft

I have tried the query but it did not give me any results, How can I get in touch with someone to get the event query that I am currently looking for?

Answer 3

Hi @HimanshuSinha-msft , Here is my Analytics Query

customEvents
| where timestamp > startofday(datetime("2021-05-01")) and timestamp < endofday(datetime("2021-08-26"))
| summarize count(user_Id) by name
| order by count_user_Id desc
| where name <> "StartServiceLog"
| where name <> "PushInstallationLog"
| where name <> "StartSessionLog"
| where name == "Navigate The Hospital - Search Opened"
or name == "Navigate The Hospital - Welcome Alert - OK Clicked"
or name == "Navigate The Hospital - FloorChange Button Clicked"
or name == "Navigate The Hospital - Get Directions Clicked"
or name == "Selected Room is "

How can I get these in the required format?

Answer 4

I am looking for this format:

Date Event Name Occurrence
08/27/2021 NavigateXXXXXXXX-Search 30
08/27/2021 NavigateXXXXXXXX-Quick 20