finding value which having a date which is older than 30 days from the current date

[akashjohn]

Hi Team,

I am trying to create a query which can provide a table like structure of data. The data I am looking forward is to fetch the count of password expired accounts for last week time period.

The user logins are integrated with AD login. could you please let us know how can we find the query?

As a basic experiments we have ran a query (sourcetype="ActiveDirectory*" AND "cn=" unixHomeDirectory "*expire".) The output which we are getting is.

---

06/27/2016 14:46:50.993
dcName=PRDADDSMGMT0004.mgt.mydomain.com
admonEventType=Update
Names:
objectCategory=CN=Person,CN=Schema,CN=Configuration,DC=mgt,DC=mydomain,DC=com
userPrincipalName=user1@mgt.mydomain.com
name=user1
displayName=user1
distinguishedName=CN=user1,OU=Privileged,OU=Users,OU=Accounts,DC=mgt,DC=mydomain,DC=com
cn=user1
Object Details:
sAMAccountType=805306368
sAMAccountName=user1
logonCount=467
accountExpires=0
objectSid=S-1-5-21-344696771-4041470829-2997178021-1001
primaryGroupID=513
pwdLastSet=11:12.18 AM, Fri 06/24/2016
lastLogon=09:42.13 PM, Wed 06/22/2016
badPasswordTime=12:59.07 PM, Wed 06/01/2016
countryCode=0
codePage=0
badPwdCount=0
userAccountControl=512
objectGUID=54a2af3f-0398-4a76-a889-458b47f3f82f
whenChanged=02:46.50 PM, Mon 06/27/2016
whenCreated=06:45.55 PM, Mon 07/20/2015
objectClass=top|person|organizationalPerson|user
Event Details:
uSNChanged=1958847
uSNCreated=12449
instanceType=4
Additional Details:
loginShell=/bin/bash
unixHomeDirectory=/home/user1
gidNumber=1222011322
uidNumber=1222011322
lastLogonTimestamp=02:46.32 PM, Mon 06/27/2016
dSCorePropagationData=20160224213721.0Z|20160118162554.0Z|20160118162021.0Z|20160114230040.0Z|16010101000000.0Z
adminCount=1
memberOf=CN=yioiyncAdmins,CN=Users,DC=mgt,DC=mydomain,DC=com|CN=pasu_sudoall,OU=Roles,OU=Security,OU=Groups,DC=mgt,DC=mydomain,DC=com|CN=PRDSNOW001_Administrators,OU=Resources,OU=Security,OU=Groups,DC=mgt,DC=mydomain,DC=com|CN=Enterprise Admins,CN=Users,DC=mgt,DC=mydomain,DC=com|CN=Schema Admins,CN=Users,DC=mgt,DC=mydomain,DC=com|CN=Domain Admins,CN=Users,DC=mgt,DC=mydomain,DC=com|CN=Users,CN=Builtin,DC=mgt,DC=mydomain,DC=com

---

In the output there is a value pwdLastSet=11:12.18 AM, Fri 06/24/2016, We were thinking like to find the a value older than 30 days (password expiry limit) older than current date and generate stats table.

Please let us know how can we find a solution on this?

Thanks,
Akash John

[sundareshr · Accepted solution]

Like this

your base search | eval lastSet=strptime(pwdLastSet, "%H:%M.%S %p, %a %m/%d/%Y") | where now()>relative_time(lastSet, "+30d")

[akashjohn]

Wow great!!! the query is seems to be working fine. Thanks a lot for your support.

[sundareshr]

@akashjohn Please mark the question as answered to close it out.

[akashjohn]

Sure, I have made it as accepted.