- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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...
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Glad to help!
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you! I've not used the EPOCH before. I'm trying this one now and I'll report back if it works.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
try this
| inputlookup AND value=x
…..
| search creationDate="1/*/2020"
this should list all values in lookuptable for all Jan Month.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?