Lookup to Drilldown: Building dynamic searches for triaging Risk notables in Splunk

Donald Murchison
8 min readJul 2, 2023

--

Risk Based Alerting (RBA) is the new frontier in SIEM. For those unfamiliar with RBA, there are a lot of great .conf presentations and resources to get an understanding of its benefits and how to get started.

Source: https://www.splunk.com/en_us/blog/security/risk-based-alerting-the-new-frontier-for-siem.html

For those who have already started their journey with RBA, you have probably noticed that triaging risk alerts is fairly different from triaging traditional alerts. Alerts in RBA, are generally triggered from a series of “interesting” events (risk events) highlighting a range of behavior flagged by a variety of detections. It is difficult to present all of the important details in a single alert, so many teams opt to create a custom investigation dashboard for RBA. Haylee Mills, a security strategist at Splunk, does an excellent job discussing how to do this in “Streamlining Analysis of Security Stories with Risk-based Alerting” (slides).

Source: https://splunk.github.io/rba/dashboards/risk_investigation/

Even with an awesome risk investigation dashboard like Haylee’s, there will be times when analysts need to gather additional information directly from the source. If your team documents detections using a format like Palantir’s ADS, then you are likely already providing analysts with steps or decision trees to drill down into the behavior. However, drilling down can sometimes require complex searches, which can be time-consuming to develop, especially if analysts are doing this on their own. It would be nice to provide analysts with pre-populated drilldown searches that they can just click to get results. So let’s do it!

By following the steps below you can create a dashboard panel with clickable drilldown searches pre-populated with details from risk events to add to any RBA investigation dashboard. (Full XML for the example dashboard and SPL for the example drilldowns are at the end of the article)

First, we build a lookup for our drilldown searches. We need at least two columns. One for the search name the drilldown is associated with, and one for the actual drilldown. I also like to add a description of the drilldown with the time range that should be used, and what to look for.

Lookup populated with drilldowns and descriptions

We will be using the drilldown field to construct a dynamic eval statement, so instead of using $<field>$ for variable replacement, we use string concatenation. Essentially, we use the lookup to run | eval drilldown=“string”.<field>.”string”. Here’s a simple example of a drilldown.

"index=risk risk_object=".risk_object." | eval message=\"this is a drilldown example\""

It’s important to note that any double quotes or back slashes will need to be escaped within your drilldown. Sometimes this can be tricky (escaped double quotes need three backslashes \\\”) so it helps to test the drilldown format by using makeresults and eval.

The drilldown field should show as proper SPL which could be run in the search bar.

Since we are building an eval statement, we can also manipulate the field with standard eval functions like coalesce. This can also be helpful to format multivalue fields since multivalue fields do not work as is with string concatenation and eval statements.

"index=risk risk_object=".coalesce(risk_object,src,src_ip)." | eval message=\"this is a drilldown example\""

Now that we know how to build the lookup, we just need to build the dashboard panel. We are going to use the SPL below to run our dynamic evals. The SPL has several comments to explain the logic but at a high level, we use map to return risk events for each individual search name. We then use |inputlookup to evaluate the contents of our lookup and set as the drilldown field.

