Simulating SQL functions

[ldeakm]

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!

[ldeakm]

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

[dwaddle]

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

[dwaddle]

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

[ldeakm]

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.

[joshd]

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

[Takajian]

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