I am trying to simulate this type of date filter in splunk. Please help...
In SQL I use
select * from table where DATEADD (DD , -30 , getdate() ) > EventHappenedTimeStamp
I am simply looking for events that happened greater than 30 days ago.
Thanks!
Looks like I am on the right track here but i still cant seem to get this to work. The time format of the data field is 11/7/2011 2:14:45 PM. I am trying this query but it returns no results.
index=ad classPath="computer" | head 1 | where (strptime(other_timestamp_field,"%M/%D/%Y %H:%M:%S") < (now() - (86400 * 30))) | table cn,pwdLastSet
(A) you can always edit your original question. It "flows" better than appending an update to your question as a new answer to your question. Splunk Answers is rather unlike a web forum in that way.
(B) If pwdLastSet
is the field that has your other timestamp in it, then that is what needs to be the first argument to strptime
. Also, your strptime
format string needs to match the timestamp format of the string. Otherwise it doesn't work. See my updated answer.
So, you're trying to do a search where you're comparing the value of some OTHER timestamp field (other than the event's actual timestamp) against current time? Easiest thing to do is use time_t
values, similar to:
sourcetype=foo | where (strptime(other_timestamp_field,"%Y%m%d %H:%M:%S") < (now() - (86400 * 30)))
We're taking advantage of strptime
to parse the other timestamp field into a time_t
. From there, now()
represents the current time of when the search is ran. And there's 86,400 seconds in a day.
If I understand your requirement correctly, this should work pretty well.
UPDATE
With some more assumptions, like your timestamp field is formatted as 11/7/2011 2:14:45 PM
and is named pwdLastSet
index=ad classPath="computer" | head 1 | where (strptime(pwdLastSet,"%M/%D/%Y %I:%M:%S %p") < (now() - (86400 * 30))) | table cn,pwdLastSet
One of the things that make conversions between SQL and SPLUNK so difficult is that we tend to use the same terms but they mean very different things. I should have been more specific.
I am trying to simulate this type of date filter in splunk. Please help...
In SQL I use
select * from table where DATEADD (DD , -30 , getdate() ) > SomeTimeValueFromTheEvent
I am simply looking for data that is contained in the event (not the indexed time but time data in the event) so I can report on it. A great example is filtering out active directory records on the Password Last Set value. “30 days ago”
Thanks!
----------------------------------Update--------------------------------------------------------
The new query below does not seem to work either. The items I am using are out of box discovery functionality of SPLUNK. The data source is Active Directory. On the computer object there is a attribute called pwdLastSet. I am simply trying to filter for computer objects that have reset there passwords in the last 30 days.
just run whatever search it is you have and use latest=-30d@d ... this will only return results that are older than 30 days.
example:
search host=myhost sessionid=AAAA1111BBBB2222 latest=-30d@d
I think you can specify the date very easily by splunk. Please take look at following manual.
http://docs.splunk.com/Documentation/Splunk/latest/User/ChangeTheTimeRangeOfYourSearch