How do I convert epochtime = -1 to a human readable format?

[lball]

I am creating a dashboard for Tenable results and some entries have a Patch Publication Date value of -1. I'm having trouble getting the search to output results because I can't get the -1 value to resolve to a human readable date. Here is a copy of the search:

index=tenable | eval PatchPubDate=strftime(patchPubDate,"%Y-%m-%d") | stats by riskFactor,severity.id,pluginID,description,solution,pluginText,PatchPubDate,ip

Any ideas of how to get the results to show when the date is -1? When I exclude the -1 values from the search all other epoch time values convert as desired.

[petom]

Just replace the "invalid" timestamp value with something else. E.g.:

index=tenable 
| eval PatchPubDate=if(patchPubDate=-1, "unknown", strftime(patchPubDate,"%Y-%m-%d")) 
| stats by riskFactor,severity.id,pluginID,description,solution,pluginText,PatchPubDate,ip

or if you still need it to be a timestamp, then:

index=tenable 
| eval PatchPubDate=if(patchPubDate=-1, "1970-01-01", strftime(patchPubDate,"%Y-%m-%d")) 
| stats by riskFactor,severity.id,pluginID,description,solution,pluginText,PatchPubDate,ip

[Richfez · Accepted solution]

You could modify the field PatchPubDate before you get to there.

index=tenable 
| eval PatchPubDate=if(PatchPubDate==-1, "1970-07-17", PatchPubDate) 
| eval PatchPubDate=-strftime(patchPubDate,"%Y-%m-%d") 
| stats by riskFactor,severity.id,pluginID,description,solution,pluginText,PatchPubDate,ip

See if that works. It'll make it be my birthday. You can't go much older, because the epoch isn't valid too much earlier than this.

In your display code (e.g. that you don't have here - after all that you could do another eval and if it's 1970-07-17 you could change it to printing out a string like "invalid" if you wanted.)

[lball]

It worked!. I did have to convert the date value in line 2 to epochtime to get the final result to figure in correctly, but it worked like a charm otherwise. Thanks!