Count of locations on unique days

[dyarashus]

I think this is a pretty basic question, but I'd appreciate some help with it.  I'm trying to produce an exportable, email-able report (CSV or Excel) of remote worker locations that shows how often people are logging in from each state.  I'd like to show a count of how many logins per IP, per UserId,  per state on unique days but I haven't been able to find a way to get that count into a table.

The basic query I'm using is this:

`m365_default_index` sourcetype="o365:management:activity" Workload=AzureActiveDirectory Operation=UserLoggedIn
| iplocation ClientIP
| search Country="United States"
| eval Time=strftime(_time, "%Y-%m-%d %H:%M:%S")
| rename Region AS State UserId AS User ClientIP AS "Client IP"
| fields "State", "User", "Client IP", "Time"
| table State User Time "Client IP"
| sort State, User, - Time

But it returns more rows than I can output, and I'd rather have a count than just all the rows of logins.

If I could figure out how to add a count so that the table's output was more like:
State User Client IP "Count of logins on unique days"

where I could apply a search filter on some count > x and I could turn that into an email-able report, that would be ideal.

[ITWhisperer]

`m365_default_index` sourcetype="o365:management:activity" Workload=AzureActiveDirectory Operation=UserLoggedIn
| iplocation ClientIP
| search Country="United States"
| bin span=1d _time
| stats count by _time ClientIP UserId Region
| rename Region AS State UserId AS User ClientIP AS "Client IP"
| sort State, User, - _time

[dyarashus]

That's definitely getting me closer to something workable, @ITWhisperer , and I thank you for that.

But that gives me an event count per day, and what I'm trying to figure out is how to count the number of unique days someone logged in from somewhere (not the number of logins themselves). For example, I'd like to be able to filter out someone passing through a state on travel vs. someone who's there every day by filtering on the count being > 5 days.

Here's what I've evolved the query to so far, but the final stats count in "Logins" seems to be showing me the total login events, not the count of unique days with login events -- that's my sticking point so far.

`m365_default_index` sourcetype="o365:management:activity" Workload=AzureActiveDirectory Operation=UserLoggedIn
| iplocation ClientIP
| search Country="United States" AND NOT Region=""
| bin span=1d _time
| stats count by _time ClientIP UserId Region
| eval Date=strftime(_time, "%Y-%m-%d")
| rename Region AS State UserId AS User ClientIP AS "Client IP"
| sort State, User, "Client IP", - Date
| table State, User, "Client IP", Date, count
| stats count AS Logins by State User
| where Logins > 5

It feels like I need something like:
| stats count(values(Date)) AS Logins by State User
near the end.

... but that doesn't work. Any further advice on counting days in the results to include in an output table would be welcome. Thanks.

[ITWhisperer]

This stats gives you a result for each day, for each clientip, userid and region

| bin span=1d _time
| stats count by _time ClientIP UserId Region

This stats effectively counts the number of clientip used on each day by userid (User) and region (State)

| stats count AS Logins by State User

If you just want the count of days, you could try

| stats dc(_time) by UserId