Does stats values command combine unique values?

[LearningGuy]

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

company	ip
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?

company	ip
companyA	1.1.1.1
companyB	1.1.1.2

Thank you so much

[gcusello]

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]

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

company	ip
companyA companyA	1.1.1.1
companyB companyB companyB	1.1.1.2

[PickleRick]

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]

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]

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

company	ip
companyA companyA	1.1.1.1
companyB companyB companyB	1.1.1.2

values command worked

company	ip
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

[gcusello]

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]

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

[gcusello · Accepted solution]

Hi @LearningGuy ,

sorry "it runs"

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

You have to try to use nomv and mvexpand.

Ciao.

Giuseppe

[yuanliu]

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