Dashboards & Visualizations

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

seetharamanss
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

somesoni2
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

somesoni2
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

seetharamanss
Explorer

Thank you. It worked .

0 Karma

ppablo
Retired

Hi @seetharamanss

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

Murali2888
Communicator

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

0 Karma

seetharamanss
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

Murali2888
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

seetharamanss
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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...