How to join multiple sourcetypes with additional data from a CSV using a common field (userId), then calculate the average for 2 possible fields by userId?

[ivanlesk]

Hi,

We have 3 sourcetypes with similar data (column names are different e.g. RATE_DOWN in two of them and ACTUAL_DATA_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 ACTUAL_DATA_RATE_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 ACTUAL_DATA_RATE_DOWN field ( avg(ACTUAL_DATA_RATE_DOWN)).

avg(RATE_DOWN) is always empty, and if I remove sourcetypes leaving just one with RATE_DOWN field, stats are calculated.

[richgalloway]

Try this.

 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

[ivanlesk]

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.