Splunk Search

Calculate Days from current date custom date

kpavan
Path Finder

Hi All,

Need help on below query to calculate ticket age from ticket creation date with current date. Please help me to fix this query. From below query am not getting the ticket_age its giving me the blank field, but both current date and ticket creation date's are getting converted correctly. But not sure where is missing. Please help me on this.

base search
|eval Tday=now()
|eval Today=strftime(now(), "%Y-%d-%m")
|eval CREATION_DATE=strftime(strptime(TICKET_CREATION_DATE, "%Y-%m-%d %H:%M:%S"),"%Y-%m-%d")
|eval TICKET_AGE=(Today - CREATION_DATE)
|table Request_Status CREATION_DATE TICKET_AGE

Thanks!
Pavan

Tags (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi kpavan,
to calculate a difference between two dates, you have to convert them in epochtime using strptime:

base search
|eval CREATION_DATE=strptime(TICKET_CREATION_DATE, "%Y-%m-%d %H:%M:%S"), TICKET_AGE=(now()-CREATION_DATE)
|table Request_Status CREATION_DATE TICKET_AGE

Bye.
Giuseppe

View solution in original post

0 Karma

niketn
Legend

@kpavan, if you want to use string time you need to conver the fields to YYYYmmdd not YYYY-mm-dd otherwise it will be treated as string not number. You can try out the following query. In your query there is also a mistake with the two string date formats being different i.e. %Y-%m-%d and %Y-%d-%m

| makeresults
| eval TICKET_CREATION_DATE="2017-10-11 10:20:30"
| eval Today=strftime(now(), "%Y%m%d")
| eval CREATION_DATE=strftime(strptime(TICKET_CREATION_DATE, "%Y-%m-%d %H:%M:%S"),"%Y%m%d")
| eval TICKET_AGE=(Today - CREATION_DATE)

However, you are better off using strptime to convert to epoch time. You can use fieldformat to display epoch time as string time.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi kpavan,
to calculate a difference between two dates, you have to convert them in epochtime using strptime:

base search
|eval CREATION_DATE=strptime(TICKET_CREATION_DATE, "%Y-%m-%d %H:%M:%S"), TICKET_AGE=(now()-CREATION_DATE)
|table Request_Status CREATION_DATE TICKET_AGE

Bye.
Giuseppe

0 Karma

kpavan
Path Finder

Thanks Giuseppe Its working!

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...