Thx a lot for the various searches as they (and the breakdown of them) greatly help. We're pulling O365 Exchange message trace logs which don't contain the recipient count field. However, we do have the MessageId field. I'm using the following search for total recipient count: index=exchange NOT Status=Quarantined NOT Status=Failed NOT [|inputlookup Senderwhitelist.csv] SenderAddress=*.xyz.com | dedup MessageId sortby _time | stats count(eval(MessageId)) as recipient_count However, when I expand the search to the following search below, no events are returned. index=exchange NOT Status=Quarantined NOT Status=Failed NOT [|inputlookup Senderwhitelist.csv] SenderAddress=*.xyz.com | dedup MessageId sortby _time | stats count(eval(MessageId)) as recipient_count | table _time SenderAddress RecipientAddress Subject recipient_count Size | timechart sum(recipient_count) as daily_total span=1d | eventstats median(daily_total) AS median, p25(daily_total) as p25, p75(daily_total) as p75, mean(daily_total) as mean | eval iqr = p75 - p25 | eval xplier = 2 | eval low_lim = median - (iqr * xplier) | eval high_lim = median + (iqr * xplier) | eval anomaly = if(daily_total<low_lim OR daily_total > high_lim, daily_total,0) | table _time daily_total anomaly ... View more

Was wondering if you could take a look at a new post I have concerning email analytics - posted under, "Need help finding average email sent per user on a daily basis" Thx ... View more

if I wanted to put this search through the ML toolkit, should I feed it a less specific search like below to see what ML will tag as a potential anomaly? index=exchange NOT Status=Quarantined NOT Status=Failed SenderAddress=*.xyz.com | stats count by SenderAddress Subject | search count > 1 Thx ... View more

Got it / re-running search Thx ... View more

Also, I changed: | threshold = max(AvgOfCount + 4 * StdevOfCount,10) to: | eval threshold = max(AvgOfCount + 4 * StdevOfCount,10) ... View more

Thx - still getting, "Error in 'eval' command: The expression is malformed. Expected )." Combing through the eval commands to see where a ) is missing... ... View more

Sorry about that / index=exchange I tagged on the following: | eval threshold=coalesce(threshold,10) | eval SubjectLen=length(Subject) | eval SubjectCopy=Subject | rex field=SubjectCopy mode=sed "s/[!@#$%^&*()_+]//g" | eval specialCharCount = SubjectLen - length(SubjectCopy) | eval KeepMe = case(count >= threshold, 1, specialCharCount>4, 1 true(),0) | search KeepMe=1 but I get an error: "Error in 'eval' command: The expression is malformed. Expected )." ... View more

Ran the search and got 16 events (lowered time frame to 30 days for quicker search, but will expand time frame in other searches I plan to run). Here's info for the first few users returned: SenderAddress Subject Count KeepMe threshold [email protected] Blah Blah Blah 493 1 398 [email protected] Blah Blah Blah 267 1 194 [email protected] Blah Blah Blah 218 1 155 In my original search I helped cut down on users by using an inputlookup command which has a whitelist of users (bounce lists, smtp mailers, and so on). Is it possible to add a second list for filtering? I have a list that has keywords a subject might contain to indicate spam. Also, I have a separate search that looks for the count in special characters in the subject as follows: index=exchange | eval SubjectLen=length(Subject) | eval SubjectCopy=Subject | rex field=SubjectCopy mode=sed "s/[!@#$%^&*()_+]//g" | eval specialCharCount = SubjectLen - length(SubjectCopy) | fields - SubjectCopy | stats count by specialCharCount | sort -count Would it be possible to inject this search as well, with a weight on subjects that have a higher count of special characters? Thx again ... View more

Got it - thx / running search now - will let you know results shortly ... View more

Thx When I run the search I get an error message: 'Error in 'search' command: Unable to parse the search: Invalid time bounds in search: start=1485283860 > end=1485280800.' ... View more

Posted question as, "How to create a search for moving average for email sent per user" Thx ... View more

Sounds good - thx ... View more

