Splunk Search

How to write an efficient subsearch with or without coalesce?

Glasses
Builder

Need some advice writing a subsearch...

I have an index=email with two sourcetypes
sourcetype=MTA
sourcetype=MSG

both contain a field with a common value that ties the msg and mta logs together

for example
index=email sourcetype=MTA sm.qid
index=email sourcetype=MSG filter.qid

sm.qid = filter.qid for the same email session

I want to write an efficient search/subsearch that will correlate the two sourcetypes and also display multiple fields from each index.

I am at this point but wondering if I am going the right direction...

[search index=email sourcetype=MSG <some-search-criteria> | fields filter.id | rename filter.id as search] index=email sourcetype=MTA | table <some-fields>

This works partially but the sourcetypes MTA and MSG have different fields that I want to display together in the results...

For example sourcetype=MTA has fields: status,to,from,sm.qid
While sourcetype=MSG has fields:
messageID, subject, to, from, filter.qid

the "to" and "from" fields are slightly different and are formatted slightly different too...

I thought about coalesce but it is acting funny, I need to use ... | eval CommonID = coalesce('sm.qid','filter.qid') <<<< only single quotes work... and I am not sure the best way to use coalesce to grab all the fields from both sourcetype.

Any advice appreciated.
Thanks

Tags (2)
0 Karma
1 Solution

zacharychristen
Path Finder

I would recommend avoiding subsearches. They are really only good for certain circumstances and often negatively affect performance. Also, some special characters in field names can lead to issues during search time. It's recommended to change how these fields are being extracted using props and transforms.

For simplicity try this:

index=email (sourcetype=MSG OR sourcetype=MTA) 
| rename 'filter.qid' as filter_qid, 'sm.qid' as sm_qid
| eval qid=coalesce(filter_qid,sm_qid)
| stats count by qid

View solution in original post

0 Karma

zacharychristen
Path Finder

I would recommend avoiding subsearches. They are really only good for certain circumstances and often negatively affect performance. Also, some special characters in field names can lead to issues during search time. It's recommended to change how these fields are being extracted using props and transforms.

For simplicity try this:

index=email (sourcetype=MSG OR sourcetype=MTA) 
| rename 'filter.qid' as filter_qid, 'sm.qid' as sm_qid
| eval qid=coalesce(filter_qid,sm_qid)
| stats count by qid
0 Karma

Glasses
Builder

for some reason that did not produce results for qid ....
but I follow your code, makes sense, not sure why its not working

0 Karma

Glasses
Builder

just had to lose the single quote in the rename... thanks

0 Karma

nareshinsvu
Builder

If possible try to rename both common fileds to qid before indexing and try below query? And then go from there?

index=email sourcetype=MSG OR sourcetype=MTA | stats values(*) as * by qid
0 Karma

Glasses
Builder

Thank you for the reply, unfortunately that is not an option for me... but very good idea.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...