Reporting

Create report with totals (pivot or through search query)

jugarugabi
Path Finder

Hello all, 

So, I am having the following information forwarded to splunk as sourcetype as below (with more than 15000 similar lines):

 

 

2021-Jan-14 09:07 2 servername2 instance1 2021-Jan-14 09:07:25.393 [transaction_string1] 79897 67163 OK 1 [269661] 97 28 OK

 

 

I don't have any kind of header of this text file that is forwarded to splunk but I do know how to create one using the Fields options - that won't be an issue. 

 I need to create a report that has the following specs:

1. Rows:

"Scored" -  a rangemap for the value which is represented in the text file as 97 (after [269661])
range map should be:

  • 0s-to-0.05s=1-50
  • 0.05s-to-0.10s=51-100
  • 0.10s-to-0.15s=101-150
  • 0.15s-to-0.20s=151-200
  • 0.20s-to-0.30s=201-300
  • 0.30s-to-0.50s=301-500
  • 0.50s-to-1s=501-1000
  • 1s-to-2s=1001-2000
  • 2s-to-3s=2001-3000
  • 3s-to-5s=3001-5000
  • 5s-to-30s=5001-30000
  • >30s=30001-99999

2. Columns: 

- All: a sum(count) for each range present - if there are no records for a specific range, then 0 should be shown as a total. 

- servername (alphabetically sorted) with instanceId (there are 2: 1 and 2 for each servername) - each one getting the count value for each range value in "Scored" mentioned above ---- if there are is 0 as count for a specific range on the servername and instanceid, then 0 should be shown for each servername and instanceid. 

Now, by the looks of it, this can be achieved using a pivot. 
So far, this is what I could've come up with: 

pivot.JPG

The output I need should be similar to one below:
neededoutput.JPG

Can anyone help me out on how to build up a search query to actually have the desired output?

Thanks!

Labels (3)
0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!