Export to Excel

Hey

Notice
I'm fully aware that this is not the first mention of this feature. It was discussed here. BUT it seems OP is satisfied with the outcome. (Not sure if digging up his topic is the way to go, but if so, I'll do that.)

For processing huge output, especially if you know what you're looking for - that surely is the case. I often do that. BUT....

Problem
I occasionally monitor network, for something that is not allowed in my network. Often it's just browsing for new or weird-looking domains, alternatively i look at some specific users who already hit regex block, because there's a chance just before/after that hit they've done something else that has not been blocked. (yet :slight_smile: )

Proposed solution
Filtering on webui is good-enough in most cases, and for those exceptional times I wouldn't want to ask for super-extra filtering options - instead. Quick'n'easy export to Excel button.

Up there I could quickly filter out everything that is allowed (gmail, facebook, amazon, CDNs, whatever) and just see what else is there. Actually I do this already, just manually pasting content of webui to excel, problem starts when I wait 2min for results to load :smiley:

Simple soluton would be to add export-to-excel button, that would create xlsx file with everything that has been found in specific date range to simple file with top row frozen and filters added automatically.

Here's example of propsed UI look

And example of excel file

Thx in advance.

I wouldn't consider Excel a good tool for doing this. Database-oriented software seems to be a lot better suited for this kind of task and, in fact, they'll likely already come with an interconnect to the database Pi-hole uses in the background (SQLite).

However, when considering this kind of export, we'd need to know how to write such xlsx files you are asking for. It seems to be a pretty complicated proprietary format so it might be easier to go to some standardized file format like OpenDocument Spreadsheet (ods) assuming Excel could read this, too. Then we'd need to find out how to handle enums and how to properly format the timestamps, etc.

Thanks for answering my request.

You are correct, and this (database oriented soft) would be a long-term goal for it. But I can't do this myself, not yet at least.

As for format - xlsx was the "obvious" proposition, if it would be better/easier for you to go with ods - I'm fine with that too :slight_smile: After all, once I have it in FILE then I can throw it in OpenOffice, Excel, Google Docs or anything else that comes to mind.

To sum it up - at this point, ANY solution and format would be better than manual copy-paste.

pihole-FTL sqlite3 /etc/pihole/pihole-FTL.db -header -csv "Select * from queries where timestamp between strftime('%s','now','-1 day') and strftime ('%s','now');" >export.csv

This will output the database into export.csv which you can easily import in excel. Adjust the time as needed Date And Time Functions

7 Likes