| tstats summariesonly=false count from datamodel=Risk.All_Risk where All_Risk.risk_object="$risk_object_token$" by source
``` Get a list of all risk rules that have generated a risk event for this risk object - assumes the dashbaord has an input which stores risk_object in "risk_object_token"
replace risk_object_token with your own token name - helpful to use risk_object_type in search if this is in a token as well ```
| fields source
``` Use map to run a search for each risk rule to generate the drilldowns - map was used to be able to pass the risk rule name as an argument to the subsearch.
This is required because we must run an individual "
| eval drilldown=…" for each risk rule in case fields are used in the drilldown that do not exist in other risk events.
String concatentation with a null field would make our entire string null.
If you wanted to remove map for better performance you could do this by only using fields that are present in every risk rule or building drilldowns with coalesce - coalesce(risk_object,\"\") - to ensure no null fields```
| map search="index=risk risk_object=\"$risk_object_token$\" | eval drilldown=[| inputlookup rba_risk_rule_drilldowns.csv | eval search_name=split(search_name,\"|\") | search search_name=\"$$source$$\" | eval drilldown=\"\\\"\".search_name.\"||@||\\\".\".drilldown.\".\\\"||@||\".description.\"\\\"\"
``` In the map search, we first search for all risk events related to the risk rule. Every risk event will get a drilldown field that we will dedup later. We do not use the datamodel in case fields outside of the datamodel are used in the drilldown.
The |inputlookup subsearch concatenates search_name, drilldown, and description for each row```
| stats values(drilldown) as drilldown
| eval drilldown=mvjoin(drilldown,\".\\\"||&||\\\".\")
``` We then condense all drilldowns to a single field and concatenate together - this allows us to evaluate all drilldowns within a single eval statement```
|return $drilldown] | fields drilldown"
```Now we break out the drilldowns into their respective components```
| eval drilldown=split(drilldown,"||&||")
| mvexpand drilldown
| eval search_name=mvindex(split(drilldown,"||@||"),0)
| eval drilldown_description=mvindex(split(drilldown,"||@||"),2)
| eval drilldown=mvindex(split(drilldown,"||@||"),1)
| stats values(*) as * by drilldown
``` Use stats to dedup the drilldowns - depending on the fields used in the drilldown there could be multiple variations of the same drilldown```
| table search_name drilldown_description drilldown

To add this to a dashboard, we simply create a new Statistics Table panel, and copy in our SPL from above.

We then click “More Actions” -> “Edit Drilldown”. In the Drilldown Editor, we select “Link to search” -> “Custom” and set the search string to “$row.drilldown$”.

That’s it! We now have a clickable dashboard panel that allows analysts to run pre-populated drilldown searches.

Full XML for the RBA Drilldown Example dashboard

<form version="1.1" theme="dark">
<label>RBA Drilldown Example</label>
<fieldset submitButton="false">
<input type="text" token="risk_object_token">
<label>Risk Object</label>
</input>
<input type="time" token="field1">
<label></label>
<default>
<earliest>-24h@h</earliest>
<latest>now</latest>
</default>
</input>
</fieldset>
<row>
<panel>
<table>
<title>RBA Drilldowns</title>
<search>
<query>| tstats summariesonly=false count from datamodel=Risk.All_Risk where All_Risk.risk_object="$risk_object_token$" by source
``` Get a list of all risk rules that have generated a risk event for this risk object - assumes the dashbaord has an input which stores risk_object in "risk_object_token"
replace risk_object_token with your own token name - helpful to use risk_object_type in search if this is in a token as well ```
| fields source
``` Use map to run a search for each risk rule to generate the drilldowns - map was used to be able to pass the risk rule name as an argument to the subsearch.
This is required because we must run an individual "
| eval drilldown=…" for each risk rule in case fields are used in the drilldown that do not exist in other risk events.
String concatentation with a null field would make our entire string null.
If you wanted to remove map for better performance you could do this by only using fields that are present in every risk rule or building drilldowns with coalesce - coalesce(risk_object,\"\") - to ensure no null fields```
| map search="index=risk risk_object=\"$risk_object_token$\" | eval drilldown=[| inputlookup rba_risk_rule_drilldowns.csv | eval search_name=split(search_name,\"|\") | search search_name=\"$$source$$\" | eval drilldown=\"\\\"\".search_name.\"||@||\\\".\".drilldown.\".\\\"||@||\".description.\"\\\"\"
``` In the map search, we first search for all risk events related to the risk rule. Every risk event will get a drilldown field that we will dedup later. We do not use the datamodel in case fields outside of the datamodel are used in the drilldown.
The |inputlookup subsearch concatenates search_name, drilldown, and description for each row```
| stats values(drilldown) as drilldown
| eval drilldown=mvjoin(drilldown,\".\\\"||&amp;||\\\".\")
``` We then condense all drilldowns to a single field and concatenate together - this allows us to evaluate all drilldowns within a single eval statement```
|return $drilldown] | fields drilldown"
```Now we break out the drilldowns into their respective components```
| eval drilldown=split(drilldown,"||&amp;||")
| mvexpand drilldown
| eval search_name=mvindex(split(drilldown,"||@||"),0)
| eval drilldown_description=mvindex(split(drilldown,"||@||"),2)
| eval drilldown=mvindex(split(drilldown,"||@||"),1)
| stats values(*) as * by drilldown
``` Use stats to dedup the drilldowns - depending on the fields used in the drilldown there could be multiple variations of the same drilldown```
| table search_name drilldown_description drilldown</query>
<earliest>$field1.earliest$</earliest>
<latest>$field1.latest$</latest>
</search>
<option name="drilldown">cell</option>
<option name="refresh.display">progressbar</option>
<drilldown>
<link target="_blank">search?q=$row.drilldown$&amp;earliest=$field1.earliest$&amp;latest=$field1.latest$</link>
</drilldown>
</table>
</panel>
</row>
</form>

