Hello, I have 1 single table that comes from two different searches/indexes/sourcetypes using append.
I need to join the results using 1 common field (name), in order to have per row all the information that I need.
The table I have is like:
+--------+--------+--------+------+----------+
| Date1 | Date2 | Date3 | name | Doc |
+--------+--------+--------+------+----------+
| Jan 08 |------- | --------- | A1 |----------|
| Jan 08 |------- | --------- | A2 |----------|
| Jan 08 |------- | --------- | A2 |----------|
| Jan 08 |------- | --------- | A3 |----------|
| Jan 08 |------- | --------- | A3 |----------|
| Jan 08 |------- | --------- | A3 |----------|
| Jan 08 |------- | --------- | A3 |----------|
| Jan 08 |------- | --------- | A3 |----------|
| Jan 08 |------- | --------- | A3 |----------|
| Jan 04 |------- | --------- | A4 |----------|
| Jan 09 |------- | --------- | A2 |----------|
|-----------| Jan2 | Jan09 | B1 | KYOM |
|-----------| Jan2 | Jan09 | A4 | UNYT |
|-----------| Jan2 | Jan09 | A1 | AGHY |
|-----------| Jan2 | Jan09 | A3 | POLK |
|-----------| Jan2 | Jan09 | B2 | TYHU |
+--------+--------+--------+------+----------+
And my goal is to end up with something like
+--------+---------+---------+--------+------+
| Date1 | Date2 | Date3 | name | Doc |
+--------+---------+---------+--------+------+
| Jan 08 | Jan2 | Jan09 | A1 | AGHY |
| Jan 08 | Jan2 | Jan09 | A2 |----------|
| Jan 08 | Jan2 | Jan09 | A2 |----------|
| Jan 08 | Jan2 | Jan09 | A3 | POLK |
| Jan 08 | Jan2 | Jan09 | A3 | POLK |
| Jan 08 | Jan2 | Jan09 | A3 | POLK |
| Jan 08 | Jan2 | Jan09 | A3 | POLK |
| Jan 08 | Jan2 | Jan09 | A3 | POLK |
| Jan 08 | Jan2 | Jan09 | A3 | POLK |
| Jan 04 | Jan2 | Jan09 | A4 | UNYT |
| Jan 09 | Jan2 | Jan09 | A2 |----------|
|----------| Jan2 | Jan09 | B1 | KYOM |
|----------| Jan2 | Jan09 | B2 | TYHU |
+--------+---------+---------+--------+------+
There is 1 and only 1 common field in the two searches, in the example the date match but is only for testing, it really never match. My search is like:
index=main sourcetype=test | many | many | many | condition
| append [search index=other | many | more | conditions]
I'm not using a single stats because it groups same name in 1 row (multivalue), and I need each result on its own row. Even tho they have same name, they are different events, that I'll evaluate per row after this table is generated.
Thanks.
I think the example is strange ...
If you concatenate Doc using name to data that Doc does not exist
(your search)
|streamstats count as no|eval no=if(isnull(Doc),0,no)
|stats list(*) as * by name
|fillnull value=""
|mvexpand Date1
|table Date1,Date2,Date3,name,Doc
I think the example is strange ...
If you concatenate Doc using name to data that Doc does not exist
(your search)
|streamstats count as no|eval no=if(isnull(Doc),0,no)
|stats list(*) as * by name
|fillnull value=""
|mvexpand Date1
|table Date1,Date2,Date3,name,Doc
Why strange? that's exactly how my data looks, but with real data, lol.
Anyways, your answer works like a charm, Thank you, I appreciate.
You say you don't want to use a single stats, but have you tried doing so and then using mvexpand
to put each result in its own row?
That's the thing, I don't know how to do so. I've tried m expand, but I guess I didn't do it correctly, I don't know how to use it to achieve my goal.
Try it. "join" use
index=main
| stats count by Date1 , Date2, Date3 , name , Doc
| join type=outer name [search index=other
| stats count by Date1 , Date2, Date3 , name , Doc ]
| table Date1 , Date2, Date3 , name , Doc
I prefer not to use join due performance issues. Thanks