To extract the timestamp for the events

[parthiban]

Hi everyone

I need to grouping the below 3 events with correlation ID. I have tried transaction cmd below but it is not taking multiple ends with. And also I need to extract the event start timestamp and event end timestamp.

| transaction correlation_id startswith="processing_stage=Obtained data" endswith="processing_stage=Successfully obtained incontact response" endswith="processing_stage=Successfully obtained genesys response"

{"message_type": "INFO", "processing_stage": "Obtained data", "message": "Successfully received data from API/SQS", "correlation_id": "c5be6c24-d0e6-4f27-a11d-86f7f194ae50", "error": "", "invoked_component": "prd-start-step-function-from-lambda-v1", 'startDate': datetime.datetime(2023, 11, 1, 5, 17, 50, 326000, tzinfo=tzlocal()), 'date': 'Wed, 01 Nov 2023 05:17:50 GMT', "invocation_timestamp": "2023-11-01T05:17:50Z", "response_timestamp": "2023-11-01T05:17:50Z", }

{"message_type": "INFO", "processing_stage": "Successfully obtained genesys response", "message": "Successfully obtained genesys response", "correlation_id": "c5be6c24-d0e6-4f27-a11d-86f7f194ae50", "error": "", "invoked_component": "prd-ccm-genesys-ingestor-v1", "request_payload": "", "response_details": "", "invocation_timestamp": "2023-11-01T05:18:21Z", "response_timestamp": "2023-11-01T05:18:21Z"}

{"message_type": "INFO", "processing_stage": "Successfully obtained incontact response", "message": "Successfully obtained incontact response", "correlation_id": "['330dba31-3d3d-4bf0-91a3-dfba81b56abf']", "error": "", "invoker_agent": "arn:aws:sqs:eu-central-1:981503094308:prd-ccm-incontact-ingestor-queue-v1", "invoked_component": "prd-ccm-incontact-ingestor-v1",  "invocation_timestamp": "2023-11-01T06:57:09Z", "response_timestamp": "2023-11-01T06:57:09Z"}

Thanks in advance

[ITWhisperer]

It is not clear what is going on here - you have what looks like JSON although not all of it is correctly formatted; you have different correlation ids; you have different timestamp formats.

To make things a bit clearer, please share your sample events in a code block </> to preserve the original formatting of the events. Also, please state whether this is actually JSON and whether the fields have already been extracted.

Also, please share what your expected output might look like for the shared events, and if it is not obvious from the output, what processing is expected to get the output from the input.

[parthiban]

Hi @ITWhisperer 

We expect this kind of results. We are migrating the code from sumologic to splunk. The below result is comes from sumologic. 



Yes you are correct, in this case we have different correlation id format.  I have shared the sample event here.

{"message_type": "INFO", "processing_stage": "Obtained data", "message": "Successfully received data from API/SQS", "correlation_id": "00190cdd-1d12-477f-bcc9-a4e2c3dcfb22", "error": "", "invoked_component": "prd-start-step-function-from-lambda-v1", "request_payload": "", "response_details": "{'executionArn': 'arn:aws:states:eu-central-1:981503094308:execution:contact-centre-dialer-service:1da26863-1645-4961-9992-c450cadf4ebd', 'startDate': datetime.datetime(2023, 11, 1, 9, 6, 36, 152000, tzinfo=tzlocal()), 'ResponseMetadata': {'RequestId': 'aba3f7b2-4d4b-4f53-9a5a-fa2c0d0754fd', 'HTTPStatusCode': 200, 'HTTPHeaders': {'x-amzn-requestid': 'aba3f7b2-4d4b-4f53-9a5a-fa2c0d0754fd', 'date': 'Wed, 01 Nov 2023 09:06:36 GMT', 'content-type': 'application/x-amz-json-1.0', 'content-length': '165', 'connection': 'keep-alive'}, 'RetryAttempts': 0}}", "invocation_timestamp": "2023-11-01T09:06:36Z", "response_timestamp": "2023-11-01T09:06:36Z"}

[ITWhisperer]

While you have shown what you are trying to do, it isn't much clearer. Please can you show an example of a row you expect in the results table i.e. event id, start time and end time, and the raw events that this information would be extracted from. Also, you still haven't clarified whether you already have the JSON fields extracted (or whether you need help extracting those as well).

[parthiban]

HI @ITWhisperer 

