Splunk Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results forย 
Search instead forย 
Did you mean:ย 
Ask a Question

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

leonheart78
Explorer
โ€Ž08-22-2016 09:33 PM

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

TagName,ValveName,VMB,TeamName,TimeStamp,StampId,Comment,duration,Flow,TAB,ToolName
"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
Reply

sundareshr
Legend
โ€Ž08-23-2016 08:55 AM

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
Reply