I found this in a search:
hxxps://www.splunk.com/blog/2014/02/10/which-servers-are-inactive.html
It is old but it describes exactly what I am trying to do but looking at user accounts. I tried using the eval commands with my ldapsearch but I do not get any results. I think I just don't understand what format lastLogonTimestamp is stored in.
Here is my attempt to apply the information from the article:
| ldapsearch basedn="OU=MyOU,DC=my,DC=domain,DC=com" scope="sub" search="(objectClass=user)" attrs="cn,lastLogonTimestamp"
| eval llt=strptime(lastLogonTimestamp,"%Y-%m-%dT%H:%M:%S.%QZ")
| eval inactiveTime=now() - llt
| table cn,lastLogonTimestamp,llt,lltAge
When I run the search, lltAge is blank.
What am I misunderstanding?
I found another article and it gives me something more readable.
hxxps://answers.splunk.com/answers/307865/converting-lastlogontimestamp-to-readable-date-and.html
| eval llt=strftime(strptime(lastLogonTimestamp,"%Y-%m-%dT%H:%M:%S.%QZ"),"%Y/%m/%d %T %Z")
| eval lltAge=now() - llt
I attempting to eventually do a where clause with lltAge>= 30 days or some value.
Thanks in advance.
Hi @s0mar,
The first search example contains an error - the fields lltAge is never actually populated, so it can't show you any value.
As for your requirement - finding accouts which did not login for the last 30 days:
| ldapsearch basedn="OU=MyOU,DC=my,DC=domain,DC=com" scope="sub" search="(objectClass=user)" attrs="cn,lastLogonTimestamp"
| eval llt=strptime(lastLogonTimestamp,"%Y-%m-%dT%H:%M:%S.%QZ")
| where llt < relative_time(now(),"-30d")
| table cn, llt
| fieldformat llt = strftime(llt,"%Y-%m-%d %H:%M:%S")
This should give you the desired results.
Hi @s0mar,
The first search example contains an error - the fields lltAge is never actually populated, so it can't show you any value.
As for your requirement - finding accouts which did not login for the last 30 days:
| ldapsearch basedn="OU=MyOU,DC=my,DC=domain,DC=com" scope="sub" search="(objectClass=user)" attrs="cn,lastLogonTimestamp"
| eval llt=strptime(lastLogonTimestamp,"%Y-%m-%dT%H:%M:%S.%QZ")
| where llt < relative_time(now(),"-30d")
| table cn, llt
| fieldformat llt = strftime(llt,"%Y-%m-%d %H:%M:%S")
This should give you the desired results.
I am adding a comment here as well, so the information may be consistant in one answer thread.
If you want to understand what your search does, it is always a good idea, to create as many fields as possible in the beginning, and narrowing it down to your final result going forward. So my above query could be enhanced like this:
| ldapsearch basedn="OU=MyOU,DC=my,DC=domain,DC=com" scope="sub" search="(objectClass=user)" attrs="cn,lastLogonTimestamp"
| eval llt=strptime(lastLogonTimestamp,"%Y-%m-%dT%H:%M:%S.%QZ")
| eval lltAge = now()-llt
| where llt < relative_time(now(),"-30d")
| table cn, lastLogonTimestamp, llt, lltAge
| fieldformat llt = strftime(llt,"%Y-%m-%d %H:%M:%S")
| fieldformat lltAge = tostring(lltAge, "duration")
Explanation:
1. Get information from AD
2. convert lastLogonTimestamp to UNIX time <= be careful that the format is correct, double check if llt
is empty!
3. calculate delta time of last logon
4. select only entries where delta is greater than 30 days (could be done differently, but lltAge
is basically not needed
5. display all fields in a table
6. convert the field llt
to a more readable format without changing its actual value (consult manual for fieldformat
for details)
7. convert lltAge
to a formatted duration, just like before
I hope this helps your understanding.
Yes, that is what I did and then read some documentation. Thanks for the answer!
Sorry, it was late and I copied from another search I was testing. My first attempt should have been an eval for lltAge and NOT inactiveTime. That line should have been this:
| eval lltAge=now() - llt
Since llt is formatted, why does that not calculate?
@DMohn,
Thanks! I'll have to research to understand what you proposed. So 2 things, how can test to see what this my eval is trying to do, specifically lltAge that is not populated? I would like to perform that calculation so that I can output it as part of my table, what am I missing there?
One way is always to show all fields in your table. Going back to the original query:
| ldapsearch basedn="OU=MyOU,DC=my,DC=domain,DC=com" scope="sub" search="(objectClass=user)" attrs="cn,lastLogonTimestamp"
| eval llt=strptime(lastLogonTimestamp,"%Y-%m-%dT%H:%M:%S.%QZ")
| eval lltAge=now() - llt
| table cn,lastLogonTimestamp,llt,lltAge
Here you should see all 4 fields populated, the last two (llt
and lltAge
) showing a Unix timestamp. If this is not the case, you have to check why. Is llt
extracted correctly? Try narrowing down where you are missing a calculation.