Making zero value buckets for Left join

[AshimaE]

I have to join 3 tables each of which have a common column with each other. However the problem is that I use Time Buckets to make each of the tables and since there are no time buckets for the 0 count spans it is leading to the join problems as expected. Any way to fill 0 in the empty time buckets for the first 1 atleast or any other way to get around with it so that the complete table is made since I need to make a timechart of the three results(overlay their respective time charts).
The query I have made is as follows:

index=xyz sourcetype=ml host=d1 message_type="error" | bucket span=10m _time | stats count as error_count by _time | join type=left _time [search index=xyz sourcetype=ml host=d1 message_type="info"| bucket span=10m _time | stats count as sent_count by _time] | table _time sent_count error_count | join type=left _time [search index=xyz sourcetype=ml host=d1 message_type="warning" | bucket span=10m _time | stats count as warn_count by _time] | fillnull value=0 | fillnull value=0 warn_count| table _time sent_count error_count warn_count

Any way to get around with the problem of missing time buckets(gaps) and solve the purpose is requested.

[somesoni2 · Accepted solution]

Assuming (based on your search) that all 3 searches uses same data source (index/sourceytpe), try like this (much more efficient as not using join/subsearch)

index=xyz sourcetype=ml host=d1 message_type="error" OR message_type="info" message_type="warning"
| bucket span=10m _time 
| chart count over _time by message
| rename error as error_count info as sent_count warning as warn_count
| fillnull value=0 sent_count warn_count error_count
| table _time sent_count error_count warn_count

Updated

index=xyz sourcetype=ml host=d1 (message_type="error" "error message 1" OR "error message 2"  OR "error message 3") OR message_type="info" OR (message_type="warning" "warning message 1" OR  "warning message 2" OR "warning message 3")
 | timechart span=10m by message_type
 | rename error as error_count info as sent_count warning as warn_count
 | fillnull value=0 sent_count warn_count error_count
 | table _time sent_count error_count warn_count

[AshimaE]

Thanks for the help. This works perfectly fine for all the cases now. Except 1 case wherein neither of the three columns had any events in the chosen time duration. Even if there is 1 event in any 1 it is working. Any way to handle this corner case more gracefully so that the visualization/table is easier to use.

Thanks

[AshimaE]

Also in this I need to handle the case when all 3 values are 0 in the bucket since I need to make a timechart and for that the gaps have to be filled in to give an accurate timechart.

[AshimaE]

Actually in this I further need to specify a string for the errors and the warnings.And give a unified count of the occurrences of the specified errors and warnings. Like if i take warning "error message 1" "error message 2" "error message 3" only and return the total count of these errors only along with the sent and warning ("warning message 1" "warning message 2" "warning message 3")as above then how can the above query be made to do the same. Previously I had planned to do

index=xyz sourcetype=ml host=d1 message_type="error" "error message 1" OR "error message 2"  OR "error message 3"| bucket span=10m _time | stats count as error_count by _time | join type=left _time [search index=xyz sourcetype=ml host=d1 message_type="info"| bucket span=10m _time | stats count as sent_count by _time] | table _time sent_count error_count | join type=left _time [search index=xyz sourcetype=ml host=d1 message_type="warning" "warning message 1" OR  "warning message 2" OR "warning message 3"| bucket span=10m _time | stats count as warn_count by _time] | fillnull value=0 | fillnull value=0 warn_count| table _time sent_count error_count warn_count

This was working fine. Just the problem with the left join was there.