How can I monitor the usage of hundreds of specific email addresses?

[earriaga]

I want to upload hundreds of email addresses in some format, so as to track the activity of each of those email addresses in one of our Sources. How can I do this?

[earriaga]

Yes, it shows me all the emails!!
Thank you very much for all your help!

[elliotproebstel · Accepted solution]

Here's one way to do it: Create a lookup table containing the items you want to monitor, reference it with an inputlookup and use the results of that inputlookup in the base search.

Let's say your lookup will be called montored_emails. So upload a CSV containing a single column, with the header email_address. In each row will be one email address, nothing more. Create a lookup from that CSV file. Then your search will look something like this:

index="mobile_app_tracking" event=song 
[ | inputlookup monitored_emails 
| stats values(email_address) AS userEmail 
| format ] 
| chart count(data.url) as SongsPlayed over userEmail 
| sort -SongsPlayed

[earriaga]

Thank you! I uploaded csv called in Splunk "F17-created.csv" to the search app with all apps permissions, all users.
File only has one column, first row called email_address.

I did not create a Lookup definition.

Here is the query:

index="mobile_app_tracking" event=song
[|inputlookup F17-created |stats values (email_address) A userEmail ]
|chart count (data.url) as SongsPlayed over userEmail
|sort -SongsPlayed

I am not familiar with the square brackets. I get this error with the brackets:

Error in 'inputlookup' command: This command must be the first command of a search.

And without brackets I get the same error:

Error in 'inputlookup' command: This command must be the first command of a search.

Suggestions?

[elliotproebstel]

I have a few thoughts.

First, you say you uploaded the CSV file into the search app. Did you also create a lookup based on that file? (If you haven't, you'll need to go to Settings > Lookups > Lookup Definitions and create a new lookup named F17-created and point it at the file you uploaded.)

If you have created the lookup, try this alone in the search bar: |inputlookup F17-created |stats values(email_address) AS userEmail | format <- Note that there are a few differences here between what you have pasted above and what I've written: Your code above has a space between the word values and the open paren following it, and there needs to be no space; also, your code is missing the letter s in the word AS; also, you ommitted the format command. But I actually don't think any of those could be the root issue, because they would be giving you a very different error - so did you copy/paste the exact code you're running?

As a little background - the square brackets are used to separate a subsearch. Here's a good overview of subsearches:
https://docs.splunk.com/Documentation/Splunk/7.0.0/SearchTutorial/Useasubsearch

So the way I'm looking to use the subsearch here is to generate a list of all email addresses from the lookup file, and use those as search terms in the outer search. If everything is structured appropriately, you should not get that error about inputlookup needing to be the first command in a search, because it is the first command inside the subsearch. Just for a little extra background, here's good info about inputlookup:
http://docs.splunk.com/Documentation/Splunk/7.0.0/SearchReference/Inputlookup

[earriaga]

Thank you again!

I had not created a Lookup Definition. I just did that, and corrected the syntax.

I got a result, only for one email out of 300.
I will have to do it all again with a file of users that I know use the system just to verify that the result is correct.

Thank you very much for your help!

[elliotproebstel]

If you are only seeing results related to one email, double-check that the subsearch is working appropriately by doing this:

|inputlookup F17-created |stats values(email_address) AS userEmail

Does that list all the email addresses you expect?

[earriaga]

Yes, it shows me all the emails!!
Thank you very much for all your help!

[elliotproebstel]

Glad to help! Want to mark the answer as accepted, so that others can find it if they need the same help?

[earriaga]

Just so you know, I did the whole thing with a Control file (pulled from my original query) And it worked perfectly.

One question if I may, when i run the query for F17-created, if the time is the last 24 hours, I get one. If the time is All time, I get none.
I do not understand that.

Again, thank you for your help.

[elliotproebstel]

Hmm...my guess on this is that your search is timing out, perhaps? But I'm not sure. That's quite strange. If the answer is generally working for you, I'd recommend accepting the answer to this and posting the 24-hour vs all-time scenario as a new question - because someone else might be able to help you, but they likely won't read this deep into the comments here to find the new question!

[rphillips_splk]

Hi @earriaga - It would help if you upload a sample event that you intend to ingest into Splunk and clarify what you mean by track the activity. Are you just looking for event count by email address or something else?

[earriaga]

Sure! for example this querie

index="mobile_app_tracking" event=song |chart count(data.url) as SongsPlayed over userEmail |sort -SongsPlayed

Tells me how many songs were played by each userEmail.

Now, I want to do this but for a predefined set of emails.

Imagine an excel with 300 email addresses, I want to know how many songs these specific email address (in my excel) played? or even if the played at all.

Is that possible?