Help with query combining lookups from two sources (i.e. cisco:asa and dshield)

[mlachnietpeckha]

Hello all, my apologies for a question that is probably documented and I am just not figuring out. I am trying to take data from Cisco firewall logs, filter out internal->internal traffic and blocked connections, and then look up the external IP addresses against the Dshield database that gets downloaded nightly.

Below are two queries that work independently. What I am trying to do is take the results in field dest_ip from the first query and then look them up in a method similar to the second query to see if I can find active malware. A sort by bytes or something would be handy on the output but not essential. Is this possible?

1) Works for finding the traffic I want:
sourcetype="cisco:asa" AND NOT (src_ip="10.0.0.1/8" AND dest_ip="10.0.0.0/8") AND NOT (src_port=53 OR dest_port=53) bytes_in>0

2) Works for looking up offender IP addresses (en masse)
index=dshield | stats sum(Reports) AS TotalReports by SourceIP | sort 10 -TotalReports

Thank you kindly,
Mark Lachniet

[mlachnietpeckha]

Thanks Ahmed, you are awesome.

I have this partially working. I know that it is doing a lookup in the CSV file when I try to get results because it was complaining quite a bit when I had a mismatched field title in the CSV.

However, neither of those fields are appearing in my searches. Here is what I have:

/data/splunk/etc/users/admin/search/lookups/dshield_processed.csv:

SourceIP,TotalReports
109.169.45.231,494447
91.218.160.70,346386
193.107.17.70,313808
[...]

/data/splunk/etc/users/admin/search/local/transforms.conf:

[dshield_processed]
filename = dshield_processed.csv

/data/splunk/etc/users/admin/search/local/props.conf:

[cisco:asa]
LOOKUP-http_auto_lookup = dshield_processed SourceIP AS dest_ip OUTPUTNEW

When I do a search such as the one above, the fields SourceIP and TotalReports are not shown on the list of selectable columns, although other data is returned. A query to create a table such as the below also shows no data in those fields:

sourcetype="cisco:asa" AND NOT (src_ip="10.0.0.1/8" AND dest_ip="10.0.0.0/8") AND NOT (src_port=53 OR dest_port=53) bytes_in>0 | table bytes_in src_ip src_port SourceIP dest_port TotalReports

Somehow those two new fields aren't being "added" to my list of possible fields. So it is close but I am still doing something wrong.

By the way, I added a couple IP addresses manually to the CSV file with some values so that I would get back some results, so I don't think its an issue of just not getting any hits. Unless perhaps it is not re-reading the CSV file? (I have been restarting the service after I edit it).

Thank you again for taking the time to help with this. I will write this up when it is working and share it back as a mini whitepaper or something.

[aakwah]

It is my pleasure ..

Actually I got the lines added to configuration files after I created the lookup from web interface, so I think the best way is to create it from we interface to avoid any missing configurations.

from web interface Settings -> Lookups

Lookup table files .. to upload the csv file
then Lookup definitions
then Automatic lookups
Lookup input fields:
on the left hand side SourceIp and on right hand side dest_ip

Regards,
Ahmed

[aakwah]

I think Splunk lookup fetaure can help in this situation, first you need to generate csv includes the contents from the second query,

index=dshield | stats sum(Reports) AS TotalReports by SourceIP | sort 10 -TotalReports

We can run the search query from the linux command line and save output to a file as per the following

/opt/splunk/bin/splunk search "index=dshield | stats sum(Reports) AS TotalReports by SourceIP | sort 10 -TotalReports earliest="01/21/2015:01:00:00" latest="01/22/2015:01:00:00" " -auth admin:P@ssw0rd > temp.csv

Modify the file temp.csv by linux tools to have the format you want, assume the final dshield_results.csv file we have is like that:

cat dshield_results.csv
SourceIp,TotalReports
10.10.1.2,77
10.10.1.3,87

The above steps can be automated in script to save dshield_results.csv in the following location:

/opt/splunk/etc/users/admin/search/lookups/

Then we will define the lookup by adding the following lines to the file /opt/splunk/etc/users/admin/search/local/transforms.conf

vi /opt/splunk/etc/users/admin/search/local/transforms.conf

[dshield_lookup]
filename = dshield_results.csv

Then we need to automate the lookup by adding the folloiwng to the file /opt/splunk/etc/users/admin/search/local/props.conf

vi /opt/splunk/etc/users/admin/search/local/props.conf (in the first line put the sourcetype you want to assign the lookup to it)

[cisco:asa]
LOOKUP-http_auto_lookup = dshield_lookup SourceIp AS dest_ip OUTPUTNEW

Now you should have two fields SourceIp and TotalReports when you search sourcetype cisco:asa

Regards,
Ahmed Elakwah