I have Json extracted fields already.  For your reference I have attached the Sumologic query

(( _sourcehost="/aws/lambda/prd-ccm-genesys-ingestor-v1" OR _sourcehost="/aws/lambda/prd-start-step-function-from-lambda-v1" OR _sourcehost="/aws/lambda/prd-ccm-incontact-ingestor-v1"))
| parse "\"correlation_id\":\"*\"," as event_id nodrop
| parse "\"message_type\":\"*\",\"processing_stage\":\"*\"," as type,stage
| extract field=event_id "'(?<event_id>[a-zA-Z0-9-]+)" multi nodrop
| transaction on event_id with "*Obtained data*" as events_received,with "*Successfully obtained incontact response*" as sent_to_incontact, with "*Successfully obtained genesys response*" as sent_to_genesys
| where _others != 1
| ((_end_time - _start_time)/1000) as total_time_to_insert_record_in_contact_centre
| formatDate(toLong(_start_time),"MM-dd-yyyy HH:mm:ss:SSS") as events_received$start_time$
| formatDate(toLong(_end_time),"MM-dd-yyyy HH:mm:ss:SSS") as sent_to_contact_centre$end_time$
| fields event_id,events_received$start_time$,sent_to_contact_centre$end_time$,total_time_to_insert_record_in_contact_centre

[ITWhisperer]

Yeah, this isn't really what I asked for. I sorry that I can't have been more help, but without the relevant information, it is difficult to suggest a way forward.

[parthiban]

Hi @ITWhisperer 

Can you help me what you need exactly.

[ITWhisperer]

As I said earlier, "Please can you show an example of a row you expect in the results table i.e. event id, start time and end time, and the raw events that this information would be extracted from."

[parthiban]

Hi @ITWhisperer 

I am not sure, this is you are expecting.. If not can you please give some assist how to get it.

Correlation ID	Event start time	Event end time	Difference
930fd232-8d16-4d1f-8725-a5893e9a46c7	11-01-2023 13:19:06:653	11-01-2023 13:19:23:359	16.706

{"message_type": "INFO", "processing_stage": "Obtained data", "message": "Successfully received data from API/SQS", "correlation_id": "0cd56112-6346-4ea3-8a2f-2b59b9eb68ba", "error": "", "invoked_component": "prd-start-step-function-from-lambda-v1", "request_payload": "", "response_details": "{'executionArn': 'arn:aws:states:eu-central-1:981503094308:execution:contact-centre-dialer-service:8a1acb14-b170-4f95-99bc-7a89ff814207', 'startDate': datetime.datetime(2023, 11, 1, 11, 33, 41, 354000, tzinfo=tzlocal()), 'ResponseMetadata': {'RequestId': '60427a29-6dd4-4cdf-b5c0-fc6cb45b08b2', 'HTTPStatusCode': 200, 'HTTPHeaders': {'x-amzn-requestid': '60427a29-6dd4-4cdf-b5c0-fc6cb45b08b2', 'date': 'Wed, 01 Nov 2023 11:33:41 GMT', 'content-type': 'application/x-amz-json-1.0', 'content-length': '165', 'connection': 'keep-alive'}, 'RetryAttempts': 0}}", "invocation_timestamp": "2023-11-01T11:33:41Z", "response_timestamp": "2023-11-01T11:33:41Z", "custom_attributes": {"entity-internal-id": "", "root-entity-id": "", "student_id": "64690945", "lead-id": "37079165", "country": "Nepal"}}

[ITWhisperer]

This appears to be a single event. The correlation id doesn't match and neither do any of the dates. Please can you provide the correct corresponding events for the row shown?

[parthiban]

Hi @ITWhisperer 

Correlation ID	Event start time	Event end time	Difference
0cd56112-6346-4ea3-8a2f-2b59b9eb68ba	11-01-2023 17:03:41:321	11-01-2023 17:04:04:300	22.979

