Splunk Search

SPL - Hard multisearch with reference to other events

Kozokkon
Engager

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?

Tags (1)
0 Karma
1 Solution

niketn
Legend

@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

alt text

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>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

niketn
Legend

@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

alt text

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>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

Kozokkon
Engager

Oh wow, thank you!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...