Reporting

How to create a report with counts per column for one specific field?

ten_yard_fight
Path Finder

I have a search that breaks down what files were accessed, how much data was retrieved and how many total requests for one particular field. Example search query below:

index="my_index" source="access_log" directory="/my_app/" | chart count(status_code) AS Requests, sum(file_size) AS TotalData, values(path_to_file) AS FileRequested BY directory

The above query produces something like:

directory        Requests      TotalData        FileRequested
my_app           10            345677           html/index.html
                                                images/happy_face.png
                                                dynamic/more_happy.py

While this is fine if you only care about the TOTAL number for the entire directory, I would like to break it down so that I can display the number of requests and total data for each of the files requested listed by the directory. The directory should only be displayed once like the example above. Any suggestions? I've tried a couple of different ways but I cant get the directory to only display once and the rest of the rows populated with relevant data per file requested.

0 Karma
1 Solution

lguinn2
Legend

Splunk is not really designed as a "report writer." Depending on what you want to do, you may find it easier to use Splunk for all the computations and then export the results to a .csv file. Then you can take the csv file into any reporting tool to format it.

However, this should work:

index="my_index" source="access_log" directory="/my_app/" 
| stats count(status_code) AS Requests, sum(file_size) AS TotalData BY directory path_to_file
| eval sort_key=directory + " " +  path_to_file
| append [ search index="my_index" source="access_log" directory="/my_app/" 
    | stats count(status_code) AS Requests, sum(file_size) AS TotalData BY directory
    | eval sort_key=directory + "***Total" 
    | eval path_to_file = "Total for directory" ]
| sort 0 sort_key
| fields - sort_key
| rename path_to_file as FileRequested
| table directory FileRequested Requests TotalData 

Note that this will probably take at least twice as much time and resources to run.

View solution in original post

0 Karma

lguinn2
Legend

Splunk is not really designed as a "report writer." Depending on what you want to do, you may find it easier to use Splunk for all the computations and then export the results to a .csv file. Then you can take the csv file into any reporting tool to format it.

However, this should work:

index="my_index" source="access_log" directory="/my_app/" 
| stats count(status_code) AS Requests, sum(file_size) AS TotalData BY directory path_to_file
| eval sort_key=directory + " " +  path_to_file
| append [ search index="my_index" source="access_log" directory="/my_app/" 
    | stats count(status_code) AS Requests, sum(file_size) AS TotalData BY directory
    | eval sort_key=directory + "***Total" 
    | eval path_to_file = "Total for directory" ]
| sort 0 sort_key
| fields - sort_key
| rename path_to_file as FileRequested
| table directory FileRequested Requests TotalData 

Note that this will probably take at least twice as much time and resources to run.

0 Karma

ten_yard_fight
Path Finder

Thanks for your response Iguinn, I took snippets of your example above and got something working.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...