Splunk Enterprise Security

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

anubhp
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

manjunathmeti
Champion

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

anubhp
New Member

Hi tried it, but not working

0 Karma

manjunathmeti
Champion

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

0 Karma

anubhp
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

manjunathmeti
Champion

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

anubhp
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

anubhp
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
Get Updates on the Splunk Community!

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...