- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to build correlationId (transactionId) search?
LealP
Explorer
05-17-2023
02:51 AM
Hi,
Below is an example of my use case:
timestamp | messageId | correlationId | region | category | trace |
17/05/2023 00:00 | 1 | correlationA | UCAN | orders | START |
17/05/2023 00:01 | 2 | correlationA | UCAN | orders | FLOW |
17/05/2023 00:02 | 3 | correlationA | UCAN | orders | FLOW |
17/05/2023 00:03 | 4 | correlationA | UCAN | orders | FLOW |
17/05/2023 00:04 | 5 | correlationA | UCAN | orders | FLOW |
17/05/2023 00:05 | 6 | correlationA | UCAN | orders | FLOW |
17/05/2023 00:06 | 7 | correlationA | UCAN | orders | FLOW |
17/05/2023 00:07 | 8 | correlationA | UCAN | orders | END |
17/05/2023 00:10 | 9 | correlationB | EMEA | accounts | START |
17/05/2023 00:11 | 10 | correlationB | EMEA | accounts | FLOW |
17/05/2023 00:12 | 11 | correlationB | EMEA | accounts | FLOW |
17/05/2023 00:13 | 12 | correlationB | EMEA | accounts | FLOW |
17/05/2023 00:14 | 13 | correlationB | EMEA | accounts | FLOW |
17/05/2023 00:15 | 14 | correlationB | EMEA | accounts | FLOW |
17/05/2023 00:16 | 15 | correlationB | EMEA | accounts | EXCEPTION |
And the expected output:
timestamp | correlationId | region | category | status | #records | duration |
17/05/2023 00:10 | correlationA | UCAN | order | SUCCESS | 6 | 00:07:00 |
17/05/2023 00:16 | correlationB | EMEA | accounts | ERROR | 5 | 00:06:00 |
- the goal is to have one row per correlationId
- the column STATUS should be calculated with the following rule: if there is a row with trace=END it means that the flow ran successfully and the status should be "SUCCESS" and if there is a row with trace=EXCEPTION it means that an error occurred and the status should be "ERROR"
- the number of records should consider only the rows with trace=FLOW
- the duration should be the time elapsed from the row with trace=START until the row trace=END or trace=EXCEPTION
Can you help on building the query?
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
ITWhisperer

SplunkTrust
05-17-2023
02:58 AM
Try something like this
| stats earliest(_time) as start latest(_time) as end last(trace) as status values(region) as region values(category) as category count as records by ocrrelationId
| eval timestamp = end
| eval duration = end-start
| fieldformat duration = tostring(duration, "duration")
