How to get count totals in the same query with |stats list (subject) as subj list(recipient) as recp..... ????

[packet_hunter]

Scenario: search email logs for all the recipients of a an email with a specific subject and get a total of number of the recipients

sample code:
index=mail sourcetype=xemail
[search index=mail sourcetype=xemail subject = "Blah" |stats count by internal_message_id | fields internal_message_id]
|stats list(subject) as subj list(sender) as sender list(recipient) as recp list(vendor_action) as status by internal_message_id

this will produce totals in the statistics tab, however if there are multiple recp addresses in each email, then the count is off.

Please provide an example.

Thank you so much

[packet_hunter]

@everyone
Thank you all for your answers.
Raschko's code

| eval recp_count=mvcount(recp)

creates a total of recps per line, which is better than what I had before, but when I get 1000+ recipients then I need to total the recp_count to get an aggregate of the entire email storm/campaign...

for example using the following :
index=mail sourcetype=xemail [search index=mail sourcetype=xemail subject = "Blah" |stats count by UID| fields
UID] |stats list(subject) as subj list(sender) as sender list(recipient) as recp by UID | eval recp_count=mvcount(recp)

this produces:

subject sender recp recp_count
blah x@d.com a@my.com 2
b@my.com

hope this makes sense....

[Raschko]

As javiergn suggested addcoltotals should give you a new column with the total sum.

| addcoltotals recp_count

[packet_hunter]

my sample data table did not display correctly

[packet_hunter]

how can I add a snag-it image?

[Raschko · Accepted solution]

I guess in your subsearch

[search index=mail sourcetype=xemail subject = "Blah" |stats count by internal_message_id | fields internal_message_id]

you only count the amount of loglines per internal_message_id. If there are more email adresses in one line your count is off.

As you already have the list of recipients after the stats command, can't you just count this?

Like:`

index=mail sourcetype=xemail
|stats list(subject) as subj list(sender) as sender list(recipient) as recp list(vendor_action) as status by internal_message_id
| eval recp_count=mvcount(recp)

[packet_hunter]

This is a good answer, however I need to total the recp_count on each line. Do you know how to aggregate the recp_counts??

Thank you

[packet_hunter]

my final code that works for me is:

index=mail sourcetype=xemail
[search index=mail sourcetype=xemail subject = "Blah" |stats count by UID| fields UID]
|stats list(subject) as subj list(sender) as sender list(recipient) as recp list(vendor_action) as status by UID
|eval recp_count=mvcount(recp)
|eventstats sum(recp_count) as total_recipients

I want to thank everyone for their help, I know my request was confusing.

[somesoni2]

So, you're getting a recp_counts in each line and you want another line, kind of summary which tell you total of all recp_counts (from each line)?

[packet_hunter]

If I could give you a snag-it shot I think you would understand but idk how to paste it in.

index=mail sourcetype=xemail

[search index=mail sourcetype=xemail subject = "Blah" |stats count by UID| fields UID]

|stats list(subject) as subj list(sender) as sender list(recipient) as recp list(vendor_action) as status by UID 

    |eval recp_count=mvcount(recp)     **this part totals all recipients in each individual email**

        |eventstats sum(recp_count) as total_recipients   **this part totals all recipients for every individual email  and  keeps a running total**

does that make sense?

[packet_hunter]

is there a better way to do this?

[somesoni2]

The method you're using now should work just fine. May be a little variation, but I don't think there will be any major improvements.

index=mail sourcetype=xemail [search index=mail sourcetype=xemail subject = "Blah" |stats count by UID| fields UID] |stats list(subject) as subj list(sender) as sender list(recipient) as recp list(vendor_action) as status count(recipient) as recp_count by UID  |eventstats sum(recp_count) as total_recipients  

[somesoni2]

Can you provide some sample output of this search, specifically when there are multiple recipients in one email. What you would need is to split them based on the delimiter used (should be a semicolon) and run teh stats again.

[packet_hunter]

| stats distinct_count(eval(recp)) as total_recp works but I want to display it all together

index=mail sourcetype=xemail
[search index=mail sourcetype=xemail subject = "Blah" |stats count by internal_message_id | fields internal_message_id]
|stats list(subject) as subj list(sender) as sender list(recipient) as recp list(vendor_action) as status by internal_message_id

like a table
subj sender recp total_recp

[somesoni2]

You can do this to get each recipient in separate row and it'll match the count. It'll still be tabular format but with some repeating values for intern_message_id.

index=mail sourcetype=xemail
[search index=mail sourcetype=xemail subject = "Blah" |stats count by internal_message_id | fields internal_message_id]
|stats list(subject) as subj list(sender) as sender list(vendor_action) as status by internal_message_id,recipient

[javiergn]

Hi,

I didn't fully understand your requirement so apologies if this is not relevant to you:

count

stats count list(subject) as subj list(sender) as sender list(recipient) as recp list(vendor_action) as status by internal_message_id

addcoltotals

stats count list(subject) as subj list(sender) as sender list(recipient) as recp list(vendor_action) as status by internal_message_id
| addcoltotals

If you post an example of your data we might be able to help a bit more.

Thanks,
J