https://community.splunk.com/t5/Getting-Data-In/How-to-join-multiple-sourcetypes-with-additional-data-from-a-CSV/m-p/253501#M48686 <P>Hi, </P> <P>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)</P> <P>Events are collected at the same time, but from different devices (one sourcetype per device type). And every event contains a user ID.</P> <P>We have an additional CSV file with list of user IDs and some additional data (rate limits etc.).</P> <P>I want to calculate avg <STRONG>RATE_DOWN</STRONG> or <STRONG>ACTUAL_DATA_RATE_DOWN</STRONG> fields per user from the CSV with additional data. I tried this:</P> <PRE><CODE>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 </CODE></PRE> <P>I've expected to get a list of all users from the CSV file and their stats, <CODE>avg(RATE_DOWN)</CODE> if that field for that user exists, or else <CODE>avg(ACTUAL_DATA_RATE_DOWN)</CODE> and the first field should be empty. But instead, I got only stats for users that have rate in ACTUAL_DATA_RATE_DOWN field ( <CODE>avg(ACTUAL_DATA_RATE_DOWN)</CODE>).</P> <P><CODE>avg(RATE_DOWN)</CODE> is always empty, and if I remove sourcetypes leaving just one with RATE_DOWN field, stats are calculated.</P> Tue, 29 Sep 2020 08:35:08 GMT ivanlesk 2020-09-29T08:35:08Z https://community.splunk.com/t5/Getting-Data-In/How-to-join-multiple-sourcetypes-with-additional-data-from-a-CSV/m-p/253501#M48686 <P>Hi, </P> <P>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)</P> <P>Events are collected at the same time, but from different devices (one sourcetype per device type). And every event contains a user ID.</P> <P>We have an additional CSV file with list of user IDs and some additional data (rate limits etc.).</P> <P>I want to calculate avg <STRONG>RATE_DOWN</STRONG> or <STRONG>ACTUAL_DATA_RATE_DOWN</STRONG> fields per user from the CSV with additional data. I tried this:</P> <PRE><CODE>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 </CODE></PRE> <P>I've expected to get a list of all users from the CSV file and their stats, <CODE>avg(RATE_DOWN)</CODE> if that field for that user exists, or else <CODE>avg(ACTUAL_DATA_RATE_DOWN)</CODE> and the first field should be empty. But instead, I got only stats for users that have rate in ACTUAL_DATA_RATE_DOWN field ( <CODE>avg(ACTUAL_DATA_RATE_DOWN)</CODE>).</P> <P><CODE>avg(RATE_DOWN)</CODE> is always empty, and if I remove sourcetypes leaving just one with RATE_DOWN field, stats are calculated.</P> Tue, 29 Sep 2020 08:35:08 GMT https://community.splunk.com/t5/Getting-Data-In/How-to-join-multiple-sourcetypes-with-additional-data-from-a-CSV/m-p/253501#M48686 ivanlesk 2020-09-29T08:35:08Z https://community.splunk.com/t5/Getting-Data-In/How-to-join-multiple-sourcetypes-with-additional-data-from-a-CSV/m-p/253502#M48687 <P>Try this.</P> <PRE><CODE> 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 </CODE></PRE> Mon, 01 Feb 2016 15:51:05 GMT https://community.splunk.com/t5/Getting-Data-In/How-to-join-multiple-sourcetypes-with-additional-data-from-a-CSV/m-p/253502#M48687 richgalloway 2016-02-01T15:51:05Z https://community.splunk.com/t5/Getting-Data-In/How-to-join-multiple-sourcetypes-with-additional-data-from-a-CSV/m-p/253503#M48688 <P>thx, but no help.</P> <P>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.</P> Mon, 01 Feb 2016 15:59:52 GMT https://community.splunk.com/t5/Getting-Data-In/How-to-join-multiple-sourcetypes-with-additional-data-from-a-CSV/m-p/253503#M48688 ivanlesk 2016-02-01T15:59:52Z