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
@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>
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
I'm not using any dashboard. i just want to show this by running a query..
Thanks,
Ganesh Shetty
@9738078959 , can you share your current SPL which generates the data as per the sample provided in the question?
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/
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)..
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!
Hi damien_chillet ,
Its not working:(
It worked for me 😞
Can you try add the SPL lines one by one see where you start losing results?