inputlookup foreach passing to search macro

[richbashaw]

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

[cmerriman]

the foreach command is more for evaluating a field/s based on existing field/s, not for joining together searches. map or join would probably be your best bet, though join is usually avoided due to processing.

http://docs.splunk.com/Documentation/Splunk/6.1.2/SearchReference/Foreach
http://docs.splunk.com/Documentation/Splunk/6.1.2/SearchReference/Map
http://docs.splunk.com/Documentation/Splunk/6.1.2/SearchReference/Join

[somesoni2 · Accepted solution]

I would give map command a try. (adjust maxsearches per number of email in your lookup

| inputlookup yourEmailLookup.csv | table address | map maxsearches=100 search="`yourmacroname(\"$address$\")`"

[richbashaw]

So I tried this, slightly modified to match the lookup header to the to the variable, but it returns no results

[inputlookup email_lookup | table recipient_address |map maxsearches=300 search="EmailStats(\"$recipient_address$\")"]

If I run the first part, it returns events, so maybe I am not passing it to the macro correctly?

[inputlookup email_lookup | table recipient_address ]

Also, I want to only run the macro once per email address. Should I dedup the inputlookup somehow? When I run the shortened search above, i get multiple results since each email sent is in the logs, and users will have multiple log entries.

Thanks

[richbashaw]

so digging deeper into the logs, I see this error:

09:21:57.017 WARN map - sid:1476969716.227287 Unable to run query 'EmailStats("rich.bashaw@mycompany.com")'.

So it looks like it is passing the correct command, but just not able to run it.

I found this answer where they say to elevate the macro permissions to app level, which we did but no luck

https://answers.splunk.com/answers/67133/bug-macros-do-not-expand-within-map.html

[somesoni2]

Try this (removed duplicates from inputlookup part as well)

|  inputlookup yourEmailLookup.csv  | stats count by address | table address | map maxsearches=100 search=" search `yourmacroname(\"$address$\")`"

[richbashaw]

You rock! That totally worked. Thanks so much.

If I may ask one final question:

This returns all the stats I need, and it looks like this:
TotalEmails Sent to Internal senttoexternal
89 49 40

and so on. Is it possible to include the $address$ variable in a field at the front of the output, something like this:
name TotalEmails SenttoInternal senttoexternal
me@mycompany.com 89 49 40

Thanks

Rich

[somesoni2]

Try this for your macro definition. (changes: included sender_address field in stats of 2nd search/first appendcols, and later, included that field in table and renamed it to general address)

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 by sender_address]| 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 sender_address,TotalEmails, SentToInternal, SentToExternal, ReceivedFromInternal, ReceivedFromExternal | rename sender_address as address

[richbashaw]

works perfectly. Thank you so much.

[richbashaw]

Hey somesoni2, I know i said one more question, but I guess I lied. I have a situation now where the search is returning multiple results if the case is different. So User1@MyDomain.com and user1@mydomain.com show up as two different rows in the output. I played around with eval $address$=lower($address$) but that throws errors. What is the best way to make sure it ignores case in the search but still returns all the results and at what point in the search string should that be?

Thanks,

Rich

[somesoni2]

Luckily your situations till now are easier to handle. Try this for your search.

 |  inputlookup yourEmailLookup.csv  | eval addess=lower(address) | stats count by address | table address | map maxsearches=100 search=" search `yourmacroname(\"$address$\")`"

[richbashaw]

thanks I will try that. Is there a way to do it within the macro? I sometime need to run the macro on an individual user, and it would be nice to have it corrected within.

[somesoni2]

In macro, the field address was used only in based search (before first pipe of each search/subsearch), so there it it case insensitive by default. But we did make a change to include sender_address in first subsearch, so here is the updated macro with case of sender_address field taken care of.

 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 | eval sender_address=lower(sender_address)|stats dc(message_id) as SentToInternal by sender_address]| 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 sender_address,TotalEmails, SentToInternal, SentToExternal, ReceivedFromInternal, ReceivedFromExternal | rename sender_address as address

[richbashaw]

Perfect. Thanks again.