How to extract first and last timestamp value from below data and subtract them to get time difference in seconds?

[anna]

 Transaction_Log__c: {"message":"Entering doPost method","level":"INFO","loggerName":"StoreManagementAPI","timestamp":"2022-08-03T11:45:25.880Z"} {"message":"Source System is : null","level":"DEBUG","loggerName":"StoreManagementAPI","timestamp":"2022-08-03T11:45:25.886Z"} {"message":"Request Body : {\"data\":{\"dealerCode\":\"FARC\",\"dealerType\":\"Premise\"}}","level":"DEBUG","loggerName":"StoreManagementAPI","timestamp":"2022-08-03T11:45:25.888Z"} {"message":"Request Type/Parameters are : {}","level":"DEBUG","loggerName":"StoreManagementAPI","timestamp":"2022-08-03T11:45:25.889Z"} {"message":"Deserializing the reqBody","level":"INFO","loggerName":"StoreManagementAPI","timestamp":"2022-08-03T11:45:25.890Z"} {"message":"Entering getSuccessResponse method and parameter are -->TLS_Store__c:{Id=a7O5L000000000zUAA, Name=TELSTRA SHOP BONDI JUNCTION, TLS_DeliveryAddress__c=a2r5L000000YybMQAS, TLS_CompanyName__c=TRS SHOPS, TLS_PremiseCode__c=FARC, TLS_DealerChannel__c=TSN, TLS_DealerStatus__c=Active, TLS_HROrgUnitCode__c=90000561, TLS_DealerABN__c=33051775556, TLS_DealerACN__c=51775556, [email protected], TLS_DealerPhone__c=1800 723 917, TLS_DealerType__c=Premise, TLS_DealerParent__c=a7O5L000000075BUAQ, TLS_PhysicalAddress__c=a2r5L000000YybMQAS}","level":"INFO","loggerName":"StoreManagementAPI_ResponseMessages","timestamp":"2022-08-03T11:45:25.928Z"} {"message":"Exiting getSuccessResponse method","level":"INFO","loggerName":"StoreManagementAPI_ResponseMessages","timestamp":"2022-08-03T11:45:25.930Z"} {"message":"Exiting Post method","level":"INFO","loggerName":"StoreManagementAPI","timestamp":"2022-08-03T11:45:25.931Z"}

[richgalloway · Accepted solution]

Use the rex command to extract the timestamps.  The max_match=0 option tells rex to extract all matches.  The matches will be put into a multi-value field so we'll use mvindex(0) and mvindex(-1) to get the first and last, respectively.  Then we have to convert the timestamps into epoch (integer) format before the difference can be calculated.  Try it out in this run-anywhere example:

| makeresults | eval _raw= "Transaction_Log__c: {\"message\":\"Entering doPost method\",\"level\":\"INFO\",\"loggerName\":\"StoreManagementAPI\",\"timestamp\":\"2022-08-03T11:45:25.880Z\"} {\"message\":\"Source System is : null\",\"level\":\"DEBUG\",\"loggerName\":\"StoreManagementAPI\",\"timestamp\":\"2022-08-03T11:45:25.886Z\"} {\"message\":\"Request Body : {\"data\":{\"dealerCode\":\"FARC\",\"dealerType\":\"Premise\"}}\",\"level\":\"DEBUG\",\"loggerName\":\"StoreManagementAPI\",\"timestamp\":\"2022-08-03T11:45:25.888Z\"} {\"message\":\"Request Type/Parameters are : {}\",\"level\":\"DEBUG\",\"loggerName\":\"StoreManagementAPI\",\"timestamp\":\"2022-08-03T11:45:25.889Z\"} {\"message\":\"Deserializing the reqBody\",\"level\":\"INFO\",\"loggerName\":\"StoreManagementAPI\",\"timestamp\":\"2022-08-03T11:45:25.890Z\"} {\"message\":\"Entering getSuccessResponse method and parameter are -->TLS_Store__c:{Id=a7O5L000000000zUAA, Name=TELSTRA SHOP BONDI JUNCTION, TLS_DeliveryAddress__c=a2r5L000000YybMQAS, TLS_CompanyName__c=TRS SHOPS, TLS_PremiseCode__c=FARC, TLS_DealerChannel__c=TSN, TLS_DealerStatus__c=Active, TLS_HROrgUnitCode__c=90000561, TLS_DealerABN__c=33051775556, TLS_DealerACN__c=51775556, [email protected], TLS_DealerPhone__c=1800 723 917, TLS_DealerType__c=Premise, TLS_DealerParent__c=a7O5L000000075BUAQ, TLS_PhysicalAddress__c=a2r5L000000YybMQAS}\",\"level\":\"INFO\",\"loggerName\":\"StoreManagementAPI_ResponseMessages\",\"timestamp\":\"2022-08-03T11:45:25.928Z\"} {\"message\":\"Exiting getSuccessResponse method\",\"level\":\"INFO\",\"loggerName\":\"StoreManagementAPI_ResponseMessages\",\"timestamp\":\"2022-08-03T11:45:25.930Z\"} {\"message\":\"Exiting Post method\",\"level\":\"INFO\",\"loggerName\":\"StoreManagementAPI\",\"timestamp\":\"2022-08-03T11:45:25.931Z\"}"
```The above just creates test data ```
| rex max_match=0 "timestamp[[:punct:]]+(?<timestamp>[^\\\"]+)"
| eval first_timestamp=mvindex(timestamp,0), last_timestamp=mvindex(timestamp, -1)
| eval first_ts = strptime(first_timestamp, "%Y-%m-%dT%H:%M:%S.%3N%Z"), last_ts = strptime(last_timestamp, "%Y-%m-%dT%H:%M:%S.%3N%Z")
| eval diff = last_ts - first_ts
| table timestamp, first_timestamp, last_timestamp, first_ts, last_ts, diff