SPL - Hard multisearch with reference to other events

[Kozokkon]

Good afternoon,
I've got a quite hard task to solve with SPL.

Here are JSON data:

{"name":"A", "pairs":["A","B"]},
{"name":"B", "pairs":["B","C"]},
{"name":"C", "pairs":["C","B"]},
{"name":"D", "pairs":["D","E"]},
{"name":"E", "pairs":["D","E"]}

Each JSON object is event
Name - is name of object
Pairs - are reference to other objects

Expected input:
The person write as input name=A

Expected output:
Splunk will return all related events referenced by pairs and will search recursively by pairs
so results will be:

    // 1. raw event 
    {"name":"A", "pairs":["A","B"]}
    // 2. raw event 
    {"name":"B", "pairs":["B","C"]}
    // 3. raw event 
    {"name":"C", "pairs":["C","B"]}

Alternative results:
It will be OK if Splunk will return joined mv field with values :

pairs = ["A","B","C"] // pairs as multi value field with values A B and C

Is this possible to get such result with single SPL query?

[niketn · Accepted solution]

@Kozokkon, for the kind of output you need, you can try the following search. Commands from |makeresults till | rename data as _raw generates dummy data similar to your question. As per your question you can add search filter for specific field value. Following example uses | search pairs="A"

| makeresults 
| eval data="{\"name\":\"A\", \"pairs\":[\"A\",\"B\"]};{\"name\":\"B\", \"pairs\":[\"B\",\"C\"]};{\"name\":\"C\", \"pairs\":[\"C\",\"B\"]};{\"name\":\"D\", \"pairs\":[\"D\",\"E\"]};{\"name\":\"E\", \"pairs\":[\"D\",\"E\"]};{\"name\":\"F\", \"pairs\":[\"E\",\"G\"]};{\"name\":\"H\",\"pairs\":[\"I\"]}" 
| makemv data delim=";" 
| mvexpand data
| rename data as _raw
| spath 
| rename "pairs{}" as "pairs" 
| eval pairs=mvappend(pairs,name) 
| stats values(pairs) as pairs by name
| eval correlate=pairs
| stats values(pairs) as pairs dc(pairs) as pairCount by correlate
| fields - correlate
| search pairs="A"
| sort - pairCount
| head 1
| fields - pairCount

However, if you want to show correlated between the name and pairs field values. You can run a faster command using single stats and then use visualization like Sankey Diagram Custom Visualization or Force Directed Graph Custom Visualization to plot the relation between directly connected nodes and all correlated groups. Just add the following to your existing search

<yourCurrentSearch>
| stats count by name pairs

Following is a run anywhere search for above approach:

| makeresults 
| eval data="{\"name\":\"A\", \"pairs\":[\"A\",\"B\"]};{\"name\":\"B\", \"pairs\":[\"B\",\"C\"]};{\"name\":\"C\", \"pairs\":[\"C\",\"B\"]};{\"name\":\"D\", \"pairs\":[\"D\",\"E\"]};{\"name\":\"E\", \"pairs\":[\"D\",\"E\"]};{\"name\":\"F\", \"pairs\":[\"E\",\"G\"]};{\"name\":\"H\",\"pairs\":[\"I\"]}" 
| makemv data delim=";"
| mvexpand data
| spath input=data
| rename "pairs{}" as "pairs"
| stats count by name pairs

Following is Simple XML code for the run anywhere dashboard showing all three examples as attached screenshot. Please try out and confirm!

<form>
  <label>Dependency between Parent Child</label>
  <fieldset submitButton="false"></fieldset>
  <row>
    <panel>
      <input type="dropdown" token="tokSearchField" searchWhenChanged="true">
        <label>Search a Value</label>
        <default>A</default>
        <fieldForLabel>name</fieldForLabel>
        <fieldForValue>name</fieldForValue>
        <search>
          <query>| makeresults 
| eval data="{\"name\":\"A\", \"pairs\":[\"A\",\"B\"]};{\"name\":\"B\", \"pairs\":[\"B\",\"C\"]};{\"name\":\"C\", \"pairs\":[\"C\",\"B\"]};{\"name\":\"D\", \"pairs\":[\"D\",\"E\"]};{\"name\":\"E\", \"pairs\":[\"D\",\"E\"]};{\"name\":\"F\", \"pairs\":[\"E\",\"G\"]};{\"name\":\"H\",\"pairs\":[\"I\"]}" 
| makemv data delim=";" 
| mvexpand data
| rename data as _raw
| spath 
| rename "pairs{}" as "pairs" 
| stats count by name</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
        </search>
      </input>
      <table>
        <search>
          <query>| makeresults 
| eval data="{\"name\":\"A\", \"pairs\":[\"A\",\"B\"]};{\"name\":\"B\", \"pairs\":[\"B\",\"C\"]};{\"name\":\"C\", \"pairs\":[\"C\",\"B\"]};{\"name\":\"D\", \"pairs\":[\"D\",\"E\"]};{\"name\":\"E\", \"pairs\":[\"D\",\"E\"]};{\"name\":\"F\", \"pairs\":[\"E\",\"G\"]};{\"name\":\"H\",\"pairs\":[\"I\"]}" 
| makemv data delim=";" 
| mvexpand data
| rename data as _raw
| spath 
| rename "pairs{}" as "pairs" 
| eval pairs=mvappend(pairs,name) 
| stats values(pairs) as pairs by name
| eval correlate=pairs
| stats values(pairs) as pairs dc(pairs) as pairCount by correlate
| fields - correlate
| search pairs="$tokSearchField$"
| sort - pairCount
| head 1
| fields - pairCount</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</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>
    <panel>
      <title>Sankey Diagram</title>
      <viz type="sankey_diagram_app.sankey_diagram">
        <search>
          <query>| makeresults 
