Deployment Architecture

inputlookup foreach passing to search macro

richbashaw
New Member

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

Tags (1)
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

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$\")`"

View solution in original post

cmerriman
Super Champion

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
New Member

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

0 Karma

richbashaw
New Member

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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$\")`"
0 Karma

richbashaw
New Member

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

richbashaw
New Member

works perfectly. Thank you so much.

0 Karma

richbashaw
New Member

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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$\")`"
0 Karma

richbashaw
New Member

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.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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
0 Karma

richbashaw
New Member

Perfect. Thanks again.

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...