Splunk Search

Date conversion needed

mbasharat
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)
0 Karma
1 Solution

venkatasri
SplunkTrust
SplunkTrust

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!

 

View solution in original post

0 Karma

tread_splunk
Splunk Employee
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.

0 Karma

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

0 Karma

mbasharat
Contributor

Hi @ PickleRick,

Yes, for real searches, I use fields 🙂

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
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.

0 Karma

venkatasri
SplunkTrust
SplunkTrust

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!

 

0 Karma
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 ...