Splunk Search

Does stats values command combine unique values?

LearningGuy
Builder

Hello,
Does stats values command combine unique values?
For example:

companyip
companyA
companyA
1.1.1.1
companyB
companyB
companyB
1.1.1.2



index=regular_index 
| stats values(company) by ip
| table company, ip

Should the command above produce the following output?

companyip
companyA1.1.1.1
companyB1.1.1.2


Thank you so much

 

Labels (1)
0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi @LearningGuy ,

sorry "it runs"

I meant that I cannot test your search because if I take the values from your page it runs

gcusello_0-1698677509778.png

You have to try to use nomv and mvexpand.

Ciao.

Giuseppe

View solution in original post

gcusello
SplunkTrust
SplunkTrust

Hi @LearningGuy ,

yes your search give you a list of distinct values by ip:

index=regular_index 
| stats values(company) AS company BY ip
| table company ip

but if you don't use "AS company" you don't have this field in the following table command.

Is this your question or do you have other doubt?

Ciao.

Giuseppe

LearningGuy
Builder

Hi @gcusello 

Yes, this answered my question, but I have other doubt.
Values command does not work if the data got merged into one row after "summary index". 
Please see below example and picture. Please suggest. Thanks

companyip
companyA companyA1.1.1.1
companyB companyB companyB1.1.1.2



summary_index2.jpg

0 Karma

PickleRick
SplunkTrust
SplunkTrust

You're again digging into the issue we're tackling in this thread:

https://community.splunk.com/t5/Reporting/summary-index-merges-multiple-line-values-into-one-row/m-p...

Due to how multivalued fields are "flattened" when collected to a stash sourcetype, your summarized events really do have the values of "companyA companyA" and "companyB companyB companyB".

 

gcusello
SplunkTrust
SplunkTrust

Hi @LearningGuy ,

to have all the values in the same row, you have to add the nomv command (https://docs.splunk.com/Documentation/Splunk/9.1.1/SearchReference/Nomv) after the stats command:

index=regular_index 
| stats values(company) AS company BY ip
| nomv company
| table company ip

 Ciao.

Giuseppe

LearningGuy
Builder

Hi @gcusello ,

Sorry if I wasn't clear.   
If you refer to the drawing I posted previously.
The issue is actually the opposite.   After I moved the **commands/searches** into summary index, the data was merged into one row, so the values command did not give me unique values 
I expected to get "companyA", but it gave me "companyA companyA" because of the Carriage Return ("\n")

Values command did not work

companyip
companyA companyA1.1.1.1
companyB companyB companyB1.1.1.2



values command worked

companyip
companyA
companyA
1.1.1.1
companyB
companyB
companyB
1.1.1.2


I also have a different post specifically discussed about why summary index caused this merge behavious
https://community.splunk.com/t5/Reporting/summary-index-merges-multiple-line-values-into-one-row/m-p...
1) Why values command does not work if the data gets merged into one line?
2) Why does summary index cause merging into one row in the first place?

Thank you so much for your help


0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @LearningGuy ,

you should try to use mvexpand and nomv commands.
I cannot test because iy runs using values from a text page.

Ciao.

Giuseppe

LearningGuy
Builder

Hi @gcusello ,

What do you mean by "iy runs using values from a text page"?

So, values won't work if "\" gets merged into one line and I should use mvexpand to fix this?

Any idea on the root cause why it happened after summary index?

Thanks

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi @LearningGuy ,

sorry "it runs"

I meant that I cannot test your search because if I take the values from your page it runs

gcusello_0-1698677509778.png

You have to try to use nomv and mvexpand.

Ciao.

Giuseppe

yuanliu
SplunkTrust
SplunkTrust

The answer is revealed in documentation of values.  Use the "AS" modifier.  If you know that each IP only corresponds to one company, the following will do the trick:

index=regular_index 
| stats values(company) as company by ip
| table company, ip
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...