topic Re: Large Report Export in CSV, Slow Dashboard Panels using Accelerated Report reference in Reporting

Large Report Export in CSV, Slow Dashboard Panels using Accelerated Report reference

mbasharat — Sun, 05 May 2019 14:57:48 GMT

I have a large report that returns data anywhere between 4GB-6GB in a nice tabular format. Report has everything what I need. This report is actually for hosts vulnerabilities. Each host is affected with several vulnerabilities so my report returns about a million rows.

However, if I try to Export it in CSV, it errors out because it is too large. If I try to go to /dispatch//results.csv.gz, I am able to copy it to my local BUT that CSV does no open in Excel because it is too large or something else is not right. WHen I try to use this reports reference in my dashboard panels, they are very slow but return results, just need to wait longer.

Is there a way I can make above happen?
Is there a way I can speed up the reference in dashboard and have dashboard panels load much quicker?
Is there a way I can create an accelerated data model from this report OR accelerated data model wont help since my report is already accelerated?
Can I accelerate dashboard panel?

Re: Large Report Export in CSV, Slow Dashboard Panels using Accelerated Report reference

tom_frotscher — Mon, 06 May 2019 07:48:58 GMT

Hi,

if you want to export it to your local machine and you can not open it in excel or notepad: This is why we initially have tools like splunk, it is to much data for those tools. However, you can still use the outputcsv command and use the option singlefile=false (your search | outputcsv singlefile=false filename=results.csv). Instead of one big csv, you should get multiple smaller files, maybe excel can handle them.

In case you want to use the results of your report in splunk:
There are multiple options to gain speed ups. You could make your report a scheduled report (if you do not already have), and reuse the results of the reports in a dashboard. Also you can accelerate your report or use summary indexing. If you want, you can also create a data model for your report data and use it to speed up searches.

In your case i would start to look into scheduled reports, report acceleration and summary indexing.

Greetings

Tom

Re: Large Report Export in CSV, Slow Dashboard Panels using Accelerated Report reference

mbasharat — Mon, 06 May 2019 20:09:13 GMT

Hi Tom,

My reports are all scheduled and accelerated.

Can a data model be created from a report?

Re: Large Report Export in CSV, Slow Dashboard Panels using Accelerated Report reference

woodcock — Mon, 06 May 2019 22:37:50 GMT

It could be that multivalued fields are borking things. Try adding this to the end of your SPL:

foreach * [nomv "<<FIELD>>"]

Re: Large Report Export in CSV, Slow Dashboard Panels using Accelerated Report reference

mbasharat — Mon, 06 May 2019 23:12:23 GMT

Hi Woodcock,

I am using stats dc(fieldA)....values(FieldB) ..... BY FieldC. None of the fields have multiple values.

Re: Large Report Export in CSV, Slow Dashboard Panels using Accelerated Report reference

tom_frotscher — Tue, 07 May 2019 06:57:23 GMT

Hi,

you can not create a data model from a query and i don't think it will be much faster than the accelerated report. But i do not think, that you want to show 1million rows on your dashboard, this is not very helpful. Is your dashboard rather showing aggregated values? something like number of unique vulnerabilities, number of clients, ...?

If so, you should calculate those accelerated values, store them in a summary index or lookup and use them as a basis for your dashboards.

Greetings

Tom

Re: Large Report Export in CSV, Slow Dashboard Panels using Accelerated Report reference

mbasharat — Wed, 08 May 2019 11:59:30 GMT

Hi Tom,

Report is for stakeholders to download for their needs e.g. grabbing list of vulnerabilities with relevant info as well as hosts so they can use data from CSV for scripting to apply patched etc.. Dashboards are not fed off of this report. I am using a different query for dashboard panels in base and post processing with only actionable information for display.

Thanks,

Re: Large Report Export in CSV, Slow Dashboard Panels using Accelerated Report reference

mbasharat — Mon, 13 May 2019 21:22:15 GMT

Hi Tom,

I will use chunks of CSV using your provided method above and see how that goes. TY!!