All Apps and Add-ons

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

dolejh76
Communicator

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?

0 Karma

dolejh76
Communicator

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

0 Karma

Raghav2384
Motivator

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
Communicator

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!!

0 Karma

Raghav2384
Motivator

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

0 Karma

dolejh76
Communicator

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

0 Karma

dolejh76
Communicator

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...