Splunk Enterprise

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

akashjohn
Explorer

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

Tags (1)
0 Karma
1 Solution

sundareshr
Legend

Like this

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

View solution in original post

sundareshr
Legend

Like this

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

akashjohn
Explorer

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

0 Karma

sundareshr
Legend

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

0 Karma

akashjohn
Explorer

Sure, I have made it as accepted.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...