Drilldown examples from lookup

Search to view identity provider login history for user, includes geolocation and previously seen country counts

"index=identity_provider user=".risk_object." 
| eval user=lower(user)
| iplocation prefix=src_ src
| lookup identity_lookup_expanded identity AS user outputnew email as user_key
| lookup access_tracker_by_location email as user_key country as src_Country OUTPUT count as previously_seen sources
| fields _time user src_Country src action authentication_method app protocol type previously_seen sources http_user_agent
| stats earliest(_time) as start_time latest(_time) as end_time values(*) as * sum(previously_seen) as previously_seen by user src
| convert ctime(*_time)"

Search to display process trees for OUTLOOK for user

"index=sysmon source=\"xmlwineventlog:microsoft-windows-sysmon/operational\" EventCode=1 user=".risk_object." 
| rex field=ParentImage \"\\x5c(?<ParentName>
[ ^\\x5c]+)$\"
| rex field=Image \"\\x5c(?<ProcessName>
[ ^\\x5c]+)$\"
| eval parent = ParentName.\" (\".ParentProcessId.\")\"
| eval child = ProcessName.\" (\".ProcessId.\")\"
| eval detail=strftime(_time,\"%Y-%m-%d %H:%M:%S\").\" \".CommandLine
| pstree child=child parent=parent detail=detail spaces=50
| search tree=*OUTLOOK*
| table tree"

Search to identify possible C2 traffic

"`proxy_logs` NOT (bytes_in=0 AND bytes_out=0) NOT `internal_ip(url_domain)` url=*/* src=".src."
| sort 0 - url_domain _time
| delta _time as time_diff
| autoregress p=1 url_domain as prev_url_domain
| where url_domain=prev_url_domain
| table _time url_domain user time_diff bytes_in bytes_out url
| stats earliest(_time) as start_time latest(_time) as end_time count avg(time_diff) as median_time_diff stdev(time_diff) as stdev_time_diff avg(bytes_in) as median_bytes_in stdev(bytes_in) as stdev_bytes_in avg(bytes_out) as median_bytes_out stdev(bytes_out) as stdev_bytes_out values(url) as url by user url_domain
| convert ctime(*_time)"

Search to identify when user likely egressed to new domains

"| tstats summariesonly=t count values(Web.dest_ip) as dest_ip earliest(_time) as _time values(Web.url) as url from datamodel=Web where Web.src=\"".src."\" by Web.url_domain
| addinfo
| fields - info_min_time info_search_time info_sid
| where _time>relative_time(info_max_time,\"-1d\")
| rename Web.* as *
| table _time url_domain url"

--

--