I am using :join" query to show one table with different columns from different sourcetypes. However some of the sourcetypes does not contain the field which I am using for join. In this case I found the table does not get displayed for all. I want atleast the sourcetype which have that field, should display the data and other which dont have data should display "0" there.
e.g. sourcetype="ABC" K1 id!="null" | stats dc(id) as totalin BY FIELD1 |
sourcetype="DEF" K2 id!="null" | stats dc(id) as totalout BY FIELD1] |
sourcetype="KLM" K3 id!="null" | stats dc(id) as totallost BY FIELD1] |
sourcetype="XYZ" K4 id!="null" | stats dc(id) as totalrec BY FIELD1] |
table FIELD1, totalin, totalout, totallost, totalrec
Here sourcetype="KLM" does not have "id" field in the available test data and sourcetype="XYZ" does not have "field1" in the available test data.
But I want a single table which should have all these values irrespective of 0 data.
well if XYZ doesnt have any values for field1, but you still want it to join the other sets, you'll need to do an 'outer' join, or 'left' join. So replace your all your | join commands with | join type="outer"
However join is really intended as a last resort. There is almost always a better way to do the same thing without join, and it looks like the case here.
1) More elegant.
If you can take a step back and define a field extraction, I strongly recommend that. Lets say you do this and let's call the new field "kValue", whose values are of course "K1", "K2", "K3", and "K4". Once you have that then this whole thing boils down to:
( sourcetype=ABC K1) OR ( sourcetype=DEF K2 ) OR ( sourcetype=KLM K3 ) OR ( sourcetype=XYZ K4 )
| chart dc(id) over field1 by kValue | rename k1 as totalin k2 as totalout k3 as totallost k4 as totalrec
2) a different approach, but one that doesnt require any field extraction
sourcetype=ABC OR sourcetype=DEF OR sourcetype=KLM OR sourcetype=XYZ
| eval inCount =if(searchmatch("sourcetype=ABC K1"),1,0)
| eval outCount =if(searchmatch("sourcetype=DEF K2"),1,0)
| eval lostCount=if(searchmatch("sourcetype=KLM K3"),1,0)
| eval recCount =if(searchmatch("sourcetype=XYZ K4"),1,0)
| stats sum(inCount) as totalin sum(outCount) as totalout
sum(lostCount) as totallost sum(recCont) as totalrec by FIELD1
Either approach is favorable to approaches that do multiple joins.
Interesting, Well that definitely makes #2 uglier. take a look though - I updated them both. I still recommend extracting the K's as a field though if you can, because stuff like #1 is so much easier to build with.
Thanks Nick! I used join type="outer" and it worked so fine. Thank you so much.
However I have one doubt in the answer 1 and 2. How can we use sourcetype=ABC OR sourcetype=DEF OR sourcetype=KLM OR sourcetype=XYZ directly because each sourcetype can contain K1, K2, K3 or K4. So there can be different combinations. I used outer join because I want K1 only from ABC, K2 only from DEF and so on.