Strftime and strptime not working for EPOCH timestamp extracted from field

[fraserj]

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.

[kamlesh_vaghela]

@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" 

[fraserj]

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

[to4kawa]

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.

[fraserj]

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

[to4kawa]

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