How to edit my search to view all occurrences of an event grouped by a field?

[emamedov]

Every time a user logs in, there is a log statement generated. I am currently running a search for a specific set of users:

(index=* OR index=_*) Message:LoginActivity:loggedIn email="email1@gmail.com" OR email="email2@gmail.com" OR email="email3@gmail.com"

How would I get a table that shows all of the e-mails, how many times they logged in, and the timestamps of each login? I would envision the table looking something like:

[gcusello]

Insert the list of email to monitor in a lookup and try something like this

| inputlookup email_lookup.csv | eval count=0 | append [ search yoursearch | stats values(_time) AS _time count by email ] | stats values(_time) AS _time sum(count) AS Total by email

In this way you have for each email the sum of events and the list of timestamps, and 0 for emails without events.
Bye.
Giuseppe

[sundareshr · Accepted solution]

Try this

base search | stats count as "Number of Logins" values(_time) as Timestamps by Email

[emamedov]

That works to get the e-mails that are in the query. There are multiple OR statements (I am trying to identify if a specific set of users have logged in). Is there any way to include the emails in the conditional statement, and if there are no events, present a 0. The purpose of this would be to see who hasn't logged in.

[sundareshr]

Where do you get the specific set of users? Is that in a .csv file somewhere? If it is, you can create a lookup file of all emails and try something like this (lookup file must have a field called email and data should be identical with index data)

| inputlookup lookupfilewithemailsids.csv | where NOT [ search (index= OR index=_) Message:LoginActivity:loggedIn email=* | table email ]  | table email | eval count=0