Splunk Search

How do I add event time fields from email logs to my search output?

Contributor

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.

Tags (4)
0 Karma
1 Solution

Revered Legend

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 

View solution in original post

0 Karma

Revered Legend

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 

View solution in original post

0 Karma
Don’t Miss Global Splunk
User Groups Week!

Free LIVE events worldwide 2/8-2/12
Connect, learn, and collect rad prizes
and swag!