XQL How to get all users that haven't logged in in the past 30 days.

[W.Edmeades]

Hi,

I'm having trouble putting together a query that'll grab me a list of users that haven't logged in within the past 30 days.

So far I've got this, but I'm not even sure if it's the right approach, so I'm just a bit stuck:

dataset = xdr_data // Using the xdr dataset
 | filter event_type = ENUM.EVENT_LOG and action_evtlog_event_id = 4624 // Filtering by windows event log and id 4624
 | alter User_Name = arrayindex(regextract(action_evtlog_message,"New Logon:\r\n.*\r\n.*?Account Name:.*?(\w.*?)\r\n"),0) // Using regextract to get just a part of the full event log message into an array, then using arrayindex to take the first item in the array
 | fields User_Name // Select all the fields to show them

| alter lastLoginEpoch = //"last Seen" column value for user
| filter lastLoginEpoch < to_timestamp(add(to_epoch(date_floor(current_time(),"d")),-2592000))

It should theoretically be possible, I just need to grab:

• a list of login timestamps for all users
• select the most recent one for each user
• remove all users who have logged in between "now" & 30 days ago.

Just not sure how to do any of that, even after consulting the documentation.

Any help would greatly be appreciated 🙂

[eluis]

Hi @W.Edmeades 

We do not collect data of things that did not happened (not logged in users). So you can not find things that did not happened on the logs. 

It is rather better to look for users that have logged in in the last 30 days. And the ones not found are the ones that did not logged in in the last 30 days. 

If you feel this has answered your query, please let us know by clicking like and on "mark this as a Solution". 

Thank you.

KR,

Luis