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

[W.Edmeades]

Hi Eluis,

Thank you for your response, but perhaps I should've been clearer. I wish to collate a list of users who haven't logged in within the previous 30 days - which is to say they have logged in before, just not since Sept 2 (if we're starting from today), so there'll definitely be logs for it. That being said, I wish for my aforementioned code to have the following requirements (I'm just lost as to how to put it together):

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

[eluis]

Hi @W.Edmeades 

XQL queries can get quite complex. 

Im gonna offer a different approach to solve this using the API get-users

https://docs-cortex.paloaltonetworks.com/r/Cortex-Platform-APIs/Get-Users?contentId=I7Cjp0wJsX6rWft4...

You can get the 
last_logged_in type of data is integer = Timestamp of when the user last logged in.

With the appropriate logic in your script it should be possible to get the data your are looking for. 

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