| eval data="{\"name\":\"A\", \"pairs\":[\"A\",\"B\"]};{\"name\":\"B\", \"pairs\":[\"B\",\"C\"]};{\"name\":\"C\", \"pairs\":[\"C\",\"B\"]};{\"name\":\"D\", \"pairs\":[\"D\",\"E\"]};{\"name\":\"E\", \"pairs\":[\"D\",\"E\"]};{\"name\":\"F\", \"pairs\":[\"E\",\"G\"]};{\"name\":\"H\",\"pairs\":[\"I\"]}" 
| makemv data delim=";"
| mvexpand data
| spath input=data
| rename "pairs{}" as "pairs"
| stats count by name pairs</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="drilldown">none</option>
        <option name="sankey_diagram_app.sankey_diagram.colorMode">categorical</option>
        <option name="sankey_diagram_app.sankey_diagram.maxColor">#3fc77a</option>
        <option name="sankey_diagram_app.sankey_diagram.minColor">#d93f3c</option>
        <option name="sankey_diagram_app.sankey_diagram.numOfBins">6</option>
        <option name="sankey_diagram_app.sankey_diagram.showBackwards">false</option>
        <option name="sankey_diagram_app.sankey_diagram.showLabels">true</option>
        <option name="sankey_diagram_app.sankey_diagram.showLegend">true</option>
        <option name="sankey_diagram_app.sankey_diagram.showSelf">false</option>
        <option name="sankey_diagram_app.sankey_diagram.showTooltip">true</option>
        <option name="sankey_diagram_app.sankey_diagram.styleBackwards">false</option>
        <option name="sankey_diagram_app.sankey_diagram.useColors">true</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
      </viz>
    </panel>
    <panel>
      <title>Force Directed Chart</title>
      <viz type="force_directed_viz.force_directed">
        <search>
          <query>| makeresults 
| eval data="{\"name\":\"A\", \"pairs\":[\"A\",\"B\"]};{\"name\":\"B\", \"pairs\":[\"B\",\"C\"]};{\"name\":\"C\", \"pairs\":[\"C\",\"B\"]};{\"name\":\"D\", \"pairs\":[\"D\",\"E\"]};{\"name\":\"E\", \"pairs\":[\"D\",\"E\"]};{\"name\":\"F\", \"pairs\":[\"E\",\"G\"]};{\"name\":\"H\",\"pairs\":[\"I\"]}" 
| makemv data delim=";"
| mvexpand data
| spath input=data
| rename "pairs{}" as "pairs"
| stats count by name pairs</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="drilldown">none</option>
        <option name="force_directed_viz.force_directed.AttractDistanceMax">200</option>
        <option name="force_directed_viz.force_directed.AttractDistanceMin">60</option>
        <option name="force_directed_viz.force_directed.AttractForceStrength">-300</option>
        <option name="force_directed_viz.force_directed.CollisionIterations">1</option>
        <option name="force_directed_viz.force_directed.CollisionRadius">20</option>
        <option name="force_directed_viz.force_directed.CollisionStrength">0.7</option>
        <option name="force_directed_viz.force_directed.ColorRange1">100</option>
        <option name="force_directed_viz.force_directed.ColorRange1Code">#65a637</option>
        <option name="force_directed_viz.force_directed.ColorRange2">500</option>
        <option name="force_directed_viz.force_directed.ColorRange2Code">#6db7c6</option>
        <option name="force_directed_viz.force_directed.ColorRange3">1000</option>
        <option name="force_directed_viz.force_directed.ColorRange3Code">#f7bc38</option>
        <option name="force_directed_viz.force_directed.ColorRange4">10000</option>
        <option name="force_directed_viz.force_directed.ColorRange4Code">#f58f39</option>
        <option name="force_directed_viz.force_directed.ColorRange5">1000000</option>
        <option name="force_directed_viz.force_directed.ColorRange5Code">#d93f3c</option>
        <option name="force_directed_viz.force_directed.ForceCollision">20</option>
        <option name="force_directed_viz.force_directed.LineColor">disabled</option>
        <option name="force_directed_viz.force_directed.LinkDistance">100</option>
        <option name="force_directed_viz.force_directed.LinkLength">1</option>
        <option name="force_directed_viz.force_directed.RepelDistanceMax">50</option>
        <option name="force_directed_viz.force_directed.RepelDistanceMin">10</option>
        <option name="force_directed_viz.force_directed.RepelForceStrength">-140</option>
        <option name="force_directed_viz.force_directed.StrokeWidth">1</option>
        <option name="force_directed_viz.force_directed.arrows">disabled</option>
        <option name="force_directed_viz.force_directed.circleSize">5</option>
        <option name="force_directed_viz.force_directed.panzoom">disabled</option>
        <option name="force_directed_viz.force_directed.theme">dark</option>
        <option name="height">450</option>
        <option name="trellis.enabled">0</option>
        <option name="trellis.scales.shared">1</option>
        <option name="trellis.size">medium</option>
      </viz>
    </panel>
  </row>
</form>

[Kozokkon]

Oh wow, thank you!