Subsearch with results from my search

[bkirk]

I have the following search to give me top email domains/servers that have been blocked by our email RBL. I would like to key in on the ones that were being accepted. So obviously I don't care about the spammer that has been trying to spam me all day but I want to know if someone who was sending me email and all of the sudden was getting blocked.

sourcetype="postfix_syslog" NOT error_code=554 NOQUEUE
| rex field=from "@(?<fromdomain>.*)" 
| stats count(from) as count by fromdomain,ip_address,from 
| sort -count
| streamstats count as counter by fromdomain,ip_address
| stats sum(count) as "Total Count" list(eval(if(counter<=5,from,null()))) as "Top Users" by fromdomain,ip_address 
| sort 25 -"Total Count"

Basically what I would like to add to this search is a subsearch for the count of records for the same time period that have the same ip_address, but that weren't blocked. And then if the count of allowed records that weren't blocked are greater than the number that were blocked display only those records. Also if that is possible maybe also compare the prior time period for the allowed messages so if yesterday we were allowing gmail to send but today we aren't show those emails.

Thank you,
Brian

[bkirk]

The Subject line doesn't give me the IP address of the mail server in a variable so I had to put a rex to extract it, and I had to add a search to the second search and the also I add conditions because I want to see servers that weren't blocked but are now.

Thank you for your help in getting me to this answer!

| multisearch [search sourcetype="postfix_syslog" NOT error_code=554 NOQUEUE | eval Type="Blocked"]
[search sourcetype="postfix_syslog" NOT NOQUEUE Subject: NOT 127.0.0.1| rex field=_raw "Subject:(?<subject_new>.*) from \S+\[(?<ip_address>[^\]]*)"| eval Type="Allowed"]
| rex field=from "@(?<fromdomain>.*)" 
| stats count(eval(Type="Blocked")) as countBlocked count(eval(Type="Allowed")) as countAllowed by fromdomain,ip_address,from
| where countBlocked>0 AND countAllowed>0
| streamstats count as counter by fromdomain,ip_address
| stats sum(countBlocked) as "Total Count-Blocked" sum(countAllowed) as "Total Count-Allowed" list(eval(if(counter<=5,from,null()))) as "Top Users" by fromdomain,ip_address 
| sort -countBlocked

[somesoni2 · Accepted solution]

Give this a shot.

|multisearch [search sourcetype="postfix_syslog" NOT error_code=554 NOQUEUE | eval Type="Blocked"]
[sourcetype="postfix_syslog" NOT NOQUEUE [IP_Address] Subject | eval Type="Allowed"]
| rex field=from "@(?<fromdomain>.*)" 
| stats count(eval(Type="Blocked")) as countBlocked count(eval(Type="Allowed")) as countAllowed
by fromdomain,ip_address,from
| sort -count
| streamstats count as counter by fromdomain,ip_address
| stats sum(countBlocked) as "Total Count-Blocked" sum(countAllowed) as "Total Count-Allowed" 
list(eval(if(counter<=5,from,null()))) as "Top Users" by fromdomain,ip_address 
| sort 25 -"Total Count"

[bkirk]

I slightly modified it but this got me to the right answer! Thank you, I will post my final search in another posting it doesn't leave me enough room in here.

[bkirk]

sourcetype="postfix_syslog" NOT NOQUEUE [IP_Address] Subject| stats count

We log the subject for accepted emails and it contains the IP so I guess a count of these would give me number of successfully sent emails. The IP would have to be dependent the results of the first search say my first search returned:
abc.com 1.1.1.1 100 me@abc.com,you@abc.com
xyz.com 2.2.2.2 50 me@xyz.com,you@abc.com

and say that 2.2.2.2 sent 10 successful emails that day but 1.1.1.1 sent 0 successful emails then I would only want to show xyz.com and filter out abc.com from my results.

[somesoni2]

Can you post the query to identify the records that were not blocked..?