topic Re: How to search and create table report? in Splunk Search

How to search and create table report?

runiyal — Wed, 05 Apr 2023 19:22:34 GMT

I have a logfile with information like this -

2023-04-05 13:54:17.259 INFO [http-nio-8080-exec-117][OTPViewController:206] The list of the form bean for Kubra
2023-04-05 13:54:17.260 INFO [http-nio-8080-exec-117][OTPViewController:207] Payment Request ID for debug the Kubra payment. DanBkDg981
2023-04-05 13:54:17.260 INFO [http-nio-8080-exec-117][OTPViewController:208] Amount Number . 00902418
2023-04-05 13:54:17.260 INFO [http-nio-8080-exec-117][OTPViewController:209] Policy Number. 05349531
2023-04-05 13:54:17.261 INFO [http-nio-8080-exec-117][OTPViewController:210] Address. 2912 9TH ST W
2023-04-05 13:54:17.261 INFO [http-nio-8080-exec-117][OTPViewController:211] Email. test@aol.com
2023-04-05 13:54:17.262 INFO [http-nio-8080-exec-117][OTPViewController:212] Pmt Amount . 999.00
2023-04-05 13:54:17.262 INFO [http-nio-8080-exec-117][OTPViewController:213] Pmt Date . 05012023I

Need a report in table format for these columns:

"RequestID" "Policy Number" "Email" "Address" "Amount Number" "Pmt Amount" "Pmt Date"

We can search based on the keyword "OTPViewController" and should look for consecutive thread number "http-nio-8080-exec-117" and extraction of value should start from the keyword and the dot "."

Will appreciate your feedback and time.

Re: How to search and create table report?

somesoni2 — Wed, 05 Apr 2023 20:38:19 GMT

Following Splunk documentation will get you started.

https://docs.splunk.com/Documentation/Splunk/9.0.4/Knowledge/Managesearch-timefieldextractions

Extract all the fields, i.e. your data fields + thread_id (value within first set of square bracket)+ module_name (value within second set of square bracket).

Once you've your fields extracted try something like this to get the report

Your base search | table thread_id module_name "RequestID", "Policy Number", "Email", "Address", "Amount Number", "Pmt Amount", "Pmt Date" | stats values(*) as * by thread_id module_name

Re: How to search and create table report?

richgalloway — Wed, 05 Apr 2023 20:42:21 GMT

This is the Splunk Community, not chatGPT. 😀

Please share the queries you've tried so far and how they've not met your expectations. We'll be happy to correct errors and misconceptions.

Re: How to search and create table report?

runiyal — Tue, 11 Apr 2023 18:27:15 GMT

I tried to run the following query. Although it runs with 20K events but its not generating output in the table.

index=yourinndex "OTPViewController" | rex "(?ms)OTPViewController:(?<session>http-nio-8080-exec-\d+).*?Policy Number\. (?<PolicyNumber>[^\n]+)\n.*?Email\. (?<Email>[^\n]+)\n.*?Address\. (?<Address>[^\n]+)\n.*?Amount Number \. (?<AmountNumber>[^\n]+)\n.*?Pmt Amount \. (?<PmtAmount>[^\n]+)\n" | stats values(*) as * by session | table PolicyNumber, Email, Address, AmountNumber, PmtAmount

Can you tell what can be the issue here?