topic Re: Stats multiple sourcetypes showing unique field question in Splunk Search

Stats multiple sourcetypes showing unique field question

PPrice — Mon, 01 Nov 2021 00:02:46 GMT

I'm trying to use a key across three sourcetypes to show unique non-multivalue rows using a stats by clause that has a different field in each of the sourcetypes
i.e.
Sourcetype A
NumberA(Key) Date (by clause)

Sourcetype B
NumberB(Key) Username (by clause)

Sourcetype C
NumberC(Key) Version (by clause)

if you use the number field, which is the key across the sourcetypes, as the stats by clause and add the different sourcetype fields as values, it produces multivalue fields (e.g. a number may have multiple dates, or users), where I'm looking for unique rows to show number, Date, Username, Version
e.g.
sourcetype=A OR sourcetype=B OR sourcetype=C
eval number=coalesce(NumberA, NumberB, NumberC)
stats values(sourcetype) values(Date) values(Username) values(Version) by number

I would have thought that you could add the different fields to the stats by clause after the key, but it's not returning anything-
e.g.
sourcetype=A OR sourcetype=B OR sourcetype=C
eval number=coalesce(NumberA, NumberB, NumberC)
stats values(sourcetype) by number Date Username Version

Would this make sense, and is possible?

Re: Stats multiple sourcetypes showing unique field question

vhharanpositka — Mon, 01 Nov 2021 03:15:01 GMT

Hi @PPrice

As per my understanding,

You can use "mvexpand" and "dedup" commands in search to get unique results in rows.

I tried this,

Thanks..

Re: Stats multiple sourcetypes showing unique field question

PPrice — Mon, 01 Nov 2021 12:22:48 GMT

The mvexpand didn't seem to work.

I'm trying to produce something like this that contains a unique row that has fields in all three Sourcetypes.

Sourcetype	number (From all sourcetypes)	Date (from SourcetypeA)	Username (from SourcetypeB)	Version (from SourcetypeC)
SourcetypeA SourcetypeB SourcetypeC	12345	12/12/2021	Fred	1.2
SourcetypeA SourcetypeB SourcetypeC	12345	13/12/2021	Fred	1.2
SourcetypeA SourcetypeB SourcetypeC	12345	14/12/2021	Fred	1.2

If I put
Stats values(sourcetype) values(Username) values(Version) by number,Date
I see these values returned-

Sourcetype	number(From all sourcetypes)	Date (from SourcetypeA)	Username (from SourcetypeB)	Version (from SourcetypeC)
SourcetypeA	12345	12/12/2021
SourcetypeA	12345	13/12/2021
SourcetypeA	12345	14/12/2021
SourcetypeB	12345		Fred
SourcetypeC	12345			1.2

If I put
Stats values(sourcetype) values(Date) values(Username) values(Version) by number
I see these values returned

Sourcetype	number(From all sourcetypes)	Date (from SourcetypeA)	Username (from SourcetypeB)	Version (from SourcetypeC)
SourcetypeA SourcetypeB SourcetypeC	12345	12/12/2021 13/12/2021 14/12/2021	Fred	1.2

Re: Stats multiple sourcetypes showing unique field question

tread_splunk — Mon, 01 Nov 2021 15:16:45 GMT

Stats values(sourcetype) values(Date) as date values(Username) values(Version) by number |mvexpand date

Re: Stats multiple sourcetypes showing unique field question

PPrice — Mon, 01 Nov 2021 19:03:41 GMT

Adding 'as date' in the stats allowed mvexpand to expand across the different sourcetypes, very nice.

Thanks for highlighting it; let me work with it some more and I'll Karma up.