Scenario background : I am searching email logs for all senders and recipients of specific subject. Each email is a chain of event logs that share a Unique ID (UID) for that specific email occurrence.
Here is my current code:
index=mail sourcetype=xemail
[search index=mail sourcetype=xemail subject = "Blah" |stats count by UID| fields UID]
|stats list(subject) as subj list(sender) as sender list(recipient) as recp values(time_hour) as Hour values(time_minute) as Minute values(time_second) as Second by UID
current result headers are:
UID Subj sender recp Hour Minute Second
I would like to combine the Hour Minute Second values into a new field called Time. One caveat is that there are multiple time_second values as the events are separate and correlated by UID. So ideally I would like the Time field to contain complete time information (HH:MM:SS) in each row for each email occurrence. In other words each email has multiple event times because of the multiple event logs, the email events chain.
I know there are a number of ways to do this but I am looking for the best way given my previous code.
Thank you
Try something like this
index=mail sourcetype=xemail
[search index=mail sourcetype=xemail subject = "Blah" |stats count by UID| fields UID]
|stats list(subject) as subj list(sender) as sender list(recipient) as recp values(time_hour) as Hour values(time_minute) as Minute values(time_second) as Second by UID | eval Time=mvzip(mvzip(Hour,Minute,":"),Second,":") | fields - Hour Minute Second
Approach2 (should handle multiple seconds as well)
index=mail sourcetype=xemail
[search index=mail sourcetype=xemail subject = "Blah" |stats count by UID| fields UID] | eval Time=time_hour.":".time_minute.":".time_second |stats list(subject) as subj list(sender) as sender list(recipient) as recp values(Time) as Time by UID
Try something like this
index=mail sourcetype=xemail
[search index=mail sourcetype=xemail subject = "Blah" |stats count by UID| fields UID]
|stats list(subject) as subj list(sender) as sender list(recipient) as recp values(time_hour) as Hour values(time_minute) as Minute values(time_second) as Second by UID | eval Time=mvzip(mvzip(Hour,Minute,":"),Second,":") | fields - Hour Minute Second
Approach2 (should handle multiple seconds as well)
index=mail sourcetype=xemail
[search index=mail sourcetype=xemail subject = "Blah" |stats count by UID| fields UID] | eval Time=time_hour.":".time_minute.":".time_second |stats list(subject) as subj list(sender) as sender list(recipient) as recp values(Time) as Time by UID
Bravo, It works great!!!
However, I lose the additional time values (because there are multiple second values per email occurrence) but I can live with that.
Try the 2nd query.
even better! did you write an SPL book?