lookup

It's a pretty popular search command and it is used in all sorts of situations. Below are some really cool searches that use lookup along with other search commands.

Detect Machines with High Threatscore

index=<replace> | stats count by src_ip dst_ip dst_port protocol | lookup threatscore clientip as dst_ip | sort –threatscore | where threatscore>0

purpose:

Detect machines/applications who are potentially infected and have active running malware on it. Even use it to detect fraud for shopping site orders coming from bad IP's

requirements:

machine data with external IP's + IP Reputation App

comments:

  • Search Logs index=
  • Make sure fields are extracted fine – you can even let this run in realtime – looks cool: | stats count by src_ip dst_ip dst_port protocol
  • Now we enrich the data with | lookup threatscore clientip as dst_ip
  • Now as there is a new field evaluated (Threatscore) we want to show the IP's with the highest threatscore first by sorting it: | sort –threatscore
  • And now we only want to see malicious connections instead of the good once: | where threatscore>0

geo-location w/ user home base lookup

index=geod
# get some location information
| iplocation clientip
# lookup user details from a lookup table
#  including their home location
| lookup user_home_lu user as user
# calculate the distance between the login location
#  and the user's home location
#  using the haversine app (http://apps.splunk.com/app/936/)
| haversine originField=home_latlon units=mi inputFieldLat=lat inputFieldLon=lon
# limit the list to those where the distance is greater
#  than 500 miles
| where distance > 500
# clean up for reporting purposes
| strcat City ", " Region cs
# report the results
| fields user, cs, distance

purpose:

find users that are logging in from a location which is greater than 500 miles away from the registered home office

requirements:

haversine app clientip lookup table with user > home_latlon

comments:

Indexes my user can search.

| rest /services/data/indexes
| search
    [
    | rest /services/data/indexes
    | dedup title
    | table title
    | search
        [
            | rest splunk_server=local /services/authorization/roles
            | search
                [
                        | rest splunk_server=local /services/authentication/users
                        | search
                            [
                                        | rest /services/authentication/current-context
                                        | search type=splunk
                                        | table username
                                        | rename username as title ]
| fields roles
| mvexpand roles
| rename roles as title] imported_srchIndexesAllowed=*
| table imported_srchIndexesAllowed
| rename imported_srchIndexesAllowed as title
| mvexpand title] ]
| stats values(splunk_server) as splunkserver by title
| eval splunkserver=mvjoin(splunkserver,":")
| lookup non_internal_indexes title as title OUTPUT description as description
| fields title, description, splunkserver
| rename title AS Index

purpose:

requirements:

lookup csv with columns "title" of Index and "description"

comments:

We use in a ~200 Users env to show the user in which Index he is allowed to search.