Utilising values from Lookup in subsequent search

[Barty]

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:-

Team	team_lvnos
Heritage Products	fiacjro 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. 

[niketn · Accepted solution]

@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>

[Barty]

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

[isoutamo]

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.