Splunk Search

Count distinct SPL help

thuhuongle
Explorer

Hi, 
Looking forward to learn from you guys. I am stucked at this calculation: Total of product in contract.
I made a simple dataset to simplify my data. 

 

| makeresults 
| eval date = "2017-01-30" , source = "a", id="111"
| makemv delim="," id
| makemv delim="," date
| makemv delim="," source
| append 
    [| makeresults 
| eval date = "2017-01-30" , source = "b", id="222"
| makemv delim="," id
| makemv delim="," date
| makemv delim="," source]
| append 
    [| makeresults 
| eval date = "2019-08-20" , source = "a", id="333"
| makemv delim="," id
| makemv delim="," date
| makemv delim="," source]
| append 
    [| makeresults 
| eval date = "2020-01-20" , source = "a", id="444"
| makemv delim="," id
| makemv delim="," date
| makemv delim="," source]
| append 
    [| makeresults 
| eval date = "2020-03-20" , source = "b", id="555"
| makemv delim="," id
| makemv delim="," date
| makemv delim="," source]

 

INPUT: let's image _time is date time of buying record

_timesigned contract dateidsource
2020-10-30 14:55:552017-01-30111a
2020-10-30 14:55:552017-01-30222b
2020-08-30 14:55:552019-08-20333a
2020-01-30 14:55:552020-01-20444a
2020-09-30 14:55:55 2020-03-20555b


The expected output: Count total product in contract from 12/2019 to 03/2020

Timetotal_nb_productSource
12/20192a
12/20191b
01/20203a
01/20201b
02/20203a
02/20201b
03/20203a
03/20202b

Thank for your time and hope to received your suggestion. 
Great weekend

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults 
| eval date = "2017-01-30" , source = "a", id="111"
| makemv delim="," id
| makemv delim="," date
| makemv delim="," source
| append 
    [| makeresults 
| eval date = "2017-01-30" , source = "b", id="222"
| makemv delim="," id
| makemv delim="," date
| makemv delim="," source]
| append 
    [| makeresults 
| eval date = "2019-08-20" , source = "a", id="333"
| makemv delim="," id
| makemv delim="," date
| makemv delim="," source]
| append 
    [| makeresults 
| eval date = "2020-01-20" , source = "a", id="444"
| makemv delim="," id
| makemv delim="," date
| makemv delim="," source]
| append 
    [| makeresults 
| eval date = "2020-03-20" , source = "b", id="555"
| makemv delim="," id
| makemv delim="," date
| makemv delim="," source]
| eval Time=round(relative_time(strptime(date,"%Y-%m-%d"),"@mon"))
| makecontinuous Time span=1mon
| fields - _time date
| eventstats values(source) as sources
| mvexpand sources
| eval source=coalesce(source, sources)
| eval id=if(source=sources,id,null)
| fields - source
| rename sources as Source
| stats values(id) as id by Time Source
| streamstats count(id) as total_nb_product by Source
| fields Time total_nb_product Source
| fieldformat Time=strftime(Time,"%m/%Y")

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults 
| eval date = "2017-01-30" , source = "a", id="111"
| makemv delim="," id
| makemv delim="," date
| makemv delim="," source
| append 
    [| makeresults 
| eval date = "2017-01-30" , source = "b", id="222"
| makemv delim="," id
| makemv delim="," date
| makemv delim="," source]
| append 
    [| makeresults 
| eval date = "2019-08-20" , source = "a", id="333"
| makemv delim="," id
| makemv delim="," date
| makemv delim="," source]
| append 
    [| makeresults 
| eval date = "2020-01-20" , source = "a", id="444"
| makemv delim="," id
| makemv delim="," date
| makemv delim="," source]
| append 
    [| makeresults 
| eval date = "2020-03-20" , source = "b", id="555"
| makemv delim="," id
| makemv delim="," date
| makemv delim="," source]
| eval Time=round(relative_time(strptime(date,"%Y-%m-%d"),"@mon"))
| makecontinuous Time span=1mon
| fields - _time date
| eventstats values(source) as sources
| mvexpand sources
| eval source=coalesce(source, sources)
| eval id=if(source=sources,id,null)
| fields - source
| rename sources as Source
| stats values(id) as id by Time Source
| streamstats count(id) as total_nb_product by Source
| fields Time total_nb_product Source
| fieldformat Time=strftime(Time,"%m/%Y")
0 Karma

thuhuongle
Explorer

Great ideal ITWhisperer,
It is exactly what i am looking for. I am developing a sum count between date, but your solution is straightforward and much better. Hope to see you arround very soon

0 Karma
Get Updates on the Splunk Community!

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...

Index This | What goes away as soon as you talk about it?

May 2025 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this month’s ...