{"message_type": "INFO", "processing_stage": "Obtained data", "message": "Successfully received data from API/SQS", "correlation_id": "0cd56112-6346-4ea3-8a2f-2b59b9eb68ba", "error": "", "invoked_component": "prd-start-step-function-from-lambda-v1", "request_payload": "", "response_details": "{'executionArn': 'arn:aws:states:eu-central-1:981503094308:execution:contact-centre-dialer-service:8a1acb14-b170-4f95-99bc-7a89ff814207', 'startDate': datetime.datetime(2023, 11, 1, 11, 33, 41, 354000, tzinfo=tzlocal()), 'ResponseMetadata': {'RequestId': '60427a29-6dd4-4cdf-b5c0-fc6cb45b08b2', 'HTTPStatusCode': 200, 'HTTPHeaders': {'x-amzn-requestid': '60427a29-6dd4-4cdf-b5c0-fc6cb45b08b2', 'date': 'Wed, 01 Nov 2023 11:33:41 GMT', 'content-type': 'application/x-amz-json-1.0', 'content-length': '165', 'connection': 'keep-alive'}, 'RetryAttempts': 0}}", "invocation_timestamp": "2023-11-01T11:33:41Z", "response_timestamp": "2023-11-01T11:33:41Z", "custom_attributes": {"entity-internal-id": "", "root-entity-id": "", "student_id": "64690945", "lead-id": "37079165", "country": "Nepal"}}

{"message_type": "INFO", "processing_stage": "Successfully obtained genesys response", "message": "Successfully obtained genesys response", "correlation_id": "0cd56112-6346-4ea3-8a2f-2b59b9eb68ba", "error": "", "invoker_agent": "arn:aws:sqs:eu-central-1:981503094308:prd-ccm-genesys-ingestor-queue-v1", "invoked_component": "prd-ccm-genesys-ingestor-v1", "request_payload": "", "response_details": "", "invocation_timestamp": "2023-11-01T11:34:04Z", "response_timestamp": "2023-11-01T11:34:04Z", "original_source_app": "YMKT", "target_idp_application": "", "retry_attempt": "1", "custom_attributes": {"entity-internal-id": "", "root-entity-id": "", "campaign-id": "4e749ade-ac9c-45e0-94fe-9ae21e1398d8", "campaign-name": "", "marketing-area": "IDP_NPL", "lead-id": "37079165", "record_count": "", "country": "Nepal"}}

[ITWhisperer]

From these events, where exactly do the timestamps come from?

[parthiban]

Hi @ITWhisperer 

"invocation_timestamp": "2023-11-01T11:33:41Z"

"processing_stage": "Obtained data">>> Start time

"processing_stage": "Successfully obtained incontact response" >>> End time

[ITWhisperer]

| eval start_time=if(processing_stage="Obtained data",invocation_timestamp,null())
| eval end_time=if(processing_stage="Successfully obtained genesys response",invocation_timestamp,null())
| stats values(start_time) as start_time values(end_time) as end_time by correlation_id
| eval difference=strptime(end_time,"%FT%TZ")-strptime(start_time,"%FT%TZ")

[parthiban]

Hi @ITWhisperer 
Really appreciate your patience and supporting me, Here the results are 'end time is not populating for most of the events, Need only event contain both start and end time stamp.

[ITWhisperer]

| where isnotnull(end_time)

[parthiban]

Hi @ITWhisperer 

Sorry for the late response. Its works after few modifications in my code. Thanks for your support.

[yuanliu]

The title is a little confusing.  Based on your description, event timestamps are fine and do not need another "extraction"; your concern is how to obtain the start and end of the tri-event group.  Is this correct?

As you observed, there is some limitation in transaction command.  startswith-endswith works best with a clear starting and ending.  Do you mean to say that "Successfully obtained incontact response" and "Successfully obtained genesys response" could appear in arbitrary orders?  Do you mean to say that for each correlation_id, there are only these three events? (Note that the three examples do not all have the same correlation_id.)  Before exploring other options in transaction command, know that it is expensive and is best avoided unless your application has special needs for it.

To obtain start time and end time of the group, and to preserve meaningful field values, you can use min, max, and list functions with stats command.  For example,

| stats min(_time) as _time max(_time) as group_end list(*) as * by correlation_id

Using list on all fields can also be expensive.  So you may want to select just those that matter to your use case.

In the end, there are many ways to fulfill a use case.  But particulars in the use case determine how to best get the results.

[parthiban]

Hi @ITWhisperer @yuanliu 

We are getting multiple events in each lambda. We need to extract start time and end time of the particular event and also need difference of the start and end time. As I mentioned above image has correlation id , start time , end time and difference. Please let me know if any input we want. Thanks

1

930fd232-8d16-4d1f-8725-a5893e9a46c7

11-01-2023 13:19:06:653

11-01-2023 13:19:23:359

16.706