Hey All. Splunk n00b here, but making some progress 🙂
I am trying to generate an email statistics report for one of our departments. I have a list of email addresses that I have imported as a lookup.
I created a search macro that accepts the email address and returns the stats I need. This work fine when used in the search bar like EmailStats("john.doe@mycompany.com")
Macro:
index=main sourcetype=msgtrk $address$ (event_id=DELIVER OR event_id=SEND)|dedup message_id |stats dc(message_id) as TotalEmails | appendcols [search (sender_address=$address$ AND recipient_address=*mycompany.com AND event_id=DELIVER) | dedup message_id |stats dc(message_id) as SentToInternal]| appendcols [search (sender_address=$address$ AND recipient_address!=*mycompany.com AND event_id=SEND) | dedup message_id |stats dc(message_id) as SentToExternal]| appendcols [search (sender_address=*mycompany.com AND event_id=DELIVER AND recipient_address=$address$) | dedup message_id |stats dc(message_id) as ReceivedFromInternal]| appendcols [search (sender_address!=*mycompany.com AND event_id=DELIVER AND recipient_address=$address$) | dedup message_id |stats dc(message_id) as ReceivedFromExternal]|table $address$,TotalEmails, SentToInternal, SentToExternal, ReceivedFromInternal, ReceivedFromExternal
What I would like to do is pass each email address in my lookup table, and then run the macro for each one and end up with a table of each users email stats. Is this possible?
The foreach function looks promising, but I have no idea how to use it.
My lookup only contains the email address, with a header of recipient_address, which is one of the fields in the message tracking logs.
Like so:
recipient_address
john.doe@mycompany.com
jane.doe@mycompany.com
...
Thanks,
Rich
... View more