Correlate multiple events, extract fields, output to table

[iulianbadea]

Hi everyone,

I'm trying to correlate some events that have same field and then to output the results to a table.

Example of raw data:

test d34e9bca-cfd9-11ea-9873-962481bd1187 Overall Executions in this runtime: 295
test d34e9bca-cfd9-11ea-9873-962481bd1187 End Execution
test d34e9bca-cfd9-11ea-9873-962481bd1187 Total Execution Time: 1.6354868500493467
test d34e9bca-cfd9-11ea-9873-962481bd1187 Query Elapsed Time 0.5768028399907053
test d34e9bca-cfd9-11ea-9873-962481bd1187 Query Status: Success
test d34e9bca-cfd9-11ea-9873-962481bd1187 Query Result: {"EXPR$0":{"0":1595834505}}
test d34e9bca-cfd9-11ea-9873-962481bd1187 Connection elapsed time: 1.056466632988304
test d34e9bca-cfd9-11ea-9873-962481bd1187 Establishing connection as: user@domain
test d34e9bca-cfd9-11ea-9873-962481bd1187 Begin Execution

For each "test" I have 9 events in Splunk.

I want to output to a table like:

ID, Query_status, Query_time, Total_time

d34e9bca-cfd9-11ea-9873-962481bd1187, Success, 0.57, 1.63

Which would be the best method to accomplish this? 

[rnowitzki]

Hi @iulianbadea ,

Do you have already fields for query status, query time and total time?

If yes, this here is a good starting point:

| transaction id
| fields id, query_status, query_time, total_time

You could finetune it with startswith="Begin Execution" endswith="End Execution" (if applicable) as described here.

Let us know if you need additional help to extract the fields.

Hope it helps

BR
Ralph

[iulianbadea]

Hi @rnowitzki,

I've tried:

index=myindex sourcetype=mysource

| rex field=_raw "test (?<id>.*) Begin Execution"
| rex field=_raw "Query Status: (?<query_status>.*)"
| rex field=_raw "Query Elapsed Time (?<query_time>.*)"
| rex field=_raw "Total Execution Time: (?<total_time>.*)"
| transaction id
| table id, query_status, query_time, total_time

But it only shows me the "id", the rest of the fields are empty.

Not sure if I can use startswith="Begin Execution" endswith="End Execution" because I don't have timestamps on these events, they are sent to Splunk in bulk each 10 mins, having as timestamps the time when were sent.

[to4kawa]

