How to combine multiple, different fields from 2 different sourcetypes into 1 stats table?

[Earenhart]

I have been searching through all of the similar questions on this site, and I believe my problem is that I have 2 different logging sources that have values I need, but the fields do not match. I have tried several subsearches, tried to coalesce field 1 and 3 (because they are the same information, just named differently grrrr), and I have been able to produce results with some of the dozens of iterations I've tried, but none of them are producing the combined table that I am looking for.

Here is the current (and probably simplest, to illustrate what I am trying to do) iteration of my search:

sourcetype=1 | rename field1 as Session_ID | append [search sourcetype=2 | rename field2 as Username | rename field3 as Session_ID] | stats count by sum(field4_size_in_bytes), Username, Session_ID, url | sort - sum(field4_size_in_bytes)

What I am trying to do with this search:

1. Begin with sourcetype1, and rename field1 (which only exists in sourcetype 1) to Session_ID.
2. Then I am trying to take field2 (which exists only in sourcetype2) and rename that to Username.
3. With field3, the name of the field itself is different on sourcetype2 than what is on sourcetype1, although the actual data is the same as field1. So I need to merge these two fields if possible (hence my attempts to use coalesce).
4. Then I want to have these fields all listed out in a statistical chart, in order, based on the field4 size in bytes.

I know this could probably be mitigated by simply renaming the fields in splunk to match each other, but that isn't really an option right now for reasons that would be too difficult to explain here.

[elliotproebstel]

Here's an example that should properly use the coalesce function to meet your goals:

sourcetype=1 OR sourcetype=2 
| eval Session_ID=coalesce(field1, field3) 
| rename field2 AS Username  
| stats sum(field4_size_in_bytes) AS field4_size_in_bytes_sum, count BY Username, Session_ID, url 
| sort 0 - field4_size_in_bytes_sum

This will give you the sum of field4, calculated per instance of [Username, Session_ID, url]. If you'd like to calculate it per Session_ID only, you might try this:

sourcetype=1 OR sourcetype=2 
| eval Session_ID=coalesce(field1, field3) 
| rename field2 AS Username  
| stats sum(field4_size_in_bytes) AS field4_size_in_bytes_sum, values(Username) AS Username, values(url) AS url, count BY Session_ID
| sort 0 - field4_size_in_bytes_sum

[Earenhart]

Hi Elliotproebstel,

This didn't give me the output I was looking for, but it does help clear up the use of the coalesce command, so thank you! I would give you points, but it says I can't because I don't have enough.

Edit: The second search actually did work, so for anyone looking at this, both the accepted answer and this answer worked. I wish I could accept them both lol.

[deepashri_123 · Accepted solution]

Hey@Earenhart,

Can you try something like this:
index=yourindex sourcetype=sourcetype1 OR sourcetype=sourcetype2 | rename field2 AS Username field3 AS SESSION_ID field1 AS SESSION_ID | stats count sum(field4) AS field4 values(Username) AS Username values(url) AS url by SESSION_ID

Let me know if this helps!!

[Earenhart]

Hi Deepashri,

This worked perfectly! All I had to do was sort it at the end by the size in bytes, and viola! Thanks so much! If you post your answer separately I will click accept so you can get points (not sure how that works yet, still pretty new here).

[deepashri_123]

Hey@Earenhart,
Glad it worked!!Please accept the answer