Splunk Search

How to split Start Time and End Time after using the Splunk transaction command?


Currently, I'm using Splunk transaction command to derive the duration using an attribute named TimeStamp from a database.
After processing the command, I noticed that the TimeStamp attribute will now contain both the StartTime and EndTime in a single field. May I know how can I split the TimeStamp field to get the StartTime and EndTime?

Thank you

Below is the sample data

"CHEM_R02671_02","H2O2-1","102V1135-3","CHEM_U_H2O2-1","2016-08-01 23:59:48.63 2016-08-02 00:00:15.883","4007998 4008037","H2O2-1 AV- 102V1135-3 Open","27.253",0,H2O2,"CAROZ-18"
"CHEM_R02671_04","H2O2-1","102V1636-1","CHEM_U_H2O2-1","2016-08-01 23:59:48.63 2016-08-02 00:00:15.883","4008000 4008038","H2O2-1 AV- 102V1636-1 Open","27.253",,,
"CHEM_R02668_07","H2O2-1","102V1723-4","CHEM_U_H2O2-1","2016-08-01 23:59:48.63 2016-08-02 00:00:15.883","4008001 4008039","H2O2-1 AV- 102V1723-4 Open","27.253",0,H2O2,"CAROZ-13"
"CHEM_R02631_14","H2SO4-1","101V2117-3","CHEM_U_H2SO4-1","2016-08-01 23:59:48.63 2016-08-02 00:00:15.883","4008002 4008043","H2SO4-1 AV- 101V2117-3 Open","27.253",0,H2SO4,"CUSOVT-23"
"CHEM_R02443_04",ESC794,"32V1414-1","CHEM_U_ESC784","2016-08-01 23:59:48.63 2016-08-02 00:00:15.883","4008006 4008046","ESC794 AV- 32V1414-1 Open","27.253",0,ESC794,"CULKCMP-02"
"CHEM_R02443_07",ESC794,"32V1414-4","CHEM_U_ESC784","2016-08-01 23:59:48.63 2016-08-02 00:00:15.883","4008007 4008047","ESC794 AV- 32V1414-4 Open","27.253",0,ESC794,"CULKCMP-07"
"CHEM_R02441_15",ESC794,"32V1402-4","CHEM_U_ESC784","2016-08-01 23:59:48.63 2016-08-02 00:00:15.883","4008011 4008049","ESC794 AV- 32V1402-4 Open","27.253",0,ESC794,"CULKCMP-13"
0 Karma


Can you share you current search. When you use transaction command, Splunk automatically calculates the duration between the first event and the last event and puts it in a field called duration. You may also be able to compute duration without transaction command like this

your base search | stats earliest(TimeStamp) as start latest(TimeStamp) as end by ValveName | eval duration=strptime(end, "%Y-%m-%d %H:%M:%S") - strptime(start, "%Y-%m-%d %H:%M:%S") | eval duration=tostring(duration, "duration")

For the grouping (ValveName), you should use the same fields you used in your transaction command

If you must use transaction command and the TimeStamp field can only have Start & End values, you can get to them by using mvindex, like this

... | eval start=mvindex(TimeStamp, 0) | eval end=mvindex(TimeStamp, 1) | eval duration=strptime(end, "%Y-%m-%d %H:%M:%S") - strptime(start, "%Y-%m-%d %H:%M:%S") | eval duration=tostring(duration, "duration")
0 Karma