Splunk Search

Strftime and strptime not working for EPOCH timestamp extracted from field

fraserj
New Member

Hi, I know a similar question has been asked a million times, but I've tried all the solutions and nothing is working so I'm at my wits end with this.

Essentially, my search is just finding AD accounts that are still active but their expiry date has passed the current time of the search (expired but still active accounts). My search query is this:

index=activedirectory sourcetype="ad:identity"  NOT(expiry="(never)") category=normal
| eval time_now = strftime(_time, "%d/%m/%Y %H:%M:%S")
| eval expiry_readable = strftime(expiry, "%d/%m/%Y %H:%M:%S")
| table time_now, identity, nick, email, expiry_readable, category
| rename time_now as "Current Time", nick as "Name", identity as "Username", email as "Email", category as "Account Status"
| dedup Username, Name, Email
| sort -expiry_readable

I've tried strptime as well but nothing is working. My table statistics just show all blanks for expiry_readable which is really infuriating as I need the eval command to work so I can further filter down results based on that timestamp.
FYI, the field in my events is called 'expiry' by default from the logs, and it returns timestamps such as: "2019-12-12T16:00:00Z"
Also, I have tried things such as:

where(expiry <= now()) and where(expiry <= _time), where(expiry <= time_now) but none of that works either (to filter down results based on the time I need)...

Any solutions would be appreciated.

Tags (3)
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@fraserj

Can you please try this?

index=activedirectory sourcetype="ad:identity" NOT(expiry="(never)") category=normal 
| dedup identity,nick, email
| eval time_now = strftime(_time, "%d/%m/%Y %H:%M:%S") 
| eval expiry_epoch=strptime(expiry,"%Y-%m-%dT%H:%M:%SZ")
| eval expiry_readable = strftime(expiry_epoch, "%d/%m/%Y %H:%M:%S") 
| sort - expiry_epoch
| eval  comments = "Apply where condition here using expiry_epoch field. like where expiry_epoch <= now()
| table time_now, identity, nick, email, expiry_readable, category 
| rename time_now as "Current Time", nick as "Name", identity as "Username", email as "Email", category as "Account Status" 
0 Karma

fraserj
New Member

No luck. 'Error in 'eval' command: Fields cannot be assigned a boolean result. Instead, try if([bool expr], [expr], [expr]).

0 Karma

to4kawa
Ultra Champion

UPDATE:

index=activedirectory sourcetype="ad:identity" NOT (expiry="(never)") category=normal
| eval expiry=strptime(expiry,"%Y-%m-%dT%H:%M:%SZ")
| stats max(_time) as _time max(expiry) as expiry values(category) as category by nick, identity , email 
| sort - expiry 
| rename COMMENT as "Compare times and extract those whose expiration date is past the current time."
| where _time >= expiry
| rename COMMENT as "Change field names and times for readability" 
| eval time_now = strftime(_time, "%d/%m/%Y %H:%M:%S") 
| eval expiry_readable = strftime(expiry, "%d/%m/%Y %H:%M:%S") 
| table time_now, expiry_readable, identity, nick, email, category 
| rename time_now as "Current Time", nick as "Name", identity as "Username", email as "Email", category as "Account Status"

Hi, @fraserj
In your query, time_now and expiry_readable are both STRINGS
therefore, you can't compare time values.

Changes for readability should be made last.

0 Karma

fraserj
New Member

Unfortunately this didn't produce any statistic results at all. No luck.

0 Karma

to4kawa
Ultra Champion

| eval expiry_readable = strftime(expiry, "%d/%m/%Y %H:%M:%S")
I was misunderstanding because of this query.
Updated the answer.

Strftime is a command to change UNIX time to a character string.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...