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

[dieguiariel]

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

[dieguiariel]

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.

[ITWhisperer · Accepted solution]

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

[dieguiariel]

thanks! this worked perfectly!

[ITWhisperer]

| 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")