How to search for users who haven't signed in to an app over the last 30 days.

[stegray93]

Hello,

I'm trying to figure out the search that would be needed to find any users who haven't logged in to an application over the last 30 days.

I don't really know what to give as an example as I've tried multiple different ways and none of them work.

So basically the core of the search is below.

source=okta:event action.message="Sign-in successful"

How do I then make this only show me results of users who haven't created a sign-in successful event in the last 30 days? Then table them as user.

[sundareshr · Accepted solution]

Let's assume you have a field called userid. Try this search.

source=okta:event action.message="Sign-in successful" | stats latest(_time) as LastLogin by userid | where LastLogin<relative_time(now(), "-30d")

[stegray93]

thanks it's seem to of worked. Do you know how I can change the LastLogin field to look more user friendly? It's currently displaying it as a load of numbers, can I get that show the date of the last time they logged in?

[woodcock]

Add this:

... | fieldformat LastLogin = strftime(LastLogin, "%m/%d/%Y %H:%M:%S")

[stegray93]

Awesome, thanks guys you've made my day 😄