topic Re: How to form a search based on my data and my desired output? in Splunk Search

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

rsathish47 — Thu, 22 Dec 2016 05:49:19 GMT

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.

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

somesoni2 — Thu, 22 Dec 2016 05:54:47 GMT

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.

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

gokadroid — Thu, 22 Dec 2016 06:35:32 GMT

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

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

rsathish47 — Thu, 22 Dec 2016 06:46:31 GMT

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

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

rsathish47 — Thu, 22 Dec 2016 07:23:23 GMT

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

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

somesoni2 — Thu, 22 Dec 2016 07:39:01 GMT

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

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

woodcock — Sat, 25 Mar 2017 06:41:07 GMT

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")