Splunk Search

How to update certain time fields of lookup table without overwriting old table entries?

Explorer

I'm currently trying to work with a csv lookup table that has the following columns. Susp_IP, Ticket_num, date_last_seen. This is a table that has a list of bad IP's we have seen over time that we then run an hourly alert that will alert if there has been one of these IP's showing up in any of our logs within the last hour. I am trying to write this so that the date_last_seen field will be updated with the _time field that is showing up for that most recent log. This list should then be updated showing the list of all suspicious IP's where the date_last_seen is the most recent date we have seen them in logs. I seem to have the issue of overwriting my old csv, even though I have the append=t flag in the inputlookup field.

I have spent quite a bit of time trying to implement other solutions regarding this similar task, but have had no luck. What I would like to do is have this search be the one that updates the lookup table and then have a different search use the lookup table within an alert that will provide more data.

Here is what I am currently running for a search to try and solve this.

sourcetype=blah [|inputlookup append=t suspect_list.csv | table Susp_IP, Ticket_num, date_last_seen | rename Susp_IP as src_ip ] | search Ticket_num=* | rename src_ip as Susp_IP | eval date_last_seen=_time | table Susp_IP, Ticket_num, date_last_seen | dedup Susp_IP | outputlookup suspect_list.csv
0 Karma
1 Solution

Revered Legend

Try like this

sourcetype=blah [| inputlookup suspect_list=csv | table Susp_IP | rename Susp_IP as src_ip ]
| search Ticket_num=* | rename src_ip as Susp_IP | eval date_last_seen=_time  | table Susp_IP, Ticket_num, date_last_seen 
|inputlookup append=t suspect_list.csv
| dedup Susp_IP
| outputlookup suspect_list.csv

View solution in original post

0 Karma

Revered Legend

Try like this

sourcetype=blah [| inputlookup suspect_list=csv | table Susp_IP | rename Susp_IP as src_ip ]
| search Ticket_num=* | rename src_ip as Susp_IP | eval date_last_seen=_time  | table Susp_IP, Ticket_num, date_last_seen 
|inputlookup append=t suspect_list.csv
| dedup Susp_IP
| outputlookup suspect_list.csv

View solution in original post

0 Karma

Explorer

ahh yes this seems to work for me, thanks! I never thought of inputting the table twice.

0 Karma