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="firstname.lastname@example.org" OR email="email@example.com" OR email="firstname.lastname@example.org"
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:
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.
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.
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