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>
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
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
Thank you. It worked .
Hi @seetharamanss
Please don't forget to resolve this post by clicking "Accept" directly below @somesoni2's answer.
Great... If there are no follow-up questions, You can close the question by accepting this answer.
Are you able to get correct values for other fields? StartTime EndTime CapturedStartTime and CapturedEndTime.
Hi
I'm able to capture the value properly using spath. But for the eval with strptime I'm getting values starting with 14*.
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?
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