Splunk subsearch to find an event from a source if it is present in another source is not working

[anubhp]

I have a query that looks for data from one source only if it is present in another source. It was working fine before. Suddenly it stopped working. Not sure why. Please find the query below.

sourcetype="ms:o365:reporting:messagetrace" NOT SenderAddress=company.com NOT Status= [search index=notable source="Threat - Detect Spam Email - Rule" | fields Subject] | stats dc(RecipientAddress) as recipientcount count by Subject SenderAddress
| where recipientcount > 10

The query should give results only if the event with the same Subject is available in the subsearch. However, this is giving result even though the event is not present in the subsearch.

[manjunathmeti]

Try this query:

sourcetype="ms:o365:reporting:messagetrace" NOT SenderAddress=company.com NOT [search index=notable source="Threat - Detect Spam Email - Rule" | stats count by Subject | fields Subject | format] | stats dc(RecipientAddress) as recipientcount count by Subject SenderAddress | where recipientcount > 10

[anubhp]

Hi tried it, but not working

[manjunathmeti]

Can you explain what is not working? May be with some sample data.

[anubhp]

Query1: index=notable source="Threat - Detect Spam Email - Rule"

Query1 has data of spam emails reported by users. It has events with fields "Subject"

For Eg: Subject = "Spam Email Notification"

Query2: sourcetype="ms:o365:reporting:messagetrace" NOT SenderAddress=company.com

Query2 have more details such as sender address and recipient details. I want to take the Subject from Query1 and search for events in Query2 with the same Subject and return results.

For Eg: If event with Subject "Spam Email Notification" is present is query1, i want to search for the same Subject in query2

[manjunathmeti]

Is field name Subject same in both Query1 and Query2? If yes, then it's something to do with values from sub-search (Query1) not matching Query2. Try this.

 sourcetype="ms:o365:reporting:messagetrace" NOT SenderAddress=company.com NOT [search index=notable source="Threat - Detect Spam Email - Rule" | stats count by Subject | eval Subject="*".Subject."*" | fields Subject | format] | stats dc(RecipientAddress) as recipientcount count by Subject SenderAddress | where recipientcount > 10

[anubhp]

Yes. Both queries have field name called Subject. The query is working fine with join command.

With subsearch it was working till yesterday. suddenly it stopped working generated thousands of false alerts.

Working Join query

(sourcetype="ms:o365:reporting:messagetrace" NOT company.com Status=* ) | join type=inner [search index=notable source="Threat - Detect Spam Email - Rule" | fields Subject]
| stats dc(RecipientAddress) as recipientcount count by Subject SenderAddress
| where recipientcount > 10

i don't want to use a join command since affects search performance

[anubhp]

I have identified the issue. There was an email with Subject as "**". Because of this, it was taking all the results.