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!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...