Splunk Search

Appending multiple search using appendcols

ansusplunk
New Member

I have a combined search query using stats count and appendcols.I am able to display the combined search result in single column -multiple rows format using 'transpose'.But when I click on count value of each search result, I am able to see the log info hit result of base query only(pets who like egg).I want to see the search result of the specific query when the specific count value of search is clicked.This means when I click on "pets who like meat " count value, the search should show only those log info specific to this search only. Please guide.
The following is the query I am using:

pets who like egg | stats count AS "Total Number of Egg loving pets" | appendcols [search pets who like meat | stats count AS "Total Number of Meat loving pets"] | transpose | rename column as "Event", row* as Count*

0 Karma
1 Solution

niketn
Legend

Please try the following run anywhere search based on Splunk's _internal logs based on errors (on similar lines as per your use case):

<form>
  <label>Search Table Custom Drilldown</label>
  <fieldset submitButton="false">
    <input type="time" token="tokTime">
      <label></label>
      <default>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <search>
          <query>index=_internal sourcetype=splunkd log_level!=INFO ("Either time adjusted forwards by" OR "Provenance argument was in an invalid format.")
|  stats count(eval(searchmatch("Either time adjusted forwards by"))) as Matched1  count(eval(searchmatch("Provenance argument was in an invalid format."))) as Matched2
| transpose column_name="Event"
| rename "row 1" as count</query>
          <earliest>$tokTime.earliest$</earliest>
          <latest>$tokTime.latest$</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <drilldown>
          <eval token="queryString">case($row.Event$="Matched1","index=_internal sourcetype=splunkd log_level!=INFO \"Either time adjusted forwards by\"", $row.Event$="Matched2","index=_internal sourcetype=splunkd log_level!=INFO \"Provenance argument was in an invalid format.\"")</eval>
        </drilldown>
      </table>
    </panel>
  </row>
  <row depends="$queryString$">
    <panel>
      <title>Event Details (Results: $resultCount$) $queryString$</title>
      <event>
        <search>
          <query>$queryString$
          </query>
          <earliest>$tokTime.earliest$</earliest>
          <latest>$tokTime.latest$</latest>
          <done>
            <set token="resultCount">$job.resultCount$</set>
          </done>
        </search>
      </event>
    </panel>
  </row>
</form>

If this does not work directly, since you might not have above two errors logged then
1) Either just run the base search index=_internal sourcetype=splunkd log_level!=INFO and and pick couple of errors
Or 2) You can change the above as per your needs with actual search strings and field names since eventually you need to test that.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

jkat54
SplunkTrust
SplunkTrust

You need a by clause on your stats command. You might also like to have just one column for "count" (which i achieve with event stats and then and eval). The example below shows 3 columns but you can easily change values(c*) as c* to values(count) as count and produce just one count column

index=main 
| eventstats count(eval(searchmatch("pets who like egg"))) as c1 count(eval(searchmatch("pets who like meat"))) as c2 by Event
| eval count=if(c1=0,c2,c1) 
| stats values(c*) as c* by Event

alt text

The drill down for the count column becomes this (assuming i click on 185, which corresponds to cert=sslPaths in my dataset):

index=main   cert=sslPaths | stats count(eval(searchmatch("cacert"))) as c1 count(eval(searchmatch("ssl*"))) as c2 by cert | eval count=if(c1=0,c2,c1)  
0 Karma

niketn
Legend

Please try the following run anywhere search based on Splunk's _internal logs based on errors (on similar lines as per your use case):

<form>
  <label>Search Table Custom Drilldown</label>
  <fieldset submitButton="false">
    <input type="time" token="tokTime">
      <label></label>
      <default>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <search>
          <query>index=_internal sourcetype=splunkd log_level!=INFO ("Either time adjusted forwards by" OR "Provenance argument was in an invalid format.")
