Has anyone figured out a query to show the last time a user was last logged onto any system from which IDR collects logs?
Hey @gschneider, quick question, does your organization have managed IDR (MDR) or just IDR?
The reason I’m asking is if you have MDR, you can look under Endpoint Agent - Agent Beacons in log search and do something like:
- where(lastLoggedInUser!=“null”)groupby(hostname, lastLoggedInUser)
If you don’t have MDR and only IDR, then you could also look under Asset Authentications and run something like:
- where(logon_type!=NETWORK AND result=SUCCESS AND destination_user!=/.service.|.svc./i)groupby(destination_asset, destination_user, timestamp)
- where(logon_type!=NETWORK AND result=SUCCESS AND destination_user NOT ICONTAINS-ANY [service, svc])groupby(destination_asset, destination_user, timestamp)
You might need to tweak the query a bit, but the above is attempting to exclude any service accounts and just show you actual end users.
Thanks, that helps a lot. The trouble I’m having, and I didn’t explain my problems very well, is that I’m looking for users who haven’t been seen for 14 days or more.
Hi @gschneider, the difficulty with using log search for such an approach is that what you are looking for is the lack of events. Effectively an inactivity alert for specific users. What is the use case for this query/investigation if I might ask?
@david_smith, I’m looking for users who haven’t touched any of our platforms (VPN, O365, or any other platforms from which IDR ingests logs. Use case: I need to identify users who are out on extended leave, and thus haven’t logged onto our VPN or into any SaaS platforms. Backstory: our HR dept keeps forgetting to give me the names of people on extended leave.
The only real way to achieve what you are after today would be to write a script against the Rest API to search the logs programatically. You could issue a query to groupby all users in the last 30 days for example, and map those users to the timestamp of the greatest value, from there you could sort the results by timestamp and trim off any users who have logged in in the last 14 days.
If you are comfortable with scripting and our Rest API see more here InsightIDR REST API | InsightIDR Documentation
You would use a combination of the Log API to fetch relevant logs and the query API to issue the queries.
@gschneider just following up on what David is recommending here, if you do go the scripting route, I would humbly suggest checking out InsightIDR4Py, a Python script that I wrote that can be imported as a module and used to execute exactly these types of queries. It simplifies the process of executing log search queries significantly, so you don’t have to worry about finding Log Set GUIDs, handling paginated responses, or checking the progress of polling queries.