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!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...