|  stats count(eval(searchmatch("Either time adjusted forwards by"))) as Matched1  count(eval(searchmatch("Provenance argument was in an invalid format."))) as Matched2
| transpose column_name="Event"
| rename "row 1" as count</query>
          <earliest>$tokTime.earliest$</earliest>
          <latest>$tokTime.latest$</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="count">20</option>
        <option name="dataOverlayMode">none</option>
        <option name="drilldown">cell</option>
        <option name="percentagesRow">false</option>
        <option name="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
        <drilldown>
          <eval token="queryString">case($row.Event$="Matched1","index=_internal sourcetype=splunkd log_level!=INFO \"Either time adjusted forwards by\"", $row.Event$="Matched2","index=_internal sourcetype=splunkd log_level!=INFO \"Provenance argument was in an invalid format.\"")</eval>
        </drilldown>
      </table>
    </panel>
  </row>
  <row depends="$queryString$">
    <panel>
      <title>Event Details (Results: $resultCount$) $queryString$</title>
      <event>
        <search>
          <query>$queryString$
          </query>
          <earliest>$tokTime.earliest$</earliest>
          <latest>$tokTime.latest$</latest>
          <done>
            <set token="resultCount">$job.resultCount$</set>
          </done>
        </search>
      </event>
    </panel>
  </row>
</form>

If this does not work directly, since you might not have above two errors logged then
1) Either just run the base search index=_internal sourcetype=splunkd log_level!=INFO and and pick couple of errors
Or 2) You can change the above as per your needs with actual search strings and field names since eventually you need to test that.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

ansusplunk
New Member

Thanks a lot!! Your answer was awesome and it guided me in the right direction!

0 Karma

niketn
Legend

Anytime!!! Glad it worked 🙂

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

niketn
Legend

@ansusplunk, when you use sub-searches, default drilldown always takes you to base search. So in case you need drilldown specific to your needs you might have to code your own drilldown event handler. With Splunk 6.6, most basic drilldown token handling can be done directly through UI edit options.

Can you please let us know which visualization you are using to show the results? Is it Table or Chart or something else?

Also on a different note, is the data source for egg loving pets and meat loving petssame or different? Is there any possibility of merging the data from two different sourcetypes in base search itself so that you dont require appendcols? Details on SPL and data will surely be more useful for us to assist you better.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

ansusplunk
New Member

Hi,
To be precise, I am trying to create a dashboard with the mentioned combined search query.The query picks up the logger statements from different services with which the Splunk service is binded with and counts the number of times these log statements appear as understood from given search query.In this sense the data source is different as the log statements are from different micro services.
The visualization used is 'table'.
Again the requirement is such that different counts of search result should be displayed in tabular format.
Can you please share some examples on how to use drilldown feature to achieve my goal or any alternate way out.
We are using Splunk cloud version - 6.5.2
Thanks!

0 Karma

niketn
Legend

One of the ways to loose appendcols would be to combine the sourcetype in base search like

(index="idx1" sourcetype="st1") OR (index="idx2" sourcetype="st2")

Then use field or pattern to match events you need.

| stats count(eval(searchmatch("pets who like egg"))) as "Total Number of Egg loving pets" count(eval(searchmatch("pets who like meat"))) as "Total Number of Meat loving pets"

However, I can give you exact query unless you provide more details like index sourcetype whether they are same or not. If there is a difference in the two which field it is. I have give you searchmatch() which will be expensive you can do it better through your field name/value pattern. So please provide information of your data (field/values) and existing SPL. So that we can be more helpful. You can mock/anonymize any sensitive information that can not be shared.

For table drilldown you can refer to Splunk 6.x Dashboard Examples app on Splunkbase (https://splunkbase.splunk.com/app/1603/). You can also read Splunk Documentation, Table Drilldown Default Tokens:
https://docs.splunk.com/Documentation/Splunk/latest/Viz/EventHandlerReference#table_.28event_tokens....

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

ansusplunk
New Member

I am using the following query now:
index=* sourcetype=* | stats count(eval(searchmatch("pets who like egg "))) as "Total Number of Egg loving pets" count(eval(searchmatch("pets who like meat"))) as "Total Number of Meat loving pets"
Still the same issue.When clicked on the specific count of the resultant search, the base search opens which is : index=* sourcetype=*
Can you please guide here.

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