Splunk Enterprise Security
Highlighted

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

New Member

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.

0 Karma
Highlighted

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

Influencer

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
0 Karma
Highlighted

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

New Member

Hi tried it, but not working

0 Karma
Highlighted

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

Influencer

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

0 Karma
Highlighted

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

New Member

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

0 Karma
Highlighted

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

Influencer

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
0 Karma
Highlighted

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

New Member

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

0 Karma
Highlighted

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

New Member

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

0 Karma