Splunk Search

Trying to get total count till selected year from multiselect input

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

Tags (1)
0 Karma

tjago11
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

renjith_nair
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>
Happy Splunking!
0 Karma

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

0 Karma

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

Happy Splunking!
0 Karma

piyali_sarkar
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
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

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