Splunk Search

How to form a search based on my data and my desired output?

rsathish47
Contributor

Hi All,

I have the below format of data

Name    Value
1-Jan   A
2-Jan   B
2-Jan   B
3-Jan   C
2-Feb    A
1-Mar   V
2-Mar   B
3-Mar   C

Output needs to be like this

Name    Value
Jan   3
FEB   3
Mar      4

Please help me format the search.

Tags (2)
0 Karma

woodcock
Esteemed Legend

This one was fun (mostly because I enjoyed the brain-teaser of figuring out what exactly you were doing to get the shown outcome):

|makeresults | eval raw="1-Jan    A::2-Jan    B::2-Jan    B::3-Jan    C::2-Feb    A::1-Mar    V::2-Mar    B::3-Mar    C"
| makemv delim="::" raw
| mvexpand raw
| rename raw AS _raw
| rex "(?<Name>\S+)\s+(?<Value>.*)"
| fields - _raw _time

| rename COMMENT AS "Everything above is faking the data; Everthing below is your solution"

| rex field=Name mode=sed "s/^\d+\-//"
| eval Name=strftime(strptime(Name . " 1 2017", "%b %d %Y"), "%m")
| sort 0 Name
| stats values(Value) AS Value BY Name
| streamstats values(Value) AS Value
| stats dc(Value) AS Value BY Name
| fieldformat Name=strftime(strptime(Name . " 1 2017", "%m %d %Y"), "%b")
0 Karma

gokadroid
Motivator

Give this a try if you want to count based on the month and how many times it occurred:

your query to return the data as given in question
| rex field=Name "\-(?<month>.*)"
| stats count by month
| rename month as Name

Updating as per info given in comments

your query to return the data as given in question
| rex field=Name "\-(?<Name>.*)"
| streamstats dc(Value) as Value
| stats last(Value) by Name

rsathish47
Contributor

I tried it but it is not giving right count.

i want dedup month
Jan --> dedup till Jan --> 3
FEB --> dedup till Feb---> 3
Mar--> dedup till Mar---> 4

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try this (assuming format of field Name is %d-%b , date-month AND you want to count how many distinct values of field 'Value' is available in the month)

your current search giving field Name, Value
| eval Name=mvindex(split(Name,"-")-1) 
| stats dc(Value) as Value by Name

If this is not what you want, please provide more details on your requirement/data.

rsathish47
Contributor

Thank You for the query
I got below output but it is not correct output
Name Value
Feb 1
Jan 3
Mar 3

i want dedup month
Jan --> dedup till Jan --> 3
FEB --> dedup till Feb---> 3
Mar--> dedup till Mar---> 4
...
Please help me

0 Karma

somesoni2
SplunkTrust
SplunkTrust

What are your exact requirements? I get you're deduping but what are you doing exactly (more details)?

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

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