Splunk Search

Query to get the result from one sourcetype and get other field values based on the output from other sourcetype

sahil237888
Path Finder

Need help in creating a query to get the result from one sourcetype and get other field values based on the output from the first sourcetype.

For. e.g

I am having the below output which shows the transaction_id as a result but the username corresponding to that transaction ID should be fetched from another sourcetype.

Result of query should be - 

_time hostname transaction_id username city
1:30AM server1 TEST cron_user US

1:31AM server2 TEST1 cron2_user CA

 

In above transaction_id is the field present in both sourcetype

hostname and transaction_id is coming from one sourcetype.

matching with specfific transaction_id , username and city should be fetched from sourcetype2

 

Labels (3)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

Something like

sourcetype IN (sourcetype1, sourcetype2)
| stats max(_time) as _time values(hostname) as hostname values(username) as username values(city) as city by transaction_id
Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Your transaction ids don't match, but assuming they did, you could try this

| eval hostname=if(sourcetype="sourcetype1",hostname,null())
| eval user=if(sourcetype="sourcetype2",user,null())
| eval city=if(sourcetype="sourcetype2",city,null())
| stats values(hostname) as hostname values(user) as user values(city) as city by transaction_id
0 Karma
Get Updates on the Splunk Community!

Webinar Recap | Revolutionizing IT Operations: The Transformative Power of AI and ML ...

The Transformative Power of AI and ML in Enhancing Observability   In the realm of IT operations, the ...

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...