topic Re: Date conversion needed in Splunk Search

Date conversion needed

mbasharat — Tue, 25 Jan 2022 02:45:38 GMT

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

Re: Date conversion needed

venkatasri — Tue, 25 Jan 2022 05:04:12 GMT

Hi @mbasharat

you could try something like below

---

appreciate a vote if it helps!

Re: Date conversion needed

ITWhisperer — Tue, 25 Jan 2022 09:56:09 GMT

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.

Re: Date conversion needed

PickleRick — Tue, 25 Jan 2022 08:29:47 GMT

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.

Re: Date conversion needed

tread_splunk — Tue, 25 Jan 2022 09:54:06 GMT

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.

Re: Date conversion needed

mbasharat — Tue, 25 Jan 2022 13:23:51 GMT

Hi @ PickleRick,

Yes, for real searches, I use fields 🙂