Dashboards & Visualizations

Utilising values from Lookup in subsequent search

Barty
Explorer

Good morning all,

I'm really hoping someone may be able to assist me, as I'm currently at a loss. I have a lookup that essentially is a reference sheet for some team & user data. There are two columns, the 'Team' name and the subsequent usernames all neatly presented with an 'OR' delimiter. The reason for this being that within a dashboard, I required to run a separate search against our RSA log data, using the token of the username(s) values from the lookup. 

The lookup is presented as such:-

 

| inputlookup team_vpn_list.csv 
| search Team="Heritage Products"

 

 which presents a table as such:-

Teamteam_lvnos
Heritage Productsfiacjro OR lv14447 OR lv14839 OR lv14989 OR lv15961

 

So, having searched through here - I've come across the map search function, and so figured that my search would be along the lines of this:-

 

| inputlookup team_vpn_list.csv 
| search Team="Heritage Products"
| map search="search index=syslog source=*rsa* earliest=-15m $$team_lvnos$$"

 

However, this returns no results, and i've tried reducing the '$$' to '$' etc in case it perhaps was that. However, from the job inspector, I can see that the search is running the following:-

INFO  SearchParser - PARSING: search index=syslog source=*rsa* earliest=-15m $"fiacjro OR lv14447 OR lv14839 OR lv14989 OR lv15961"$

 So to me, this seems to be referencing the token as hoped, but not returning expected search events from the rsa event data, that I know are there. To confirm, If I run the above search without the $" at the start and end, it returns valid events from the syslog index and source.

Please anyone, if you're able to shed some light as to why this is happening, or how to remidiate the issue, i'd be forever thankful. 

Labels (3)
0 Karma
1 Solution

niketn
Legend

@Barty if your intent is to run the following search by using results from inputlookup 

index=syslog source=*rsa* (fiacjro OR lv14447 OR lv14839 OR lv14989 OR lv15961)

Map should work. You should use single $ sign if you are running the SPL in Search UI, and two $$ if your are running the same inside a dashboard to escape the dollar sign $

Following are three options. You would need to escape $ for return or map approach. The third option with table in the subsearch does not need escaping as there is no $ sign. Please try out and confirm!

 

<dashboard>
  <label>Run Subsearch based on lookup results</label>
  <row>
    <panel>
      <title>Option 1 : Using return command</title>
      <event>
        <search>
          <query>index=syslog source=*rsa* earliest=-15m 
    [| inputlookup team_vpn_list.csv where Team="Heritage Products" 
    | fields team_lvnos 
    | rename team_lvnos as search 
    | return 1 $$search ]</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="list.drilldown">none</option>
        <option name="list.wrap">1</option>
        <option name="maxLines">5</option>
        <option name="raw.drilldown">full</option>
        <option name="rowNumbers">0</option>
        <option name="table.drilldown">all</option>
        <option name="table.sortDirection">asc</option>
        <option name="table.wrap">1</option>
        <option name="type">list</option>
      </event>
    </panel>
  </row>
  <row>
    <panel>
      <title>Option 2: Using map command</title>
      <table>
        <search>
          <query>| inputlookup team_vpn_list.csv where Team="Heritage Products" 
| map search="search index=syslog source=*rsa* earliest=-15m $$team_lvnos$$" maxsearches=1</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">10</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <title>Option 3 : Using table in SubSearch</title>
      <event>
        <search>
          <query>index=syslog source=*rsa* earliest=-15m 
    [| inputlookup team_vpn_list.csv where Team="Heritage Products" 
    | fields team_lvnos 
    | head 1
    | rename team_lvnos as search 
    | table search ]</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="list.drilldown">none</option>
        <option name="list.wrap">1</option>
        <option name="maxLines">5</option>
        <option name="raw.drilldown">full</option>
        <option name="rowNumbers">0</option>
        <option name="table.drilldown">all</option>
        <option name="table.sortDirection">asc</option>
        <option name="table.wrap">1</option>
        <option name="type">list</option>
      </event>
    </panel>
  </row>
</dashboard>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

@Barty if your intent is to run the following search by using results from inputlookup 

index=syslog source=*rsa* (fiacjro OR lv14447 OR lv14839 OR lv14989 OR lv15961)

Map should work. You should use single $ sign if you are running the SPL in Search UI, and two $$ if your are running the same inside a dashboard to escape the dollar sign $

Following are three options. You would need to escape $ for return or map approach. The third option with table in the subsearch does not need escaping as there is no $ sign. Please try out and confirm!

 

<dashboard>
  <label>Run Subsearch based on lookup results</label>
  <row>
    <panel>
      <title>Option 1 : Using return command</title>
      <event>
        <search>
          <query>index=syslog source=*rsa* earliest=-15m 
    [| inputlookup team_vpn_list.csv where Team="Heritage Products" 
    | fields team_lvnos 
    | rename team_lvnos as search 
    | return 1 $$search ]</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="list.drilldown">none</option>
        <option name="list.wrap">1</option>
        <option name="maxLines">5</option>
        <option name="raw.drilldown">full</option>
        <option name="rowNumbers">0</option>
        <option name="table.drilldown">all</option>
        <option name="table.sortDirection">asc</option>
        <option name="table.wrap">1</option>
        <option name="type">list</option>
      </event>
    </panel>
  </row>
  <row>
    <panel>
      <title>Option 2: Using map command</title>
      <table>
        <search>
          <query>| inputlookup team_vpn_list.csv where Team="Heritage Products" 
| map search="search index=syslog source=*rsa* earliest=-15m $$team_lvnos$$" maxsearches=1</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">10</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">none</option>
        <option name="percentagesRow">false</option>
        <option name="refresh.display">progressbar</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <title>Option 3 : Using table in SubSearch</title>
      <event>
        <search>
          <query>index=syslog source=*rsa* earliest=-15m 
    [| inputlookup team_vpn_list.csv where Team="Heritage Products" 
    | fields team_lvnos 
    | head 1
    | rename team_lvnos as search 
    | table search ]</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="list.drilldown">none</option>
        <option name="list.wrap">1</option>
        <option name="maxLines">5</option>
        <option name="raw.drilldown">full</option>
        <option name="rowNumbers">0</option>
        <option name="table.drilldown">all</option>
        <option name="table.sortDirection">asc</option>
        <option name="table.wrap">1</option>
        <option name="type">list</option>
      </event>
    </panel>
  </row>
</dashboard>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

Barty
Explorer

That is EXACTLY what I was looking for, thank you so much.

Tags (1)
0 Karma

isoutamo
SplunkTrust
SplunkTrust

Hi

easiest way is run that inputlookup as sub search. 

index=syslog source=*rsa* [| inputlookup team_vpn_list.csv |search Team=“Heritage Products”|format]

Please check the correct syntax as I haven’t had splunk on my hands.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...