topic Query to get the result from one sourcetype and get other field values based on the output from other sourcetype in Splunk Search

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

sahil237888 — Tue, 22 Aug 2023 11:08:02 GMT

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

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

ITWhisperer — Tue, 22 Aug 2023 11:16:40 GMT

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

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

yuanliu — Wed, 23 Aug 2023 07:14:25 GMT

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