Alerting

How to report grouping by user sorting by time for each user?

dieguiariel
Path Finder

Hi! i have a report for users login in from different countries in the last 24 hours:

index="accesslogs" sourcetype=apilogs authIP=* | iplocation authIP | stats count(authIP) AS ipCount by authDato, authIP, _time, Country, City, | where ipCount>=1 | eval _time=strftime(_time, "%Y-%m-%d %H:%M:%S") | table authDato, Country, City, authIP, _time | dedup authIP | eventstats dc(Country) as COUNT by authDato | where COUNT > 1 

The results has this format:

authdato | Country | City | authIP | _time 

246423 | Paraguay | Asuncion | xxx.xxx.xxx.xxx | 2023-03-07  12:10:06

246423| Brazil | Sao Paulo | xxx.xxx.xxx.xxx | 2023-03-07  10:10:34

246423 | Argentina | Caseros | xxx.xxx.xxx.xxx | 2023-03-06  10:10:34

1004629 | Paraguay | Asuncion | xxx.xxx.xxx.xxx | 2023-03-07  10:05:34

1004629 | Argentina | Tucuman | xxx.xxx.xxx.xxx | 2023-03-06  16:34:06

1422262 | Paraguay | Asuncion | xxx.xxx.xxx.xxx | 2023-03-07  12:42:32

1422262 | Brazil | Uberlandia | xxx.xxx.xxx.xxx | 2023-03-07  09:46:32

the goal is to detect compromised accounts (user A cant connect on the same day from different countries).

This report is sorted by authDato (its our username). 

I need to sort it by _time (newest event first), but i need the report still grouped by authdato:

Like:

1422262 | Paraguay | Asuncion | xxx.xxx.xxx.xxx | 2023-03-07  12:42:32

1422262 | Brazil | Uberlandia | xxx.xxx.xxx.xxx | 2023-03-07  09:46:32

246423 | Paraguay | Asuncion | xxx.xxx.xxx.xxx | 2023-03-07  12:10:06

246423| Brazil | Sao Paulo | xxx.xxx.xxx.xxx | 2023-03-07  10:10:34

246423 | Argentina | Caseros | xxx.xxx.xxx.xxx | 2023-03-06  10:10:34

1004629 | Paraguay | Asuncion | xxx.xxx.xxx.xxx | 2023-03-07  10:05:34

1004629 | Argentina | Tucuman | xxx.xxx.xxx.xxx | 2023-03-06  16:34:06

 

 

 

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| eventstats max(_time) as last by authdato
| sort 0 - last _time

View solution in original post

0 Karma

dieguiariel
Path Finder

Thanks! tried with 

| sort 0 authdato -_time

but still is sorted first by authdato.

The goal is to get first, the last login event (lets say is for user X) and then all the other login events for X user.

After the next recent login for other user (lets say Y user) all of his logins.

After the next recent login for other user (lets say A user) and all of his logins. 

Kind of the lastlog command from linux but  for each user show the other logins too.

Example

user | time

X     12:23:00

X      11:45:34

X      10:34:36

then show the next most recent user login

Y       12:08:45

Y        11:40:06

Y        11:05:56

after the next login for A user

A        12:01:33

A        11:50:32

A         09:34:00

and so on.

      

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| eventstats max(_time) as last by authdato
| sort 0 - last _time
0 Karma

dieguiariel
Path Finder

thanks! this worked perfectly!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| sort 0 authdato -_time

You shouldn't need to format _time with the eval as it will automatically be displayed in your local format.

If this isn't the format you want, you could use fieldformat to change the way it is displayed whilst leaving it a numeric which can be sorted.

| fieldformat _time=strftime(_time, "%Y-%m-%d %H:%M:%S")
Get Updates on the Splunk Community!

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

🍂 Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...