topic Re: Correlate multiple events, extract fields, output to table in Splunk Search

Correlate multiple events, extract fields, output to table

iulianbadea — Mon, 27 Jul 2020 07:31:54 GMT

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?

Re: Correlate multiple events, extract fields, output to table

rnowitzki — Mon, 27 Jul 2020 08:06:01 GMT

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

Re: Correlate multiple events, extract fields, output to table

iulianbadea — Mon, 27 Jul 2020 08:19:10 GMT

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.

Re: Correlate multiple events, extract fields, output to table

to4kawa — Mon, 27 Jul 2020 08:53:49 GMT

| 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

Re: Correlate multiple events, extract fields, output to table

iulianbadea — Mon, 27 Jul 2020 09:26:01 GMT

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.

Re: Correlate multiple events, extract fields, output to table

to4kawa — Mon, 27 Jul 2020 10:09:02 GMT

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

Re: Correlate multiple events, extract fields, output to table

iulianbadea — Mon, 27 Jul 2020 10:11:18 GMT

| 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.

Re: Correlate multiple events, extract fields, output to table

rnowitzki — Mon, 27 Jul 2020 10:23:19 GMT

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

Re: Correlate multiple events, extract fields, output to table

iulianbadea — Mon, 27 Jul 2020 13:08:14 GMT

Thank you both!

Re: Correlate multiple events, extract fields, output to table

iulianbadea — Mon, 27 Jul 2020 20:10:47 GMT

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

Re: Correlate multiple events, extract fields, output to table

spitchika — Mon, 27 Jul 2020 20:46:28 GMT

| 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

Re: Correlate multiple events, extract fields, output to table

spitchika — Mon, 27 Jul 2020 20:48:47 GMT

Try this...