Thx for the new search. I ran that and I'm down to 102 events, which is a great reduction. However, let me throw another wrench into this if you don't mind 🙂 Right now when I pick a time from the time picker, I'm limiting the average to that exact time, but what I wanted to get a rolling average. For example, say I have a user who if I ran a search with eventstats avg(emailsent) for all-time returns an average of say 3.2. Running the same search but for the last 24 hours, the average is now 6.82. if possible, I'd like to look at the average for the user for a larger time period (say 3 or 6 months) to get a more accurate look at their average, which would help smooth out some spikes. Would that be possible ... View more

Thx for the search. When I run this against 'Last 24 hours' I get 14,477 events, whereas running the search below returns six: | stats count as RecipientAddress by SenderAddress Subject | eval emailsent=(RecipientAddress) | eventstats avg(emailsent) as avg stdev(emailsent) as stdev by SenderAddress | stats max(emailsent) as maxsent, values(avg) as avgpersender, values(stdev) as stdevpersender by SenderAddress,Subject | eval threshold = 4 * ( stdevpersender + avgpersender ) | where maxsent > threshold | sort -threshold ... View more

I'm going to keep this open for a few more days to see if anyone else can offer perhaps a more efficient way or perhaps provide feedback on the search I provided. Thx ... View more

After mucking around a bit, I believe I came up with a decent search. Search is as follows: | stats count as RecipientAddress by SenderAddress Subject | eval emailsent=(RecipientAddress) | eventstats avg(emailsent) as avg stdev(emailsent) as stdev by SenderAddress | stats max(emailsent) as maxsent, values(avg) as avgpersender, values(stdev) as stdevpersender by SenderAddress | eval threshold = 4 * ( stdevpersender + avgpersender ) | where maxsent > threshold Basically, I'm trying to calculate the baseline of each sender and then isolate the outliers who are sending greater than four times their standard deviation to show me potential indicators of compromise. ... View more

That's my bad on the misspelling! Ran the following, but still no events were returned: | eval TheMinute = relative_time(_time,"@m") | eval TheDay = relative_time(_time,"@d") | stats count as RecipientAddress by SenderAddress Subject TheMinute | eventstats count as SenderCount by SenderAddress TheDay | where RecipientCount > 1 or SenderCount > 100 Please note I had to change '| stats count (ReceipientAddress) as RecipientCount by SenderAddress Subject TheMinute' to '| stats count as RecipientAddress by SenderAddress Subject TheMinute' as I get the same error message as before, '"Error in 'stats' command: The argument '(RecipientAddress)' is invalid." (with correct spelling of RecipientAddress) ... View more

I get: FromIP messageId Received ReceipientAddress SenderAddress Status Subject ToIP ... View more

it's 'Subject' Thx ... View more

Thx Slightly modified the query as follows: | eval TheMinute = relative_time(_time,"@m") | eval TheDay = relative_time(_time,"@d") | stats count as RecipientAddress by SenderAddress Subject TheMinute | eventstats count as SenderCount by SenderAddress TheDay | where RecipientAddress > 1 or SenderCount > 100 and I'm getting events returned. Splunk gave me an error when the query had, "| stats count (RecipientAddress) as RecipientCount by SenderAddress Subject TheMinute", "Error in 'stats' command: The argument '(RecipientAddress)' is invalid." I'd still like to pull some query variables from another search I have that will help me find the avg and stdev of emails sent per Sender to recipients and then look for stdev that are 4x the threshold. From my other query I have: | eventstats avg(RecipientAddress) as avg stdev(RecipientAddress) as stdev by SenderAddress | stats max(RecipientAddress) as maxsent, values(avg) as avgpersender, values(stdev) as stdevpersender by SenderAddress, RecipientAddress | eval threshold = 4 * ( RecipientAddress + avgpersender ) | where maxsent > threshold ... View more

Thx for the reply. I tried the | eval theMinute = relative_time(_time,"@m") | stats count as RecipientAddress by SenderAddress Subject theMinute | where count > 1 but I'm not seeing any results. Even modified the "@m" to various number, @5m, @1h, etc, but no events are returned. Testing the query, I ran just the search and | stats count as RecipientAddress by SenderAddress Subject - and I see SenderAddresses with a count > 1 with the same Subject ... View more

Thx for the reply. Running search... | stats dc(ReceipientAddress) as count by SenderAddress,Subject | fields SenderAddress Subject count - brings back a count of 0 for each event ... View more