How to format the Powershell timestamp to YYYY-MM-DD?

[dolejh76]

Trying to run some powershell scripts to pull stale computers / logons etc. I am using SA-ldap and the following queries.

| ldapsearch domain=XXX search="(objectclass=computer)" attrs="cn,lastLogonTimestamp" 
| eval ninetydays=now() - (90*86400)
| eval lastConnected=strptime(lastLogonTimestamp,"%Y-%m-%d") 
| where lastConnected < ninetydays 
| table cn,lastConnected
| rename cn to Computer,lastConnected to "Last Connected"

This gives me
computernamexxx 1411707600.000000

Another conversion attempt gave me 2015-01-09T15:09:09.814585Z

The question is, how do I format this date field? Active Directory time stamp is actually something else, but since I dont get the actual value from AD, I am not sure how to format it.

I have tried the following

| eval ADTime=lastLogonTimestamp/1000000000 
| eval lastConnected=strftime(ADTime, "%F %H:%M:%S.%3Q")

Also
| eval lastConnected=strptime(lastLogonTimestamp,”%Y-%m-%d”)
Neither of these are working correctly. Any guidance on formatting these would be helpful.

Really all I want is the date.... YYYY-MM-DD I don't want the time at all.

Side question - my understanding is that lastLogonTimeStamp is a replicated field. Does SA-ldap search just query one DC - the one I have configured. Should I be using a different attribute to get what I am looking for and focus the non replicated fields and query all DCs?

[dolejh76]

I built on your idea of removing everything else and just getting the first 10 characters. This does not seem efficient but it works....

| ldapsearch domain=XXX search="(objectclass=computer)" attrs="cn,lastLogonTimestamp" 
| eval ninetydays=now() - (90*86400)
| eval lastConnected=strptime(lastLogonTimestamp,"%Y-%m-%d") 
| where lastConnected < ninetydays 
| table cn,lastLogonTimestamp
| rename cn to Computer,lastLogonTimestamp to LastConnected 
| fieldformat LastConnected=substr(LastConnected,1,10)

Had to remove the space from "Last Connected" on the table rename as it didn't like the "quotes" in the field format string but I can live with it I guess.

Unless you have a more efficient way to do this I can live with it. Seems like rather than evaluating the field and modifying the time we are just taking it as a string and only getting first 10 characters.

John

[Raghav2384]

Please pardon me if didn't understand this correctly. I tried to convert "1411707600.000000" using the below run anywhere search

|gentimes start=-1 |eval x = "1411707600.000000"|eval Time = strftime(x,"%Y-%m-%d")|table Time and it gives me the output

2014-09-26
Is this what you're looking for?

Thanks,
Raghav

[dolejh76]

I have never used gentimes but it doesn't like it in the ldap search. I tried to use just the strftime(x,"%Y-%m-%d") and that doesn't work either.

I don't understand the difference between strftime and strptime. I believe that strptime is Data and Time so since this is the format that I am getting in the ldap query I would need to use that.

Anyone that has the ldap addon can try this and see what you are getting - perhaps you see something different than I do.. replacing domain xxx with your fqdn.

| ldapsearch domain=xxx search="(objectclass=computer)" attrs="cn,lastLogonTimestamp" 
| eval ninetydays=now() - (90*86400)
| eval lastConnected=strptime(lastLogonTimestamp,"%Y-%m-%d") 
| where lastConnected < ninetydays 
| table cn,lastConnected
| rename cn to Computer,lastConnected to "Last Connected"

Looking at the raw text of one entry it returns the following

{"lastLogonTimestamp":"2014-09-26T19:25:28.770726Z","cn":"mycomputername"}

Again if there is a better way to get stale computers in AD with Splunk I would be interested in hearing that as well. Thanks!!

[Raghav2384]

You don't need to use gentimes and start as i used that to create a adhoc entry to my splunk. Try this , use

eval lastConnected=strftime(lastlogonTimestamp,"%Y-%m-%d")

and remove everything starting where and see you get any desired output.

Thanks,
Raghav

[dolejh76]

Raghav,

First thanks for your quick response. As I mentioned though using strftime doesn't work - It results in "No Results Found" on my query.

If I do not eval my attribute at all and just use lastLogonTimestamp then I get the result of

2014-09-26T19:25:28.770726Z

[dolejh76]

See below post - I built on Raghav thought of getting rid of the tail and using fieldformat substr