I am new to splunk and try to create a search that displays me all Emails that was send 6 Weeks after a specific Date.
At the moment I am lacking the knowledge how to filter the events by comparing to timestamps without losing necessary fields for the following aggregation.
In pseudo SQL I would do something like that:
select ROUND(COUNT(*) / 6WEEKS) FROM MAIL as mail JOIN Event as event ON event.ID = mail.ID where event.createdAt + 6WEEKS <= mail.createdAt;
Basically I am interested in the number of Mails that reaches me per day 6 Weeks after an specific Meetup took place. Somebody has an idea?
Probably, the pseudo-SQL is the reason you haven't got any response yet. It's better to use plain language around here, and to tell us what your splunk records look like rather than telling us how you'd code an extract in another language.
Let's suppose your "events" are in an index called "myevents" and have a field called "CreatedAT" that contains the date of the event, a unique EventID called "ID" and a field called "EventDesc" that describes the event. So, this would make a table of the eventIDs, their event date, and description. Let's assume the event date is stored in the CreatedAT field in "MM/DD/YYYY" format.
index=myevents | eval EventDate=strptime(CreatedAT,"%m/%d/%Y") | table EventDate ID EventDesc | sort 0 EventDate | rename ID as EventID
Let's suppose your mails are in an index called "mymail", have a field SendTime that represents the date and time the email was sent to you, and a field called EventID that contains the ID of the event that the email was related to. This code would get you a count of how many emails were received in any particular day related to any particular event. Let's also assume the SendTime field is stored in a format like "yyyy-mm-dd HH:MM:SS", and that we don't care about anything but the number of emails sent on any particular date.
index=mymail | table SendTime EventID | eval MailDateEpoch=strptime(SendTime,"%Y-%m-%d %H:%M:%S") | bin MailDateEpoch as MailDate span=1d | stats count as DayCount by EventID MailDate
Now, we just need to combine these two queries appropriately. Most of the details are regarding the mail dates, so we want that on the left side of the join.
index=mymail | table SendTime EventID | eval MailDateEpoch=strptime(SendTime,"%Y-%m-%d %H:%M:%S") | bin MailDateEpoch as MailDate span=1d | stats count as DayCount by EventID MailDate | join type=left EventID [index=myevents | eval EventDate=strptime(CreatedAT,"%m/%d/%Y") | table EventDate ID EventDesc | sort 0 EventDate | rename ID as EventID]
The last two fields will be NULL if no match was found on the myevents index for the EventID. That gives us records that look about like this.
| table EventID MailDate DayCount EventDate EventDesc
We have already made sure that both date fields are in epoch format (number of seconds since a fixed point in time). Thus, we can directly compare them. With rare exceptions, there are 604800 seconds in a week. Thus, the following gives you the records you are trying to analyze, those where more than 6 weeks have elapsed.
| where EventDate + 604800*6 < MailDate | eval MailDateDisplay=strftime(MailDate,"%Y-%m-%d") | eval EventDateDisplay=strftime(EventDate,"%Y-%m-%d")
Or, if I misunderstood you and you are trying to calculate the average number of mails you have received each day for only those events that are six weeks old or more, then it would be this way...
| where EventDate + 604800*6 < relative_time(now(),"@d") | stats sum(DayCount) as TotalCount, values(EventDate) as EventDate), values(EventDesc) as EventDesc by EventID | eval AvgDayCount = 86400*TotalCount / ( relative_time(now(),"@d") - EventDate)
Note - 604800 is the number of seconds in a week, 86400 is the number in a day.