Splunk Search

Count of locations on unique days

dyarashus
Loves-to-Learn

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.

Labels (3)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
`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
Loves-to-Learn

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...