Date conversion needed

[mbasharat]

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

[tread_splunk]

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.

[PickleRick]

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.

[mbasharat]

Hi @ PickleRick,

Yes, for real searches, I use fields 🙂

[ITWhisperer]

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.

[venkatasri · Accepted solution]

Hi @mbasharat 

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!