Archive
Highlighted

Trying to get total count till selected year from multiselect input

New Member

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 curyear=tonumber($yearvalue$) --> this is not allowing me to select more than one year.
| eval countstatus=if(Status="Active" AND year <= curyear,1,0)
| stats sum(countstatus) as countstatus by Supplier

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

Tags (1)
0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

@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

  1. Use , as delimiter in your multiselect
  2. 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>
0 Karma
Highlighted

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

New Member

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 countstatus=if(Status="Active" AND year <= curyear,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 ?

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

@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 ?

0 Karma
Highlighted

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

New Member

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 ?

0 Karma
Highlighted

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

Communicator

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

0 Karma