topic Re: How do I convert epochtime = -1 to a human readable format? in Splunk Search

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

lball — Thu, 06 Dec 2018 23:10:07 GMT

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.

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

Richfez — Fri, 07 Dec 2018 04:05:05 GMT

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

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

lball — Fri, 07 Dec 2018 15:09:10 GMT

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!

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

petom — Fri, 07 Dec 2018 15:28:38 GMT

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