How to sort by date for date field that is not the timestamp?

[user93]

Hello,

I have a items with creation dates where we are tracking the events on the item. Once a month, I need to be able to look at only events for items created in the last month. The creation data is a meta-data value in a lookup table and it includes time in the format.

1/15/2020 22:20

I want to run an automated report that gives a list of all items with a creation date for the current month where the "Created On" field. I do not actually need the events in this scenario so the base search is simply
|inputlookup AND value=x
.....
| help me filter by date

[user93]

Thank you!

|inputlookup table.csv
|eval created_epoch=strptime(created,"%m/%d/%Y")
|eval time_diff=now()-created_epoch
|table id,title,created,created_epoch,time_diff
|search time_diff<=2592000

[acfecondo75 · Accepted solution]

@user93,

You'll need to convert that timestamp into EPOCH format using strptime. Then subtract the creation time from the current time and return results where the difference is less than the number of seconds in a month:

| inputlookup yourlookup
| eval creation_date_epoch=strptime(creation_date,"%m/%d/%Y %T") 
| eval time_diff=now()-creation_date_epoch 
| search time_diff<=2592000

Just plug in your lookup name and change creation_date to whatever the field in your lookup with that data is called. You could add an extra days worth of seconds to the time_diff line to avoid missing anything.

[user93]

Ok, so this worked mostly. For what ever reason, it did not like %T. I only need to go as find as the day so after I dropped %T and made the Y lower case, it worked to give me the value.

However, the 1 month epoch value was then |search time_diff<=1576522166. But when I do this I get negative results once I go into the previous year.

This is where I am at:

|inputlookup table.csv
|eval created_epoch=strptime(Created,"%m/%d/%y")
|eval time_diff=now()-created_epoch
|table ID,title,created_epoch,time_diff
|search time_diff<=1576522166

[acfecondo75]

try this:

| inputlookup yourlookup
 | eval creation_date_epoch=strptime(creation_date,"%m/%d/%Y %H:%M") 
 | eval time_diff=now()-creation_date_epoch 
 | search time_diff<=2592000 
 | eval days_since_creation=time_diff/60/60/24

Your timestamp didn't include seconds which is why it didn't like the %T (%T is for time that is in this format: 10:12:11, but yours was just 10:12).

It's important that is done correctly or none of the calculations will work. EPOCH is a number value for time. It's the number of seconds that have passed since January 1st 1970. If the conversion isn't done properly from the string value to the number, then the SPL that searches on time_diff is no longer reliable. I noticed in your code sample you changed the time_diff to 1576522166 which would limit your results to KOs created in the past ~1800 days...

[acfecondo75]

Glad to help!

[user93]

Thank you, I was able to add in the hour and minute and I have it now exact using the epoch time difference.

Thank you so much!

[user93]

Ok, I fixed it. When I used a lowercase y, it did not work because it was giving me all of the years for that month. The final answer is like so:

|inputlookup table.csv
|eval created_epoch=strptime(created,"%m/%d/%Y")
|eval time_diff=now()-created_epoch
|table id,title,created,created_epoch,time_diff
|search time_diff<=2592000

[user93]

Thank you! I've not used the EPOCH before. I'm trying this one now and I'll report back if it works.

[Anantha123]

try this

| inputlookup AND value=x
…..
| search creationDate="1/*/2020"

this should list all values in lookuptable for all Jan Month.

[user93]

Hi Anatha,

Thank you. This works for January, but this is not what I need. I need to be able to automate this completely every month using a report. So next month, I want the scheduled report to run for 2/*/2020.

What I actually want more specifically is all items between the date range 07 JAN to 07 FEB. and so on. How can I do this?