Archive

How to retrieve the values of the existing column based on eval result dynamically?

9738078959
Engager

i have a data like below....

ID | Name | 2017-12 |2018-01|2018-02|2018-03
X123 |aaa | 90 | 89 | 87 | 87
X234 |bbb | 89 | 91 | 77 | 99
X345 |ccc | 97 | 96 | 95 | 94

i need to retrieve data or column base on Month.
lets say if the month changes to December it should take 2017-12 values for calculation.
Please help.

Thanks,
Ganesh Shetty

Tags (1)
0 Karma

niketnilay
Legend

@9738078959, following is a Run anywhere dashboard which filters and displays result for only current month. However, you should provide your current search so that we can help you with better query (By Filtering results for current month upfront and then displaying only those counts - refer to second Panel in the following run-anywhere dashboard).

Following query generates sample data as per question:

 | makeresults
 | eval fieldname="ID|Name|2017-12 |2018-01|2018-02|2018-03"
 | eval data="X123|aaa|90|89|87|87;X234|bbb|89|91|77|99;X345|ccc|97|96|95|94"
 | makemv data delim=";" 
 | mvexpand data
 | eval data=split(data,"|")
 | eval ID=mvindex(data,0)
 | eval Name=mvindex(data,1)
 | eval 2017-12=mvindex(data,2)
 | eval 2017-11=mvindex(data,3)
 | eval 2017-01=mvindex(data,4)
 | eval 2017-02=mvindex(data,5)
 | table ID Name 2017-12 2017-11 2017-01 2017-02

Following dummy search generates current month and year table column header.

  <search>
    <query>| makeresults
| eval FilterYearMonth=strftime(_time,"%Y-%m")
    </query>
    <progress>
      <set token="tokYearMonth">$result.FilterYearMonth$</set>
    </progress>
  </search>

The token tokYearMonth is passed from dummy search to your query to filter and show only stats column for current month:

| fields ID Name $tokYearMonth$</query>

Please find below complete code for run anywhere dashboard:

<dashboard>
  <label>Filter Column for current month</label>
  <search>
    <query>| makeresults
| eval FilterYearMonth=strftime(_time,"%Y-%m")
| eval FilterYear=strftime(_time,"%Y")
| eval FilterMonthStr=lower(strftime(_time,"%B"))
|  eval FilterEpoch=replace(strptime(FilterYearMonth."-01 00:00:00","%Y-%m-%d %H:%M:%S"),"(\d+).(\d+)","\1")
    </query>
    <progress>
      <set token="tokYearMonth">$result.FilterYearMonth$</set>
      <set token="tokYear">$result.FilterYear$</set>
      <set token="tokMonth">$result.FilterMonthStr$</set>
      <set token="tokEpoch">$result.FilterEpoch$</set>
    </progress>
  </search>
  <row>
    <panel>
      <title>Stats for current Year-Month: $tokYearMonth$</title>
      <table>
        <search>
          <query>| makeresults
| eval fieldname="ID|Name|2017-12 |2018-01|2018-02|2018-03"
| eval data="X123|aaa|90|89|87|87;X234|bbb|89|91|77|99;X345|ccc|97|96|95|94"
| makemv data delim=";" 
| mvexpand data
| eval data=split(data,"|")
| eval ID=mvindex(data,0)
| eval Name=mvindex(data,1)
| eval 2017-12=mvindex(data,2)
| eval 2017-11=mvindex(data,3)
| eval 2017-01=mvindex(data,4)
| eval 2017-02=mvindex(data,5)
| table ID Name 2017-12 2017-11 2017-01 2017-02
| table ID Name $tokYearMonth$</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="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>
  <row>
    <panel>
      <title>Filter Results in Base Search - Year: $tokYear$| Month: $tokMonth$ | replace epoch Time $tokEpoch$ with String Time $tokYearMonth$ in Column Header</title>
      <table>
        <search>
          <query>index=_internal sourcetype=splunkd date_year="$tokYear$" date_month="$tokMonth$"
| chart count by component _time span=1mon
| fieldformat _time=strftime(_time,"%Y-%m")
| rename $tokEpoch$ as $tokYearMonth$</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="rowNumbers">false</option>
        <option name="totalsRow">false</option>
        <option name="wrap">true</option>
      </table>
    </panel>
  </row>
</dashboard>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

9738078959
Engager

below is my search string...

index=system_details | table "System ID",System_Name,Domain,"Task Priority","AM Fixed Fee"| rename "Task Priority" as tp | rename "AM Fixed Fee" AS fee | eval Slab=if((fee*12)>=600000,2,if((fee*12)<=400000,3,2.5)) | join "System ID" [search index=pro_tracker | rename "Product Name" as "System ID" | rename "Task Priority" as tp | eval P=if(tp="2-Major (B)" OR tp="1-Critical (A)",1,0) | stats sum(P) , sum("I8 Actual minutes") by "System ID",tp | rename sum("I8 Actual minutes") as down , sum(P) as countI| eval workhrs=[ search index=system_details | eval M=relative_time(now(),"-1d@d") | eval mh=strftime(M,"%Y-%m") | eval Flg=if(mh=Month,'Work Days',0) | eventstats max(Flg) as max | return $max]| eval downtime=(down-if((countI<2 OR workhrs*60=0),0,(down-(workhrs*60)+((workhrs*60)*POW(((workhrs*60)-1)/(workhrs*60),down)))))/60 | eval Availability=if(downtime>workhrs,0,(1-downtime/workhrs)) | where (tp="2-Major (B)" OR tp="1-Critical (A)")| eval scslab=if('2017_11'="BL","None",pow((('2017_11'-Availability)*100),2)*0.01)]

in place of '2017_11' it should automatically pickup the value of the column ('2017_11') corresponding to "System ID"

Thanks,
Ganesh Shetty

0 Karma

9738078959
Engager

I'm not using any dashboard. i just want to show this by running a query..

Thanks,
Ganesh Shetty

0 Karma

niketnilay
Legend

@9738078959 , can you share your current SPL which generates the data as per the sample provided in the question?

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

damien_chillet
Builder

I'm assuming you are talking about displaying the values in a dashboard where you can select the month.

The easiest way would be to have a dropdown input for the months with a token $month$.
For example for "December 2017", the token value would be "2017-12".

Your dashboard search query would end with

| fields ID Name $month|s$

to keep only the column which represents December 2017.
You would have to assign token values for each month manually.

You can find examples on how to use dropdown in a dashboard here:
https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/Viz/Buildandeditforms

You can also install the Splunk Dashboard app on your local instance and look for dropdown examples:
https://splunkbase.splunk.com/app/1603/

0 Karma

9738078959
Engager

Thanks damien_chillet 🙂

I'm not using drop down but it should automatically pick up the values of current month based on field heading(2017-12)..

0 Karma

damien_chillet
Builder

How about this:
Add the following to your existing search

| transpose
| eval isdate=if(match(column,"\d{4}-\d{2}"),1,0)
| eval month=strftime(now()+320000, "%Y-%m")
| where column=month OR isdate=0
| fields - isdate month
| transpose column_name=column header_field=column
| fields  - column

Note: I've added +32000 to now() to be and December and try it, to be removed after!

0 Karma

9738078959
Engager

Hi damien_chillet ,

Its not working:(

0 Karma

damien_chillet
Builder

It worked for me 😞
Can you try add the SPL lines one by one see where you start losing results?

0 Karma
.conf21 CFS Extended through 5/20!

Don't miss your chance
to share your Splunk
wisdom in-person or
virtually at .conf21!

Call for Speakers has
been extended through
Thursday, 5/20!