Help Wanted: How to Identify Dormant Accounts using Stats Search

Question

Hey Google Cloud Community -- appreciate any help your able to provide in advance here.

Put simply -- I’d like to create a stats search that identifies when an account has not logged in for over 30 days.

I’d like to use the metadata.product_event_type = 4624 (windows successful logon) field and basically show how long ago each target.user.userid generates a 4624 event - then filter on traffic that is over 30 days old.

I have tried a few stats searches unsuccessfully. I realize there is also a preconfigured dashboard that shows Dormant Accounts but the output it is producing is not accurate.

Thanks

jstoner · Accepted Answer

How about something like this? I commented out some extra fields I had around and I used the unselect to get rid of columns used in computation but not display.

metadata.product_event_type = "4624"
target.user.userid = $user
match:
    $user
outcome:
    //$count = count($user)
    //$earliest = timestamp.get_timestamp(min(metadata.event_timestamp.seconds))
    $latest = timestamp.get_timestamp(max(metadata.event_timestamp.seconds))
    $current_time = timestamp.get_timestamp(max(timestamp.current_seconds()))
    //$first_seen = min(metadata.event_timestamp.seconds)
    $last_seen = max(metadata.event_timestamp.seconds)
    $now = max(timestamp.current_seconds())
    $delta = $now - $last_seen
    $rounded_days = math.floor($delta / 86400)
condition:
    $delta > 2592000 //30 days in seconds
    //$delta > 259200 //3 days in seconds
order:
    $delta desc
unselect:
    $now, $last_seen, $current_time

russell_pfeifer · Answer

​@jstonerper usual - you are the man John - thank you very much - exactly what I was looking for

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded