Splunk Search

Join fields with different field names and stats value?

sumarri
Path Finder

So, I have one source (transactions) with userNumber and another source (users) with number. I want to join both of them. In each source, they have different field names. I want my table to have the employees name, which in in source users, which I get in my 2nd query in the join separately. Below is my SPL as of now:

index=* sourcetype=transaction 
| stats dc(PARENT_ACCOUNT) as transactionMade by POSTDATE, USERNUMBER
| join left=L right=R where L.USERNUMBER=R.NUMBER [search sourcetype=users | stats values(NAME) as Employee by NUMBER]
| table USERNUMBER Employee PARENT_ACCOUNT POSTDATE transactionMade 



What is it that I am doing wrong?

Labels (2)
0 Karma

sumarri
Path Finder

I was not getting any data together, that was what was wrong... sorry for the miscommunication. I implemented your idea, and I am getting data now! Thank you!!!

However, I am not getting the  Employee column filled. It might because of the data issue. But, I wanted to know if we can label the fields by source. For example. I have UserNumber in both sources that mean different things and name in both sources that mean different things... How can I help Splunk differentiate them? Is there any resources would you suggest?

Thank you so much!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this

index=* sourcetype=transaction OR sourcetype=users
| eval CommonNumber=if(sourcetype="transaction", USERNUMBER, NUMBER)
| eventstats values(NAME) as Employee by CommonNumber
| stats dc(PARENT_ACCOUNT) as transactionMade values(Employee) as Employee values(USERNUMBER) as USERNUMBER by POSTDATE, CommonNumber
| table CommonNumber USERNUMBER Employee PARENT_ACCOUNT POSTDATE transactionMade
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

It depends what it is you are trying to do, and what you think is wrong. As it stands, PARENT_ACCOUNT is not a field beyond the stats command (since it isn't listed as an output field - dc just counts the distinct values of the field without listing them).

For the "join", you don't need a join (and they usually should be avoided if possible as they are slow and have limitations). Try something like this:

index=* sourcetype=transaction OR sourcetype=users
| eval USERNUMBER=coalesce(USERNUMBER, NUMBER)
| eventstats values(NAME) as Employee by USERNUMBER
| stats dc(PARENT_ACCOUNT) as transactionMade values(Employee) as Employee by POSTDATE, USERNUMBER
| table USERNUMBER Employee PARENT_ACCOUNT POSTDATE transactionMade
Get Updates on the Splunk Community!

Earn a $35 Gift Card for Answering our Splunk Admins & App Developer Survey

Survey for Splunk Admins and App Developers is open now! | Earn a $35 gift card!      Hello there,  Splunk ...

Continuing Innovation & New Integrations Unlock Full Stack Observability For Your ...

You’ve probably heard the latest about AppDynamics joining the Splunk Observability portfolio, deepening our ...

Monitoring Amazon Elastic Kubernetes Service (EKS)

As we’ve seen, integrating Kubernetes environments with Splunk Observability Cloud is a quick and easy way to ...