Dashboards & Visualizations

How to calculate the time difference between two xml tags in the same event?

Explorer

Hi,

I am facing an issue in calculating time difference with two timestamp fields in the same XML event. The difference field is always coming as spaces if I use the below search. Please advise if there is any change required in conf file to calculate the timestamp difference

Search:

sourcetype="SOURCEA" "History.rowdata.StageName"="stage A"  | spath output=StartTime path=ProcessHistory.rowdata.ProcessStartTime |spath output=EndTime path=History.rowdata.ProcessCompleteTime | eval CapturedStartTime = strptime(StartTime,"%Y%m%dT%H%M%S.%3N GMT") |eval CapturedEndTime = strptime(EndTime,"%Y%m%dT%H%M%S.%3N GMT")  |eval  Duration = CapturedEndTime - CapturedStartTime  | table ActivityCd StartTime EndTime CapturedStartTime CapturedEndTime Duration



<History> <rowdata>
<status>Completed</Status>
<StageName>stage A</StageName>
<ProcessStartTime>20160126T193403.622GMT</ProcessStartTime>
<ProcessCompleteTime>20160126T193403.632 GMT</ProcessCompleteTime> </rowdata>
<rowdata> <Status>Completed</Status>
<StageName>stage A</StageName>
<ProcessStartTime>20160126T193359.815 GMT</ProcessStartTime>
<ProcessCompleteTime>20160126T193359.915 GMT</ProcessCompleteTime> </rowdata>
</History>
0 Karma
1 Solution

SplunkTrust
SplunkTrust

I think I got the issue. Based on your input xml, the field that you extract using spath, StartTime and EndTime is coming as multivalued fields and thus causing your Duration calculation to fail. Here is what you can try:

 sourcetype="SOURCEA" "History.rowdata.StageName"="stage A"  | spath output=StartTime path=History.rowdata.ProcessStartTime |spath output=EndTime path=History.rowdata.ProcessCompleteTime | table ActivityCd StartTime EndTime | eval temp=mvzip(StartTime,EndTime,"#") | mvexpand temp | eval StartTime=mvindex(split(temp,"#"),0)  | eval EndTime=mvindex(split(temp,"#"),1)  | eval CapturedStartTime = strptime(StartTime,"%Y%m%dT%H%M%S.%3N GMT") |eval CapturedEndTime = strptime(EndTime,"%Y%m%dT%H%M%S.%3N GMT")  |eval  Duration = CapturedEndTime - CapturedStartTime  | table ActivityCd StartTime EndTime CapturedStartTime CapturedEndTime Duration

View solution in original post

SplunkTrust
SplunkTrust

I think I got the issue. Based on your input xml, the field that you extract using spath, StartTime and EndTime is coming as multivalued fields and thus causing your Duration calculation to fail. Here is what you can try:

 sourcetype="SOURCEA" "History.rowdata.StageName"="stage A"  | spath output=StartTime path=History.rowdata.ProcessStartTime |spath output=EndTime path=History.rowdata.ProcessCompleteTime | table ActivityCd StartTime EndTime | eval temp=mvzip(StartTime,EndTime,"#") | mvexpand temp | eval StartTime=mvindex(split(temp,"#"),0)  | eval EndTime=mvindex(split(temp,"#"),1)  | eval CapturedStartTime = strptime(StartTime,"%Y%m%dT%H%M%S.%3N GMT") |eval CapturedEndTime = strptime(EndTime,"%Y%m%dT%H%M%S.%3N GMT")  |eval  Duration = CapturedEndTime - CapturedStartTime  | table ActivityCd StartTime EndTime CapturedStartTime CapturedEndTime Duration

View solution in original post

Explorer

Thank you. It worked .

0 Karma

Community Manager
Community Manager

Hi @seetharamanss

Please don't forget to resolve this post by clicking "Accept" directly below @somesoni2's answer.

0 Karma

SplunkTrust
SplunkTrust

Great... If there are no follow-up questions, You can close the question by accepting this answer.

0 Karma

Communicator

Are you able to get correct values for other fields? StartTime EndTime CapturedStartTime and CapturedEndTime.

0 Karma

Explorer

Hi
I'm able to capture the value properly using spath. But for the eval with strptime I'm getting values starting with 14*.

0 Karma

Communicator

that is epochtime.
I have checked the query and there is no issue with that and I am able to see the Duration field with value 0.010000 when I ran the same.

Can you post your output?

0 Karma

Explorer

Hi
Below is my output. If you see the duration field. It is always coming as spaces. Please advise if I need to make any changes to configuration to capture the duration.

ActivityCd                    StartTime                     EndTime                        CapturedStartTime    CapturedEndTime        Duration
Stage A                       20160126T193403.622 GMT       20160126T193403.632 GMT        1453862043.622000    1453862043.632000 
Stage A                       20160126T193359.815 GMT       20160126T193359.915 GMT        1453862039.815000    1453862039.915000 
0 Karma