Want To extract values from query output and create a table

[RKP]

Here is the my output data. i want to create a table for path and responsetime . can you please help.

Expecting output is below:

path                                                                                     responsetime

/rkedgeapp/provider/dental/keysearch/           156

{"time": 1722582494370,"host1": "arn:aws:firehose:ca-central-1:2222222:deliverystream/Splunk-Kinesis-apigateway-CA","source1": "rkedgevil-restapi-Access-Logs:API-Gateway-Access-Logs_8o2y6hzl6e/prod","event": "{ \"requestId\":\"d85fa529-3979-44a3-9018-21f81e12eafd\", \"ip\": \"40.82.191.190\", \"caller\":\"-\", \"user\":\"-\",\"requestTime\":\"02/Aug/2024:07:08:14 +0000\", \"httpMethod\":\"POST\",\"resourcePath\":\"/{proxy+}\", \"status\":\"200\",\"protocol\":\"HTTP/1.1\", \"responseLength\":\"573\", \"clientCertIssuerDN\":\"C=GB,ST=Greater Manchester,L=Salford,O=Sectigo Limited,CN=Sectigo RSA Organization Validation Secure Server CA\", \"clientCertSerialNumber\":\"22210811239199552309700144370732535146\", \"clientCertNotBefore\":\"Jan 22 00:00:00 2024 GMT\", \"clientCertNotAfter\":\"Jan 21 23:59:59 2025 GMT\", \"path\":\"/rkedgeapp/provider/dental/keysearch/\", \"responsetime\":\"156\" }"}

[RKP]

Below  query is giving the 3000 events like that, how can i make this command work for that. can please give the straight command.

index="aws-apigateway" source1="rkedgevil-restapi-Access-Logs:API-Gateway-Access-Logs_8o2y6hzl/prod"

[ITWhisperer]

| makeresults 
| eval _raw="{\"time\": 1722582494370,\"host1\": \"arn:aws:firehose:ca-central-1:2222222:deliverystream/Splunk-Kinesis-apigateway-CA\",\"source1\": \"rkedgevil-restapi-Access-Logs:API-Gateway-Access-Logs_8o2y6hzl6e/prod\",\"event\": \"{ \\\"requestId\\\":\\\"d85fa529-3979-44a3-9018-21f81e12eafd\\\", \\\"ip\\\": \\\"40.82.191.190\\\", \\\"caller\\\":\\\"-\\\", \\\"user\\\":\\\"-\\\",\\\"requestTime\\\":\\\"02/Aug/2024:07:08:14 +0000\\\", \\\"httpMethod\\\":\\\"POST\\\",\\\"resourcePath\\\":\\\"/{proxy+}\\\", \\\"status\\\":\\\"200\\\",\\\"protocol\\\":\\\"HTTP/1.1\\\", \\\"responseLength\\\":\\\"573\\\", \\\"clientCertIssuerDN\\\":\\\"C=GB,ST=Greater Manchester,L=Salford,O=Sectigo Limited,CN=Sectigo RSA Organization Validation Secure Server CA\\\", \\\"clientCertSerialNumber\\\":\\\"22210811239199552309700144370732535146\\\", \\\"clientCertNotBefore\\\":\\\"Jan 22 00:00:00 2024 GMT\\\", \\\"clientCertNotAfter\\\":\\\"Jan 21 23:59:59 2025 GMT\\\", \\\"path\\\":\\\"/rkedgeapp/provider/dental/keysearch/\\\", \\\"responsetime\\\":\\\"156\\\" }\"}"
``` the line above recreate your sample event ```
| spath
| spath input=event
| table path responsetime

[RKP]

Below  query is giving the 3000 events like that, how can i make this command work for that. can please give the straight command.

index="aws-apigateway" source1="rkedgevil-restapi-Access-Logs:API-Gateway-Access-Logs_8o2y6hzl/prod"

[ITWhisperer]

Try something like this

index="aws-apigateway" source1="rkedgevil-restapi-Access-Logs:API-Gateway-Access-Logs_8o2y6hzl/prod"
| spath
| spath input=event
| table path responsetime

 You may not need the first spath command if your ingestion path already recognises JSON data format.

[RKP]

Its giving same output which i provided 1st.

{"time": 1722597668055,"host1": "arn:aws:firehose:ca-central-1:2222:deliverystream/Splunk-Kinesis-apigateway-CA","source1": "manuuatedgevil-restapi-Access-Logs:API-Gateway-Access-Logs_8o2y6hzl6e/prod","event": "{ \"requestId\":\"dffc1e08-83d7-4801-b10d-239efd1b7f7d\", \"ip\": \"40.82.191.190\", \"caller\":\"-\", \"user\":\"-\",\"requestTime\":\"02/Aug/2024:11:21:08 +0000\", \"httpMethod\":\"POST\",\"resourcePath\":\"/{proxy+}\", \"status\":\"200\",\"protocol\":\"HTTP/1.1\", \"responseLength\":\"573\", \"clientCertIssuerDN\":\"C=GB,ST=Greater Manchester,L=Salford,O=Sectigo Limited,CN=Sectigo RSA Organization Validation Secure Server CA\", \"clientCertSerialNumber\":\"22210811239199552309700144370732535146\", \"clientCertNotBefore\":\"Jan 22 00:00:00 2024 GMT\", \"clientCertNotAfter\":\"Jan 21 23:59:59 2025 GMT\", \"path\":\"/rkedgeapp/provider/dental/keysearch/\", \"responsetime\":\"172\" }"}

[ITWhisperer]

What is this that you have just shown? Please provide a screenshot

[RKP]

[ITWhisperer]

What do you get if you do this

index="aws-apigateway" source1="rkedgevil-restapi-Access-Logs:API-Gateway-Access-Logs_8o2y6hzl/prod"
| table event

[RKP]

[ITWhisperer]

What about when you do this?

index="aws-apigateway" source1="rkedgevil-restapi-Access-Logs:API-Gateway-Access-Logs_8o2y6hzl/prod"
| spath input=event

[RKP]

[ITWhisperer]

Try this

index="aws-apigateway" source1="rkedgevil-restapi-Access-Logs:API-Gateway-Access-Logs_8o2y6hzl/prod"
| spath input=event
| table *

[RKP]

Its showing most of the values but there is no path and responsetime

[ITWhisperer]

Can you do the same but scroll the view to the right to show the fields beginning with "p"

[RKP]

Getting empty tables.

[ITWhisperer]

Remove the table command and see what you get