How to configure a Haversine scheduled report so that a user will not appear multiple times each time the report is run?

[afarmer]

I use the haversine formula to search for users who have logged into resources within a short period of time in the last 24 hours. I have a scheduled report set to run every hour and it looks back at the past 24 hours across our authentication logs. The report works perfectly, but here is the issue.

Let's say my scheduled report will run at 9:00AM.
A particular username is shown as logging in from Atlanta, Georgia, and logging in from Paris, France, within a 5 minute time frame.
My scheduled report runs every hour, so this user will appear as logging in from both cities.

However, since my report runs every hour, this user will continue to appear each hour until the 24 hour time frame has passed. As you can tell, if I set up an alert, I'll receive many emails each hour.

What is the best way to configure the report/alert so that this user will only appear once and not every time the report runs? The alert settings don't seem to have an affect.

Thanks in advance.

[woodcock · Accepted solution]

Just add this to the end of your search:

| eventstats latest(_time) AS mostRecentTime BY user
| where mostRecentTime >= (now() - 3600)
| fields - mostRecentTime

This will keep only those groups of events where at least one of them happened in the last hour.

[afarmer]

That's it! I completely overlooked the eventstats command and it's obvious now. Thanks!

[somesoni2]

What's the current query/logic that you're using? Are you checking if a user has logged from multiple cities/resources with a specified interval?