question

AmruthaTamanam avatar image
0 Votes"
AmruthaTamanam asked HimanshuSinha-MSFT commented

Azure Analytics Query to group the date and events

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.

azure-monitorazure-stream-analytics
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @AmruthaTamanam,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu

0 Votes 0 ·

Hello @AmruthaTamanam,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu

0 Votes 0 ·
AmruthaTamanam avatar image AmruthaTamanam HimanshuSinha-MSFT ·

Sorry @HimanshuSinha-MSFT I did not get any updates regd this solutions posted here,

The Query did not help me for the results I was looking for.

Can you help me?

0 Votes 0 ·
HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered HimanshuSinha-MSFT commented

Hello @AmruthaTamanam ,
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


132438-image.png


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




image.png (48.3 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @AmruthaTamanam ,
It was great to know that you were able to get to a resolution . We expect you to keep using this forum and also motivate others to do that same . You can always help other community members by answering to their queries .
Thanks
Himanshu

0 Votes 0 ·
AmruthaTamanam avatar image
0 Votes"
AmruthaTamanam answered HimanshuSinha-MSFT commented

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?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @AmruthaTamanam ,
You mentioned that the the query did not returned any data , that means that you do not have any data in the "SecurityEvent" . I was just trying to give you the idea of the query . Please let me know which table are you trying to query and update the query accordingly .

YourTableName
// Add the columns you want to see
| project EventData, EventID, TimeGenerated
//Sort by the time Generated
| sort by TimeGenerated desc

Please do let me know how it goes .
Thanks
Himanshu

0 Votes 0 ·
AmruthaTamanam avatar image
0 Votes"
AmruthaTamanam answered

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?


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmruthaTamanam avatar image
0 Votes"
AmruthaTamanam answered HimanshuSinha-MSFT commented

I am looking for this format:

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@AmruthaTamanam , I have updated the answer above , hope that heps .


0 Votes 0 ·