Getting Data In

How do you filter events by date range per account_name in a lookup file from date fields in the same lookup file?

kvandegrift
New Member

I have a lookup table that consists of the follow fields: Account_Name, Name, Start Date, Return Date. I want to search for windows event log activity for account names listed in the lookup table that are >= Start Date and < Return Date. I have been able to search for logon events using the Account_Name in the lookup as a filter, but how do I use the dates in the lookup file to filter for the event dates also? Search that works for filtering by lookup Account_Name is:

inputlookup employee_vac | fields Account_Name sourcetype="WinEventLog:Security"
eventtype="msad-nt6-successful-user-logons"
EventCode="4624" Account_Name="*"
| eval date=strftime(_time, "%Y-%m-%d") | eval Account_Name=lower(Account_Name)
| table date, "Start Date", "Return Date", Account_Name, Workstation_Name, src_ip, Logon_Type

This search uses the lookup to only return windows logon events for accounts listed in the lookup file, but does not return any additional fields ("Start Date", "Return Date") from the lookup table.

0 Karma

lguinn2
Legend

In the subsearch, you specified the field to return (Account_Name). Therefore, that is the only field that was retrieved from the lookup table. Also, you can't return other fields at that point without messing up your search. Assuming that the fields are named "Start Date" and "Return Date", and that these fields have dates in the form of Year-Month-Day, try the following

[inputlookup employee_vac | fields Account_Name] (index=winserver_events OR index=win-dc-security) 
sourcetype="WinEventLog:Security" eventtype="msad-nt6-successful-user-logons" EventCode="4624" Account_Name="*" 
| lookup employee_vac Account_Name
| eval start_date = strptime('Start Date',"%Y-%m-%d")
| eval return_date = strptime('Return Date',"%Y-%m-%d")
| where _time > start_date AND _time < return_date
| eval date=strftime(_time, "%Y-%m-%d") | eval Account_Name=lower(Account_Name) 
| table date, "Start Date", "Return Date", Account_Name, Workstation_Name, src_ip, Logon_Type

Note that this solution (as well as the original one) implies that there is only one listing in the lookup table per Account_Name. If this is not true, you should probably change to a time-based lookup. Here is a link to the documentation.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...