Scenario: I am looking for all recipients and senders of a specific email subject using the following search.
(UID is the unique id of each email event chain, for example one event will have subject, the next will have recipient, etc. but all share the same UID)
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 by UID
Now I need to add the date and time fields to the output.
For example, currently I have as output:
UID SUBJ SENDER RECIPIENT
But I would like the output to show:
UID Date Time SUBJ SENDER RECIPIENT
The time fields are Time_Hour Time_Minute Time_Second
The date fields are Date_Month Date_Day Date_Year
I know there are a number of ways to do this, but I would like to keep these fields and not extract new fields.
One caveat is that each event will have the date and time fields so I need to dedup the date and time per UID.
Please provide an example, thank you so much.
How about this
index=mail sourcetype=xemail
[search index=mail sourcetype=xemail subject = "Blah" |stats count by UID| fields UID]
|stats values(subject) as subj values(sender) as sender values(recipient) as recp values(Date_*) as Date_* values(Time_*) as Time_* by UID
If my understanding is correct, there should be just one unique value of Date_* and Time_* per UID. so values function should give you just one value. You can concatenate the fields later to generate timestamp in expected format.
Updated
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(Date_Month) as Date_Month values(Date_Day) as Date_Day values(Date_Year) as Date_Year values(Time_Hour) as Time_Hour values(Time_Minute) as Time_Minute values(Time_Second) as Time_Second by UID
How about this
index=mail sourcetype=xemail
[search index=mail sourcetype=xemail subject = "Blah" |stats count by UID| fields UID]
|stats values(subject) as subj values(sender) as sender values(recipient) as recp values(Date_*) as Date_* values(Time_*) as Time_* by UID
If my understanding is correct, there should be just one unique value of Date_* and Time_* per UID. so values function should give you just one value. You can concatenate the fields later to generate timestamp in expected format.
Updated
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(Date_Month) as Date_Month values(Date_Day) as Date_Day values(Date_Year) as Date_Year values(Time_Hour) as Time_Hour values(Time_Minute) as Time_Minute values(Time_Second) as Time_Second by UID