Getting Data In

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
Engager

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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
---
If this reply helps you, Karma would be appreciated.
0 Karma

ivanlesk
Engager

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.

0 Karma
Get Updates on the Splunk Community!

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...