Join between indexes and sum fields

[giulio]

Hi all, I have two indexes with the following fields:

index=sofware
sw                        version       author
software_1            1.0           Mark
software_2            1.1           Holly
software_3            1.2           Tom
software_4            1.3          Gorge

index=downloads
timestamp                                         sw
2021-11-23 00:00:00          software_1
2021-11-22 00:00:00          software_1
2021-11-21 00:00:00          software_4
2021-11-20 00:00:00          software_1
2021-11-19 00:00:00          software_3
2021-11-18 00:00:00          software_1

I need to create a report with the number of downloads for each software, something like this:

sw                     version                 author               #downloads
software_1       1.0                     Mark                               4
software_2       1.1                     Holly                               0
software_3       1.2                     Tom                                1
software_4       1.3                   Gorge                               1

I tried using left join but couldn't fine any good solution.
Thanks for helping.

[giulio · Accepted solution]

thanks all for your suggestions.

finally used this:

index="software"
| table sw version author
| join type=left sw [search index="downloads" | stats count by sw | table sw, count
| rename count as #downloads]
| fillnull value=0 #downloads

[richgalloway]

We might be able to fix the join you tried if you showed us the query and what didn't work about it.

As the manual says, however, join usually is not the best solution because it doesn't perform well.  Try using append, instead.

index=sofware
| append [ search index=downloads ]
| stats count by sw version author

[PickleRick]

We might even spare ourselves the append and do a simple alternative.

And I'm not sure if it shouldn't be aggregatted slightly differently.

index=software OR index=downloads
| stats count(timestamp) as downloads values(version) as version
values(author) as author by sw

But I'm not sure if your or mine aggregation is better because we simply don't see the full raw data.