Splunk Search

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

anna
Explorer

 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, TLS_DealerEmail__c=bondijunction@team.telstra.com, 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"}

0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

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, TLS_DealerEmail__c=bondijunction@team.telstra.com, 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

 

---
If this reply helps you, Karma would be appreciated.

View solution in original post

richgalloway
SplunkTrust
SplunkTrust

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, TLS_DealerEmail__c=bondijunction@team.telstra.com, 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

 

---
If this reply helps you, Karma would be appreciated.
Get Updates on the Splunk Community!

Why You Can't Miss .conf25: Unleashing the Power of Agentic AI with Splunk & Cisco

The Defining Technology Movement of Our Lifetime The advent of agentic AI is arguably the defining technology ...

Deep Dive into Federated Analytics: Unlocking the Full Power of Your Security Data

In today’s complex digital landscape, security teams face increasing pressure to protect sprawling data across ...

Your summer travels continue with new course releases

Summer in the Northern hemisphere is in full swing, and is often a time to travel and explore. If your summer ...