We have 3 sourcetypes with similar data (column names are different e.g. RATEDOWN in two of them and ACTUALDATA_RATE in one of them)
Events are collected at the same time, but from different devices (one sourcetype per device type). And every event contains a user ID.
We have an additional CSV file with list of user IDs and some additional data (rate limits etc.).
I want to calculate avg RATE_DOWN or ACTUALDATARATE_DOWN fields per user from the CSV with additional data. I tried this:
index=index (sourcetype=sourcetype1 OR sourcetype=sourcetype2 OR sourcetype=sourcetype3) | join type=inner userId [ |inputcsv additional_data] | stats avg(RATE_DOWN) avg(ACTUAL_DATA_RATE_DOWN) by userId
I've expected to get a list of all users from the CSV file and their stats,
avg(RATE_DOWN) if that field for that user exists, or else
avg(ACTUAL_DATA_RATE_DOWN) and the first field should be empty. But instead, I got only stats for users that have rate in ACTUALDATARATEDOWN field ( `avg(ACTUALDATARATEDOWN)`).
avg(RATE_DOWN) is always empty, and if I remove sourcetypes leaving just one with RATE_DOWN field, stats are calculated.
index=index (sourcetype=sourcetype1 OR sourcetype=sourcetype2 OR sourcetype=sourcetype3) | join type=inner userId [ |inputcsv additional_data] | eval rate_down = coalesce(RATE_DOWN, ACTUAL_DATA_RATE_DOWN) | stats avg(rate_down) by userId
thx, but no help.
again only stats from sourcetype3 are shown (IDs for other users are shown but no stats). If I remove sourcetype3 from first line than results for those users are shown.