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?
Stats values(sourcetype) values(Date) as date values(Username) values(Version) by number
|mvexpand date
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.
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..
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 |