topic Re: Want To extract values from query output and create a table in Splunk Search

Want To extract values from query output and create a table

RKP — Fri, 02 Aug 2024 10:19:56 GMT

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\" }"}

Re: Want To extract values from query output and create a table

ITWhisperer — Fri, 02 Aug 2024 10:34:43 GMT

| 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

Re: Want To extract values from query output and create a table

RKP — Fri, 02 Aug 2024 10:46:39 GMT

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"

Re: Want To extract values from query output and create a table

RKP — Fri, 02 Aug 2024 10:55:06 GMT

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"

Re: Want To extract values from query output and create a table

ITWhisperer — Fri, 02 Aug 2024 11:05:46 GMT

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.

Re: Want To extract values from query output and create a table

RKP — Fri, 02 Aug 2024 11:14:02 GMT

Getting empty tables.

Re: Want To extract values from query output and create a table

ITWhisperer — Fri, 02 Aug 2024 11:21:50 GMT

Remove the table command and see what you get

Re: Want To extract values from query output and create a table

RKP — Fri, 02 Aug 2024 11:34:02 GMT

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\" }"}

Re: Want To extract values from query output and create a table

ITWhisperer — Fri, 02 Aug 2024 11:33:15 GMT

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

Re: Want To extract values from query output and create a table

RKP — Fri, 02 Aug 2024 11:44:20 GMT

Re: Want To extract values from query output and create a table

ITWhisperer — Fri, 02 Aug 2024 11:55:00 GMT

What do you get if you do this

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

Re: Want To extract values from query output and create a table

RKP — Fri, 02 Aug 2024 12:04:07 GMT

Re: Want To extract values from query output and create a table

ITWhisperer — Fri, 02 Aug 2024 12:07:31 GMT

What about when you do this?

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

Re: Want To extract values from query output and create a table

RKP — Fri, 02 Aug 2024 12:18:38 GMT

Re: Want To extract values from query output and create a table

ITWhisperer — Fri, 02 Aug 2024 12:31:56 GMT

Try this

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

Re: Want To extract values from query output and create a table

RKP — Fri, 02 Aug 2024 13:00:59 GMT

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

Re: Want To extract values from query output and create a table

ITWhisperer — Fri, 02 Aug 2024 13:34:43 GMT

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