topic Re: Does stats values command combine unique values? in Splunk Search

Does stats values command combine unique values?

LearningGuy — Mon, 30 Oct 2023 02:25:28 GMT

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

Re: Does stats values command combine unique values?

yuanliu — Mon, 30 Oct 2023 03:34:23 GMT

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

Re: Does stats values command combine unique values?

gcusello — Mon, 30 Oct 2023 07:11:43 GMT

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

Re: Does stats values command combine unique values?

LearningGuy — Mon, 30 Oct 2023 13:32:08 GMT

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

Re: Does stats values command combine unique values?

gcusello — Mon, 30 Oct 2023 13:40:40 GMT

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

Re: Does stats values command combine unique values?

LearningGuy — Mon, 30 Oct 2023 13:58:59 GMT

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/666673#M12266
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

Re: Does stats values command combine unique values?

gcusello — Mon, 30 Oct 2023 14:18:37 GMT

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

Re: Does stats values command combine unique values?

LearningGuy — Mon, 30 Oct 2023 14:42:31 GMT

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

Re: Does stats values command combine unique values?

PickleRick — Mon, 30 Oct 2023 14:51:12 GMT

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/666626#M12263

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

Re: Does stats values command combine unique values?

gcusello — Mon, 30 Oct 2023 14:52:30 GMT

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