topic Re: What is the best way to concatenate multiple separate field values into a combined new field? in Splunk Search

What is the best way to concatenate multiple separate field values into a combined new field?

packet_hunter — Tue, 29 Sep 2020 08:37:16 GMT

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

Re: What is the best way to concatenate multiple separate field values into a combined new field?

somesoni2 — Fri, 29 Jan 2016 18:00:18 GMT

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

Re: What is the best way to concatenate multiple separate field values into a combined new field?

packet_hunter — Fri, 29 Jan 2016 18:15:58 GMT

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.

Re: What is the best way to concatenate multiple separate field values into a combined new field?

somesoni2 — Fri, 29 Jan 2016 18:23:31 GMT

Try the 2nd query.

Re: What is the best way to concatenate multiple separate field values into a combined new field?

packet_hunter — Fri, 29 Jan 2016 20:25:02 GMT

even better! did you write an SPL book?