Hi All
I have a room mailbox in office365 and i want to get the information of how many meetings were booked for one month.
i am using the below two queries but i am not getting the output. can anyone help me correct the syntax.
Query1
index=mail sourcetype="ms:o365:reporting:messagetrace"
| rename RecipientAddress as email
| lookup meeting_rooms email
| search NOT subject=Canceled:*
| bucket _time span=1h
| stats dc(email) as invited values(name) as room_name values(email) as invitees by SenderAddress subject _time
| rename subject as meeting_subject
| search room_name="room1@mydomain.com"
| lookup meeting_rooms name as room_name outputnew email as room_email
| stats sum(invited) as room_total by room_name room_email
Query2
index=mail sourcetype="ms:o365:reporting:messagetrace"
| rename RecipientAddress as email
| lookup meeting_rooms email
| search NOT subject=Canceled:*
| bucket _time span=1h
| stats dc(email) as invited values(name) as room_name values(email) as invitees by SenderAddress subject _time
| rename subject as meeting_subject
| search room_name="Room1"
| stats sum(invited) as room_total by room_name
The SPL syntax looks good. We need to see some sample events to know if the SPL is correct or not.
One thing that will result in no output is if either the room_name or room_email field is null. When that happens, the stats command cannot group results so it returns nothing.