| makeresults
| eval _raw="test d34e9bca-cfd9-11ea-9873-962481bd1187 Overall Executions in this runtime: 295
test d34e9bca-cfd9-11ea-9873-962481bd1187 End Execution
test d34e9bca-cfd9-11ea-9873-962481bd1187 Total Execution Time: 1.6354868500493467
test d34e9bca-cfd9-11ea-9873-962481bd1187 Query Elapsed Time 0.5768028399907053
test d34e9bca-cfd9-11ea-9873-962481bd1187 Query Status: Success
test d34e9bca-cfd9-11ea-9873-962481bd1187 Query Result: {\"EXPR$0\":{\"0\":1595834505}}
test d34e9bca-cfd9-11ea-9873-962481bd1187 Connection elapsed time: 1.056466632988304
test d34e9bca-cfd9-11ea-9873-962481bd1187 Establishing connection as: user@domain
test d34e9bca-cfd9-11ea-9873-962481bd1187 Begin Execution"
| multikv noheader=t
| table _raw
| rename COMMENT as "this is sample"
| rex "(?<common>\w+)\s(?<ID>\S+)\s(?<messages>.*)"
| rex field=messages "(?<field>.*):? (?<value>\S+)"
| eval {field}=value
| fields - field value messages
| stats values(*) as * by common
| rename "Query Status:" as Query_status
| rename "Query Elapsed Time" as Query_time
| rename "Total Execution Time:" as Total_time
| table ID, Query_status, Query_time, Total_time

[iulianbadea]

Wow, thank you @to4kawa 

Almost there...seems that all the columns are sorted and not correlated.

I mean for the first ID I have different connection times.

[to4kawa]

your sample is not enough information.You should be the first to tell us.

[iulianbadea]

| makeresults
| eval _raw="test a1314456-cfef-11ea-a30e-962481bd1187 Overall Executions in this runtime: 961
test a1314456-cfef-11ea-a30e-962481bd1187 End Execution
test a1314456-cfef-11ea-a30e-962481bd1187 Total Execution Time: 1.2524148670490831
test a1314456-cfef-11ea-a30e-962481bd1187 Query Elapsed Time 0.5367236440069973
test a1314456-cfef-11ea-a30e-962481bd1187 Query Status: Success
test a1314456-cfef-11ea-a30e-962481bd1187 Query Result: {\"EXPR$0\":{\"0\":1595843870}}
test a1314456-cfef-11ea-a30e-962481bd1187 Connection elapsed time: 0.7129632540745661
test a1314456-cfef-11ea-a30e-962481bd1187 Establishing connection as: user@domain
test a1314456-cfef-11ea-a30e-962481bd1187 Begin Execution
test a04e872e-cfef-11ea-a30e-962481bd1187 Overall Executions in this runtime: 960
test a04e872e-cfef-11ea-a30e-962481bd1187 End Execution
test a04e872e-cfef-11ea-a30e-962481bd1187 Total Execution Time: 1.1855176850222051
test a04e872e-cfef-11ea-a30e-962481bd1187 Query Elapsed Time 0.4926855160156265
test a04e872e-cfef-11ea-a30e-962481bd1187 Query Status: Success
test a04e872e-cfef-11ea-a30e-962481bd1187 Query Result: {\"EXPR$0\":{\"0\":1595843868}}
test a04e872e-cfef-11ea-a30e-962481bd1187 Connection elapsed time: 0.6907656920375302
test a04e872e-cfef-11ea-a30e-962481bd1187 Establishing connection as: user@domain
test a04e872e-cfef-11ea-a30e-962481bd1187 Begin Execution
test 9f626fb0-cfef-11ea-a30e-962481bd1187 Overall Executions in this runtime: 959
test 9f626fb0-cfef-11ea-a30e-962481bd1187 End Execution
test 9f626fb0-cfef-11ea-a30e-962481bd1187 Total Execution Time: 1.2467742280568928
test 9f626fb0-cfef-11ea-a30e-962481bd1187 Query Elapsed Time 0.5091846379218623
test 9f626fb0-cfef-11ea-a30e-962481bd1187 Query Status: Success
test 9f626fb0-cfef-11ea-a30e-962481bd1187 Query Result: {\"EXPR$0\":{\"0\":1595843867}}
test 9f626fb0-cfef-11ea-a30e-962481bd1187 Connection elapsed time: 0.7355797099880874
test 9f626fb0-cfef-11ea-a30e-962481bd1187 Establishing connection as: user@domain
test 9f626fb0-cfef-11ea-a30e-962481bd1187 Begin Execution
test 9e69ea5c-cfef-11ea-a30e-962481bd1187 Overall Executions in this runtime: 958
test 9e69ea5c-cfef-11ea-a30e-962481bd1187 End Execution
test 9e69ea5c-cfef-11ea-a30e-962481bd1187 Total Execution Time: 1.328197255032137
test 9e69ea5c-cfef-11ea-a30e-962481bd1187 Query Elapsed Time 0.542056486941874
test 9e69ea5c-cfef-11ea-a30e-962481bd1187 Query Status: Success
test 9e69ea5c-cfef-11ea-a30e-962481bd1187 Query Result: {\"EXPR$0\":{\"0\":1595843865}}
test 9e69ea5c-cfef-11ea-a30e-962481bd1187 Connection elapsed time: 0.784138589981012
test 9e69ea5c-cfef-11ea-a30e-962481bd1187 Establishing connection as: user@domain
test 9e69ea5c-cfef-11ea-a30e-962481bd1187 Begin Execution"
| multikv noheader=t
| table _raw
| rename COMMENT as "this is sample"
| rex "(?<common>\w+)\s(?<ID>\S+)\s(?<messages>.*)"
| rex field=messages "(?<field>.*):? (?<value>\S+)"
| eval {field}=value
| fields - field value messages
| stats values(*) as * by common
| rename "Query Status:" as Query_status
| rename "Query Elapsed Time" as Query_time
| rename "Total Execution Time:" as Total_time
| table ID, Query_status, Query_time, Total_time

Here are 4 full events, maybe it's clear now what I'm trying to say. All the columns are sorted/scrambled.

[rnowitzki · Accepted solution]

Hi @iulianbadea ,

Based on @to4kawa 's approach and your sample data,  I edited the SPL slightly:

| makeresults
| eval _raw="test a1314456-cfef-11ea-a30e-962481bd1187 Overall Executions in this runtime: 961
test a1314456-cfef-11ea-a30e-962481bd1187 End Execution
test a1314456-cfef-11ea-a30e-962481bd1187 Total Execution Time: 1.2524148670490831
test a1314456-cfef-11ea-a30e-962481bd1187 Query Elapsed Time 0.5367236440069973
test a1314456-cfef-11ea-a30e-962481bd1187 Query Status: Success
test a1314456-cfef-11ea-a30e-962481bd1187 Query Result: {\"EXPR$0\":{\"0\":1595843870}}
test a1314456-cfef-11ea-a30e-962481bd1187 Connection elapsed time: 0.7129632540745661
test a1314456-cfef-11ea-a30e-962481bd1187 Establishing connection as: user@domain
test a1314456-cfef-11ea-a30e-962481bd1187 Begin Execution
test a04e872e-cfef-11ea-a30e-962481bd1187 Overall Executions in this runtime: 960
test a04e872e-cfef-11ea-a30e-962481bd1187 End Execution
test a04e872e-cfef-11ea-a30e-962481bd1187 Total Execution Time: 1.1855176850222051
test a04e872e-cfef-11ea-a30e-962481bd1187 Query Elapsed Time 0.4926855160156265
test a04e872e-cfef-11ea-a30e-962481bd1187 Query Status: Success
test a04e872e-cfef-11ea-a30e-962481bd1187 Query Result: {\"EXPR$0\":{\"0\":1595843868}}
test a04e872e-cfef-11ea-a30e-962481bd1187 Connection elapsed time: 0.6907656920375302
test a04e872e-cfef-11ea-a30e-962481bd1187 Establishing connection as: user@domain
test a04e872e-cfef-11ea-a30e-962481bd1187 Begin Execution
test 9f626fb0-cfef-11ea-a30e-962481bd1187 Overall Executions in this runtime: 959
test 9f626fb0-cfef-11ea-a30e-962481bd1187 End Execution
test 9f626fb0-cfef-11ea-a30e-962481bd1187 Total Execution Time: 1.2467742280568928
test 9f626fb0-cfef-11ea-a30e-962481bd1187 Query Elapsed Time 0.5091846379218623
test 9f626fb0-cfef-11ea-a30e-962481bd1187 Query Status: Success
test 9f626fb0-cfef-11ea-a30e-962481bd1187 Query Result: {\"EXPR$0\":{\"0\":1595843867}}
test 9f626fb0-cfef-11ea-a30e-962481bd1187 Connection elapsed time: 0.7355797099880874
test 9f626fb0-cfef-11ea-a30e-962481bd1187 Establishing connection as: user@domain
test 9f626fb0-cfef-11ea-a30e-962481bd1187 Begin Execution
test 9e69ea5c-cfef-11ea-a30e-962481bd1187 Overall Executions in this runtime: 958
test 9e69ea5c-cfef-11ea-a30e-962481bd1187 End Execution
test 9e69ea5c-cfef-11ea-a30e-962481bd1187 Total Execution Time: 1.328197255032137
test 9e69ea5c-cfef-11ea-a30e-962481bd1187 Query Elapsed Time 0.542056486941874
test 9e69ea5c-cfef-11ea-a30e-962481bd1187 Query Status: Success
test 9e69ea5c-cfef-11ea-a30e-962481bd1187 Query Result: {\"EXPR$0\":{\"0\":1595843865}}
test 9e69ea5c-cfef-11ea-a30e-962481bd1187 Connection elapsed time: 0.784138589981012
test 9e69ea5c-cfef-11ea-a30e-962481bd1187 Establishing connection as: user@domain
test 9e69ea5c-cfef-11ea-a30e-962481bd1187 Begin Execution"
| multikv noheader=t
| table _raw
| rename COMMENT as "this is sample"
| rex "(?<common>\w+)\s(?<ID>\S+)\s(?<messages>.*)"
| rex field=messages "(?<field>.*):? (?<value>\S+)"
| eval {field}=value
| fields - field value messages

| rename "Query Status:" as Query_status
| rename "Query Elapsed Time" as Query_time
| rename "Total Execution Time:" as Total_time
| chart values(Query_status), values(Query_time), values(Total_time) by ID

[iulianbadea]

Thank you both!

[iulianbadea]

Can you please help once more? Timestamp is also present now:

Sample data:

2020-07-27T17:55:40.990228+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Begin Execution
2020-07-27T17:55:40.990270+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Establishing connection as: user@domain
2020-07-27T17:55:41.677376+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Connection elapsed time: 0.6870694829999948
2020-07-27T17:55:42.149634+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Query Result: {\"EXPR$0\":{\"0\":1595872451}}
2020-07-27T17:55:42.149669+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Query Status: Success
2020-07-27T17:55:42.149685+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Query Elapsed Time 0.4722382859999996
2020-07-27T17:55:42.218875+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Total Execution Time: 1.2286392209999946
2020-07-27T17:55:42.218918+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 End Execution
2020-07-27T17:55:42.218952+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Overall Executions in this runtime: 20
2020-07-27T17:55:42.522960+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Begin Execution
2020-07-27T17:55:42.523002+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Establishing connection as: user@domain
2020-07-27T17:55:43.120431+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Connection elapsed time: 0.5973759029999997
2020-07-27T17:55:43.690096+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Query Result: {\"EXPR$0\":{\"0\":1595872453}}
2020-07-27T17:55:43.690128+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Query Status: Success
2020-07-27T17:55:43.690144+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Query Elapsed Time 0.5696396760000013
2020-07-27T17:55:43.747893+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Total Execution Time: 1.224972496999996
2020-07-27T17:55:43.747934+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 End Execution
2020-07-27T17:55:43.747947+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Overall Executions in this runtime: 21

[spitchika]

Try this...

| makeresults
| eval _raw="2020-07-27T17:55:40.990228+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Begin Execution
2020-07-27T17:55:40.990270+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Establishing connection as: user@domain
2020-07-27T17:55:41.677376+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Connection elapsed time: 0.6870694829999948
2020-07-27T17:55:42.149634+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Query Result: {\"EXPR$0\":{\"0\":1595872451}}
2020-07-27T17:55:42.149669+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Query Status: Success
2020-07-27T17:55:42.149685+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Query Elapsed Time 0.4722382859999996
2020-07-27T17:55:42.218875+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Total Execution Time: 1.2286392209999946
2020-07-27T17:55:42.218918+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 End Execution
2020-07-27T17:55:42.218952+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Overall Executions in this runtime: 20
2020-07-27T17:55:42.522960+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Begin Execution
2020-07-27T17:55:42.523002+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Establishing connection as: user@domain
2020-07-27T17:55:43.120431+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Connection elapsed time: 0.5973759029999997
2020-07-27T17:55:43.690096+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Query Result: {\"EXPR$0\":{\"0\":1595872453}}
2020-07-27T17:55:43.690128+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Query Status: Success
2020-07-27T17:55:43.690144+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Query Elapsed Time 0.5696396760000013
2020-07-27T17:55:43.747893+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Total Execution Time: 1.224972496999996
2020-07-27T17:55:43.747934+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 End Execution
2020-07-27T17:55:43.747947+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Overall Executions in this runtime: 21"
| multikv noheader=t
| table _raw
| rename COMMENT as "this is sample"
| rex "\d+ (?<common>[^ ]+)\W(?<ID>\S+)\s(?<messages>.*)"
| rex field=messages "(?<field>.*):? (?<value>\S+)"
| eval {field}=value
| fields - field value message
| rename "Query Status:" as Query_status
| rename "Query Elapsed Time" as Query_time
| rename "Total Execution Time:" as Total_time
| chart values(Query_status), values(Query_time), values(Total_time) by ID

[spitchika]

| makeresults
| eval _raw="2020-07-27T17:55:40.990228+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Begin Execution
2020-07-27T17:55:40.990270+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Establishing connection as: user@domain
2020-07-27T17:55:41.677376+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Connection elapsed time: 0.6870694829999948
2020-07-27T17:55:42.149634+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Query Result: {\"EXPR$0\":{\"0\":1595872451}}
2020-07-27T17:55:42.149669+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Query Status: Success
2020-07-27T17:55:42.149685+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Query Elapsed Time 0.4722382859999996
2020-07-27T17:55:42.218875+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Total Execution Time: 1.2286392209999946
2020-07-27T17:55:42.218918+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 End Execution
2020-07-27T17:55:42.218952+00:00 test-test-test 62a14dc4-d032-11ea-a166-acde48001122 Overall Executions in this runtime: 20
2020-07-27T17:55:42.522960+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Begin Execution
2020-07-27T17:55:42.523002+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Establishing connection as: user@domain
2020-07-27T17:55:43.120431+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Connection elapsed time: 0.5973759029999997
2020-07-27T17:55:43.690096+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Query Result: {\"EXPR$0\":{\"0\":1595872453}}
2020-07-27T17:55:43.690128+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Query Status: Success
2020-07-27T17:55:43.690144+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Query Elapsed Time 0.5696396760000013
2020-07-27T17:55:43.747893+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Total Execution Time: 1.224972496999996
2020-07-27T17:55:43.747934+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 End Execution
2020-07-27T17:55:43.747947+00:00 test-test-test 638b2c5a-d032-11ea-a166-acde48001122 Overall Executions in this runtime: 21"
| multikv noheader=t
| table _raw
| rename COMMENT as "this is sample"
| rex "\d+ (?<common>[^ ]+)\W(?<ID>\S+)\s(?<messages>.*)"
| rex field=messages "(?<field>.*):? (?<value>\S+)"
| eval {field}=value
| fields - field value message
| rename "Query Status:" as Query_status
| rename "Query Elapsed Time" as Query_time
| rename "Total Execution Time:" as Total_time
| chart values(Query_status), values(Query_time), values(Total_time) by ID