Splunk Search

## Date conversion needed

Contributor

Hi,

I am trying to calculate age for a task. Time is in below format. What am I missing?

| makeresults

| eval Last_Checkin="2021-05-26T20:47:22Z"

| table Last_Checkin, _time

| eval Age_Days=ceiling((now()-strptime(Last_Checkin,"%Y-%m-%dT%H:%M:%S%3NZ"))/86400)

| eval CVE_Age=case(

Age_Days<30,"A_0 to 29 Days",

Age_Days>=365,"G_365 Days+",

Age_Days>=180,"F_180 to 364 Days",

Age_Days>=120,"E_120 to 179 Days",

Age_Days>=90,"D_90 to 119 Days",

Age_Days>=60,"C_60 to 89 Days",

Age_Days>=30,"B_30 to 59 Days",

0==0,"H_No Age Data")

Labels (3)

• ### rex

1 Solution
SplunkTrust

you could try something like below

``````| makeresults
| eval Last_Checkin="2021-05-26T20:47:22Z"
| table Last_Checkin, _time
| eval epoch=strptime(Last_Checkin, "%Y-%m-%dT%H:%M:%S")
| eval diff_days=ceiling((_time-epoch)/86400)``````

---

appreciate a vote if it helps!

Splunk Employee

Replace Age_Days with...

``eval Age_Days=ceiling((now()-strptime(Last_Checkin,"%Y-%m-%dT%H:%M:%SZ"))/86400)``

You are specifying %3N in your format string, which deals with a millisecond component.  Your time value in Last_Checkin doesn't have a millisecond component.

Ultra Champion

One additional remark. I know that your example uses makeresults to produce a single result but if you eventually run your search on a production data you'll probably get several resulting events.

In that case you shouldn't use table command to limit the processed fields, but either leave the events alone or use fields command. At first glance

`| table _time`

seems to work the same as

`| fields _time| fields - _raw`

But there is a huge difference. The table command is a transforming command and produces a statistical table whereas fields command works in a pipeline one event at a time.

So table is ok if you need to produce a nice looking table at the end of your processing pipeline but if you want to just limit your search to a subset of fields, do it with fields command.

For comparison - on my home splunk searching over 3.5 millions events with | fields took 58 seconds. If I switched to | table I stopped the search after few minutes and it only returned some 500k results. I think the difference would be even bigger in a distributed environment.

Contributor

Hi @ PickleRick,

Yes, for real searches, I use fields 🙂

Tags (1)
SplunkTrust

Your example timestamp doesn't have 3 digits for the milliseconds, whereas your parse string for strptime specifies 3 digits, so the timestamp does not parse, hence the failure to determine what the epoch time is.

SplunkTrust

you could try something like below

``````| makeresults
| eval Last_Checkin="2021-05-26T20:47:22Z"
| table Last_Checkin, _time
| eval epoch=strptime(Last_Checkin, "%Y-%m-%dT%H:%M:%S")
| eval diff_days=ceiling((_time-epoch)/86400)``````

---

appreciate a vote if it helps!

Get Updates on the Splunk Community!

#### Build Scalable Security While Moving to Cloud - Guide From Clayton Homes

Clayton Homes faced the increased challenge of strengthening their security posture as they went through ...

#### Mission Control | Explore the latest release of Splunk Mission Control (2.3)

We’re happy to announce the release of Mission Control 2.3 which includes several new and exciting features ...

#### Cloud Platform | Migrating your Splunk Cloud deployment to Python 3.7

Python 2.7, the last release of Python 2, reached End of Life back on January 1, 2020. As part of our larger ...