topic Re: Trying to get total count till selected year from multiselect input in Splunk Search

Trying to get total count till selected year from multiselect input

piyali_sarkar — Wed, 30 Sep 2020 02:06:39 GMT

Hi All,

I am trying to display total active users count till selected year.
I could achieve this , if I select only one year at a time, but if i try to select more that 1 year , I am getting error .

Query using :

base query
| eval cur_year=tonumber($year_value$) --> this is not allowing me to select more than one year.
| eval count_status=if(Status="Active" AND year <= cur_year,1,0)
| stats sum(count_status) as count_status by Supplier

Could anyone please let me know if there is a way to display total count till multiple years.

Re: Trying to get total count till selected year from multiselect input

renjith_nair — Wed, 11 Sep 2019 09:07:27 GMT

@piyali_sarkar,

Not sure why you have a multiselect instead of a simple dropdown because you want the count until the lowest/highest value of year from the selected year values.

Nevertheless, you can find the min or max from the selected years and substitute that value in your eval function

Use , as delimiter in your multiselect
Change the eval to | eval cur_year=tonumber(min($year_value$) )

Here is a run anywhere example

<form>
  <fieldset submitButton="false">
    <input type="multiselect" token="field1">
      <label>field1</label>
      <choice value="2019">2019</choice>
      <choice value="2018">2018</choice>
      <choice value="2017">2017</choice>
      <default>2019</default>
      <initialValue>2019</initialValue>
      <delimiter>,</delimiter>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <search>
          <query>| makeresults| eval year="2019"| eval Status=if (year > tonumber(min($field1$)),1,0)</query>
          <earliest>-15m</earliest>
          <latest>now</latest>
        </search>
        <option name="drilldown">none</option>
      </table>
    </panel>
  </row>
</form>

Re: Trying to get total count till selected year from multiselect input

piyali_sarkar — Wed, 30 Sep 2020 02:06:45 GMT

Hi Renjith,

Thank you for your response. Apology if I was not clear enough.
I don't want to get the min or Max value , I will try to explain the query below:
1.base query >> In my base query , there are fields like,
Status (Active/Inactive),
Supplier (FALSE/TRUE) ,
Originated date (I am extracting year from this field)
2. I have dynamic query to generate the year from the indexed data,
| eval count_status=if(Status="Active" AND year <= cur_year,1,0)
in the above line , "cur_year" value if the year field token value , so this
query is calculating total "ACTIVE" users from start to the year selected.

My problem here is , when I selecting one year for ex. 2016 ,the query is displaying total active user count till 2016 from starting , but if I select 2016 as well as 2018, the eval command is throwing error.
So I want to display total active users till 2016 as well as 2018
Is it possible ?

Re: Trying to get total count till selected year from multiselect input

renjith_nair — Wed, 11 Sep 2019 12:17:22 GMT

@piyali_sarkar,
Sorry but what do you mean by " I want to display total active users till 2016 as well as 2018" ?
Because when you select 2018 , it includes both 2016,2017 and 2018
Do you want count by the year ?

Re: Trying to get total count till selected year from multiselect input

piyali_sarkar — Wed, 11 Sep 2019 12:37:24 GMT

yes, Actually if you see my query , I am calculating total active user count till 2016 , So If I want display the count till 2016 and then till 2018 , just to see the diff between the total active user count of these two years, Is it possible in one query ?
Suppose in 2016 total active user count is 3K and in 2018 it's 6K
Can I display both counts using one query ?

Re: Trying to get total count till selected year from multiselect input

tjago11 — Wed, 11 Sep 2019 19:59:18 GMT

So, if you are trying to find the sum of values for the given years you'll need to get pretty creative. The way I read your question means you want results for each of the years selected, so if they pick 2018 and 2019 then you'll have two results.

Here is something that runs against the main index, the top section you would have to fix for using your Multi-Select. Should work:

| makeresults 
| eval firstDay= "09/09/2019"
| eval secondDay= "09/10/2019"
| eval thirdDay="09/11/2019"
| eval daysCombined = firstDay.",".secondDay.",".thirdDay 
| makemv daysCombined delim="," 
| fields daysCombined 
| eval groupBy="foo" 
| append 
    [| tstats count where index=main earliest=-3d@d by _time span=1d 
    | eval formattedTime = strftime(_time, "%m/%d/%Y") 
    | eval groupBy="foo" 
    | eval {formattedTime}Count = count 
    | stats first(*Count) as *Count by groupBy
        ] 
| stats list(*) as * by groupBy
| mvexpand daysCombined
| foreach *Count [eval <<MATCHSTR>>Included = if(strptime("<<MATCHSTR>>", "%m/%d/%Y") >= strptime(daysCombined, "%m/%d/%Y"), '<<FIELD>>', 0)]
| stats sum(*Included) as *

Enjoy!!