d4software / querytree

Data reporting and visualization for your app

QueryTree

QueryTree is an ad-hoc reporting tool that works with any Microsoft SQL Server, PostgreSQL or MySQL database. It allows users to query databases, build reports and schedule those reports to email distribution lists, without needing to write any code.

For more information see the QueryTree website

Features

QueryTree can connect to MySQL and PostgreSQL databases using SSH tunnels, secured with passwords or key files.

Supports customization of the logo image, system name and CSS used within the app.

Can use either Sqlite or Microsoft SQL Server database for it's own user and reports data storage.

Database and SSH passwords are stored in its database in encryped form, using AES encryption. Users may provide their own key file, or let the system generate one on first run. Keys can be shared between mutliple web servers in a load balancing scenario.

Users may choose to build their own plugins to store database/SSH passwords in an external key vault. This is achieved by implementing a .NET interface and registering the class in the appSettings.config file. See Building a password manager for more information.

Running QueryTree

Skip to section:

• Running from Source
• Building Binaries
• Running from Binaries
• Running the Tests
• Running with Docker

Prerequisites

To build binaries or run from source you need the .NET 6.0 installed.

Running from Source

Check you have the prerequisites installed, then follow these steps:

1. Clone this repo into a folder

2. At the command prompt, cd into the folder, then into the "Web" folder.

3. Type:

dotnet run

4. Dotnet should report that the applicaiton is running, e.g.

Now listening on: http://localhost:54182
Application started. Press Ctrl+C to shut down.

Visit the URL shown in your browser. You should see the QueryTree application.

If you would like to run QueryTree with your own local development settings, you can add a Web/usersettings.json file containing a modified copy of appsettings.json. Settings in this file will override appsettings.json. However, this file will be ignored by git.

Building Binaries

To build a release binary from the project root execute:

dotnet publish -c Release ./Web/QueryTree.csproj -o ./dist

This will create a release folder in dist of all the unpacked QueryTree binaries and its dependencies.

Running from Binaries

To run QueryTree on your server you will need to install the ASP.NET Core Runtime 6.0.x. (It is not necessary to install the full .NET SDK, just the runtime.) You can download the installer here.

To verify that you have the .NET runtime installed, open a terminal/cmd window and type

dotnet --version

If the command returns a version number, you're ready to run QueryTree.

Once the dotnet runtime is installed, follow these steps:

1. Download the release and unpack the files into a folder of your choice.

2. Open a terminal/cmd window and cd into the folder containing the unpacked QueryTree release files.

3. At the command prompt, type:

dotnet QueryTree.dll

4. Dotnet should report that the applicaiton is running, e.g.

Now listening on: http://localhost:5000
Application started. Press Ctrl+C to shut down.

Visit the URL shown in your browser. You should see the QueryTree application.

5. For use in production environments, QueryTree should be run behind a reverse proxy such as nginx. For more information on hosting QueryTree using nginx see: https://docs.microsoft.com/en-us/aspnet/core/publishing/linuxproduction

You can also host QueryTree using IIS. For information on running .NET Core apps in IIS see: https://docs.microsoft.com/en-us/aspnet/core/publishing/iis

Running the Tests

To run the automated tests in this project, from the project root folder, type the following:

cd Tests
dotnet test

Running with Docker

See the full Docker guide: docs/docker.md

Getting Started

1. When first run, QueryTree will have no users and no database connections. Visiting app, you will be presented with a login page:

2. Click the "Sign up as a new user" button, and enter your details to create a new account.

3. Having signed in, you won't have any database connections configured. The system will ask you whether you want to set up a connection yourself, or invite another user who might be able to do it for you.

4. Assuming you have a database that you can connect to, select the "+ Connect Database" option. You will see the Create Connection page:

Once all the information is entered, you can check the connection by pressing the "Test Connection" button. If the system reports that the conneciton is working, press "Save".

5. You will be taken to the reports list for this connection, but there won't be any reports yet.

6. Click on "+ Create Report". You will be taken to the defualt report builder

7. All reports start by picking a datbase table to start from. From there the report builder will prompt you to select any related tables that it can join to. For example, in this screenshot, I have selected the "orders" table and QueryTree is prompting me to join the "users" table. QueryTree can see that "orders" has a link to "users" so it offers to join the tables.

For more information on how to help QueryTree automatically join between tables in your database see QueryTree's Auto Join feature

8. Having selected a starting table, and any relevant related tables, click Next. The filter panel will open and you will be prompted to add one or more Filters.

9. Once you are happy with the filters, you have the option to summarize the data that is being shown in the results panel. summerizing the data can mean totaling, averaging, counting or finding the minimum/maximum values, for one or more columns. You can do this for all the data, or for different groups of values. For example, you could find the average value of the orders, for each country.

10. Finally, you have the option of generating a chart from the data in the results panel.

11. Once you are happy with your report, save it by clicking the Save button. You will be returned to the list of reports for this connection.

Other Guides

• Scheduling Reports
• Sharing Individual Reports
• Team Management
• The Advanced Query Builder
• Auto Join
• Customizing QueryTree
• Building a password manager
• Using with Docker
• Configuring Email

License

QueryTree is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

QueryTree is licensed under the LGPLv3 license. See the LICENSE file for more information.

QueryTree is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with QueryTree. If not, see http://www.gnu.org/licenses/.

querytree's Issues

Unable to create Advanced Report

I am able to successfully add a database and create simple reports with the docker image.
However, when I try to create an advanced report and choose a data table, I start to get GET http://localhost:8800/api/cache/.../?startRow=0&rowCount=10 404 (Not Found) repeatedly.

Steps taken:

1. Clicked Create Report
2. Clicked advanced query builder
3. Dragged Data Table into the drop area
4. Error starts to happen repeatedly and I can't perform joins with new data tables because the column names don't show up.

export to Excel mangles dates

I have a report generated from MySQL that shows date and time correctly in the web UI. Underlying column is a MySQL DATETIME. When I export the report to Excel (Office 2016 for OS X), the date is mangled. In the web UI, it is "2018-06-29T23:22:59.173", but in excel it shows "1/6/1900 12:00:00 AM". Other dates are similarly mangled.

This happens with the latest published docker image.

Long requests

Been playing with QueryTree and found a bit of a problem.

When running queries on tables with a lot of fields we may get an error from the server:
"The request filtering module is configured to deny a request where the query string is too long."

Basically the problem is that all fields, are being passed on the query string and with a few other properties like searchable, regex, etc.

This makes the request too long.

When exporting to Excel there is no problem, because it only makes a http://localhost:54181/api/queries/1/export

instead of this request bellow:

http://localhost:54181/api/queries/1/?draw=1&columns[0][data]=0&columns[0][name]=&columns[0][searchable]=true&columns[0][orderable]=false&columns[0][search][value]=&columns[0][search][regex]=false&columns[1][data]=1&columns[1][name]=&columns[1][searchable]=true&columns[1][orderable]=false&columns[1][search][value]=&columns[1][search][regex]=false&columns[2][data]=2&columns[2][name]=&columns[2][searchable]=true&columns[2][orderable]=false&columns[2][search][value]=&columns[2][search][regex]=false&columns[3][data]=3&columns[3][name]=&columns[3][searchable]=true&columns[3][orderable]=false&columns[3][search][value]=&columns[3][search][regex]=false&columns[4][data]=4&columns[4][name]=&columns[4][searchable]=true&columns[4][orderable]=false&columns[4][search][value]=&columns[4][search][regex]=false&columns[5][data]=5&columns[5][name]=&columns[5][searchable]=true&columns[5][orderable]=false&columns[5][search][value]=&columns[5][search][regex]=false&columns[6][data]=6&columns[6][name]=&columns[6][searchable]=true&columns[6][orderable]=false&columns[6][search][value]=&columns[6][search][regex]=false&columns[7][data]=7&columns[7][name]=&columns[7][searchable]=true&columns[7][orderable]=false&columns[7][search][value]=&columns[7][search][regex]=false&columns[8][data]=8&columns[8][name]=&columns[8][searchable]=true&columns[8][orderable]=false&columns[8][search][value]=&columns[8][search][regex]=false&columns[9][data]=9&columns[9][name]=&columns[9][searchable]=true&columns[9][orderable]=false&columns[9][search][value]=&columns[9][search][regex]=false&columns[10][data]=10&columns[10][name]=&columns[10][searchable]=true&columns[10][orderable]=false&columns[10][search][value]=&columns[10][search][regex]=false&columns[11][data]=11&columns[11][name]=&columns[11][searchable]=true&columns[11][orderable]=false&columns[11][search][value]=&columns[11][search][regex]=false&columns[12][data]=12&columns[12][name]=&columns[12][searchable]=true&columns[12][orderable]=false&columns[12][search][value]=&columns[12][search][regex]=false&columns[13][data]=13&columns[13][name]=&columns[13][searchable]=true&columns[13][orderable]=false&columns[13][search][value]=&columns[13][search][regex]=false&columns[14][data]=14&columns[14][name]=&columns[14][searchable]=true&columns[14][orderable]=false&columns[14][search][value]=&columns[14][search][regex]=false&columns[15][data]=15&columns[15][name]=&columns[15][searchable]=true&columns[15][orderable]=false&columns[15][search][value]=&columns[15][search][regex]=false&columns[16][data]=16&columns[16][name]=&columns[16][searchable]=true&columns[16][orderable]=false&columns[16][search][value]=&columns[16][search][regex]=false&columns[17][data]=17&columns[17][name]=&columns[17][searchable]=true&columns[17][orderable]=false&columns[17][search][value]=&columns[17][search][regex]=false&columns[18][data]=18&columns[18][name]=&columns[18][searchable]=true&columns[18][orderable]=false&columns[18][search][value]=&columns[18][search][regex]=false&columns[19][data]=19&columns[19][name]=&columns[19][searchable]=true&columns[19][orderable]=false&columns[19][search][value]=&columns[19][search][regex]=false&columns[20][data]=20&columns[20][name]=&columns[20][searchable]=true&columns[20][orderable]=false&columns[20][search][value]=&columns[20][search][regex]=false&columns[21][data]=21&columns[21][name]=&columns[21][searchable]=true&columns[21][orderable]=false&columns[21][search][value]=&columns[21][search][regex]=false&columns[22][data]=22&columns[22][name]=&columns[22][searchable]=true&columns[22][orderable]=false&columns[22][search][value]=&columns[22][search][regex]=false&columns[23][data]=23&columns[23][name]=&columns[23][searchable]=true&columns[23][orderable]=false&columns[23][search][value]=&columns[23][search][regex]=false&columns[24][data]=24&columns[24][name]=&columns[24][searchable]=true&columns[24][orderable]=false&columns[24][search][value]=&columns[24][search][regex]=false&columns[25][data]=25&columns[25][name]=&columns[25][searchable]=true&columns[25][orderable]=false&columns[25][search][value]=&columns[25][search][regex]=false&columns[26][data]=26&columns[26][name]=&columns[26][searchable]=true&columns[26][orderable]=false&columns[26][search][value]=&columns[26][search][regex]=false&columns[27][data]=27&columns[27][name]=&columns[27][searchable]=true&columns[27][orderable]=false&columns[27][search][value]=&columns[27][search][regex]=false&columns[28][data]=28&columns[28][name]=&columns[28][searchable]=true&columns[28][orderable]=false&columns[28][search][value]=&columns[28][search][regex]=false&columns[29][data]=29&columns[29][name]=&columns[29][searchable]=true&columns[29][orderable]=false&columns[29][search][value]=&columns[29][search][regex]=false&columns[30][data]=30&columns[30][name]=&columns[30][searchable]=true&columns[30][orderable]=false&columns[30][search][value]=&columns[30][search][regex]=false&columns[31][data]=31&columns[31][name]=&columns[31][searchable]=true&columns[31][orderable]=false&columns[31][search][value]=&columns[31][search][regex]=false&columns[32][data]=32&columns[32][name]=&columns[32][searchable]=true&columns[32][orderable]=false&columns[32][search][value]=&columns[32][search][regex]=false&columns[33][data]=33&columns[33][name]=&columns[33][searchable]=true&columns[33][orderable]=false&columns[33][search][value]=&columns[33][search][regex]=false&columns[34][data]=34&columns[34][name]=&columns[34][searchable]=true&columns[34][orderable]=false&columns[34][search][value]=&columns[34][search][regex]=false&columns[35][data]=35&columns[35][name]=&columns[35][searchable]=true&columns[35][orderable]=false&columns[35][search][value]=&columns[35][search][regex]=false&columns[36][data]=36&columns[36][name]=&columns[36][searchable]=true&columns[36][orderable]=false&columns[36][search][value]=&columns[36][search][regex]=false&columns[37][data]=37&columns[37][name]=&columns[37][searchable]=true&columns[37][orderable]=false&columns[37][search][value]=&columns[37][search][regex]=false&columns[38][data]=38&columns[38][name]=&columns[38][searchable]=true&columns[38][orderable]=false&columns[38][search][value]=&columns[38][search][regex]=false&columns[39][data]=39&columns[39][name]=&columns[39][searchable]=true&columns[39][orderable]=false&columns[39][search][value]=&columns[39][search][regex]=false&columns[40][data]=40&columns[40][name]=&columns[40][searchable]=true&columns[40][orderable]=false&columns[40][search][value]=&columns[40][search][regex]=false&columns[41][data]=41&columns[41][name]=&columns[41][searchable]=true&columns[41][orderable]=false&columns[41][search][value]=&columns[41][search][regex]=false&columns[42][data]=42&columns[42][name]=&columns[42][searchable]=true&columns[42][orderable]=false&columns[42][search][value]=&columns[42][search][regex]=false&columns[43][data]=43&columns[43][name]=&columns[43][searchable]=true&columns[43][orderable]=false&columns[43][search][value]=&columns[43][search][regex]=false&columns[44][data]=44&columns[44][name]=&columns[44][searchable]=true&columns[44][orderable]=false&columns[44][search][value]=&columns[44][search][regex]=false&columns[45][data]=45&columns[45][name]=&columns[45][searchable]=true&columns[45][orderable]=false&columns[45][search][value]=&columns[45][search][regex]=false&columns[46][data]=46&columns[46][name]=&columns[46][searchable]=true&columns[46][orderable]=false&columns[46][search][value]=&columns[46][search][regex]=false&columns[47][data]=47&columns[47][name]=&columns[47][searchable]=true&columns[47][orderable]=false&columns[47][search][value]=&columns[47][search][regex]=false&columns[48][data]=48&columns[48][name]=&columns[48][searchable]=true&columns[48][orderable]=false&columns[48][search][value]=&columns[48][search][regex]=false&columns[49][data]=49&columns[49][name]=&columns[49][searchable]=true&columns[49][orderable]=false&columns[49][search][value]=&columns[49][search][regex]=false&columns[50][data]=50&columns[50][name]=&columns[50][searchable]=true&columns[50][orderable]=false&columns[50][search][value]=&columns[50][search][regex]=false&columns[51][data]=51&columns[51][name]=&columns[51][searchable]=true&columns[51][orderable]=false&columns[51][search][value]=&columns[51][search][regex]=false&columns[52][data]=52&columns[52][name]=&columns[52][searchable]=true&columns[52][orderable]=false&columns[52][search][value]=&columns[52][search][regex]=false&columns[53][data]=53&columns[53][name]=&columns[53][searchable]=true&columns[53][orderable]=false&columns[53][search][value]=&columns[53][search][regex]=false&columns[54][data]=54&columns[54][name]=&columns[54][searchable]=true&columns[54][orderable]=false&columns[54][search][value]=&columns[54][search][regex]=false&columns[55][data]=55&columns[55][name]=&columns[55][searchable]=true&columns[55][orderable]=false&columns[55][search][value]=&columns[55][search][regex]=false&columns[56][data]=56&columns[56][name]=&columns[56][searchable]=true&columns[56][orderable]=false&columns[56][search][value]=&columns[56][search][regex]=false&columns[57][data]=57&columns[57][name]=&columns[57][searchable]=true&columns[57][orderable]=false&columns[57][search][value]=&columns[57][search][regex]=false&columns[58][data]=58&columns[58][name]=&columns[58][searchable]=true&columns[58][orderable]=false&columns[58][search][value]=&columns[58][search][regex]=false&columns[59][data]=59&columns[59][name]=&columns[59][searchable]=true&columns[59][orderable]=false&columns[59][search][value]=&columns[59][search][regex]=false&columns[60][data]=60&columns[60][name]=&columns[60][searchable]=true&columns[60][orderable]=false&columns[60][search][value]=&columns[60][search][regex]=false&columns[61][data]=61&columns[61][name]=&columns[61][searchable]=true&columns[61][orderable]=false&columns[61][search][value]=&columns[61][search][regex]=false&start=0&length=10&search[value]=&search[regex]=false&_=1552047877908

Unable to add database

Test Database connection is not working. when I entered the mysql details and click on test connection nothing happen.

thanks you

How can I export the report as PDF?

Is there a way to export the generated report as PDF as well just like export of excel operation is present

Use dotless instead of npm less

Might be good to keep everything Csharpy and use dotless as a nuget dependency instead of reaching out to the node world?

Noticed a few people being caught out by that.

It claims to be compatible with .NET Standard 2.0 (.NET Core 2.0, Mono 5.4, .NET Framework 4.6.1+)

https://github.com/dotless/dotless

not displaying tables

Hi ,

I have got success while connecting to the database, but while generating reports , it continuously shows connecting to your database. please help to fix this

i have checked the error logs of container..

at Microsoft.AspNetCore.Antiforgery.Internal.DefaultAntiforgery.GetCookieTokenDoesNotThrow(HttpContext httpContext)
fail: Microsoft.AspNetCore.Antiforgery.Internal.DefaultAntiforgery[7]
An exception was thrown while deserializing the token.
System.InvalidOperationException: The antiforgery token could not be decrypted. ---> System.Security.Cryptography.CryptographicException: The key {d577aca6-67c1-40a5-b8fe-0f1913d79389} was not found in the key ring.
at Microsoft.AspNetCore.DataProtection.KeyManagement.KeyRingBasedDataProtector.UnprotectCore(Byte[] protectedData, Boolean allowOperationsOnRevokedKeys, UnprotectStatus& status)
at Microsoft.AspNetCore.DataProtection.KeyManagement.KeyRingBasedDataProtector.DangerousUnprotect(Byte[] protectedData, Boolean ignoreRevocationErrors, Boolean& requiresMigration, Boolean& wasRevoked)
at Microsoft.AspNetCore.DataProtection.KeyManagement.KeyRingBasedDataProtector.Unprotect(Byte[] protectedData)
at Microsoft.AspNetCore.Antiforgery.Internal.DefaultAntiforgeryTokenSerializer.Deserialize(String serializedToken)
--- End of inner exception stack trace ---
at Microsoft.AspNetCore.Antiforgery.Internal.DefaultAntiforgeryTokenSerializer.Deserialize(String serializedToken)
at Microsoft.AspNetCore.Antiforgery.Internal.DefaultAntiforgery.GetCookieTokenDoesNotThrow(HttpContext httpContext)

Summarize does not allow MAX of dates

Summary:
I need to create a subselect with MAX(initialDate) GROUP BY id but it was not possible.

Error:
All date fields from my table were excluded from the list of columns in the Summarize tool.

How to Reproduce:

1. Create a DataSource pointing to your table. Your table must have at least one DATE column.
2. Create a Summarize tool from your datasource.
3. In Calculate the, choose Maximum.
4. In of column, look for your date field.

Unable to build project on .Net Core 2.1 SDK

Hi,

Unable to build the project with DotNet Core 2.1 SDK. Keeps returning

"error : A connection attempt failed because the connected party did not properly respond after a long period of time, or established connection failed because connected host has failed to respond [D:\QueryTree-master\Web\QueryTree.csproj].

Please explain the issue and provide a fix.

Thanks.

Scheduled report mailed 8 times

Hi there.

Just found one more thing. I wish i knew .Net so I could send you PRs.

I scheduled a report to be mailed daily at 10AM. At 9:33AM I received 8 (eight) emails with the very same report.

Let me know if you need any additional information or tests.

Running from Source issue - exited with code 9009

Hi...
i have got an error while Running from Source...
i installed .net core 2.0 and 2.1 and 2.2 separately but below error occurred.

...\Web>dotnet run
error MSB3073: The command "lessc --source-map=Styles/aqb/app.css.map Styles/aqb/app.less Styles/aqb/app.css" exited with code 9009

Localization

Hi,

as far as i can see, we can't localize QueryTree at the moment, right? Are there any future plans to enable internationalization options?

Kind regards

[Feature Request] Set home URL

Hello. Would it be possible to add some kind of customization of the home URL likewise Wordpress?
Reason: I am trying to redirect the querytree docker container to mydomain.com/mydirectory but all static files point to root (/), which returns 404.

Improve ```dotnet publish``` step

Based on doc from MS starting from .NET Core 2.0 dotnet publish command already include restore option. This also can be checked via publish command. So you can skip restore step just by specifing --no-restore option.
Second point related to omitting targeting framework(netcoreapp2.1) option when publish app due to already specified value inside *.csproj file.

Unable to connect to MySQL over SSH

Operating system:
Ubuntu 17.10

Docker version:

Client:
 Version:	18.03.0-ce
 API version:	1.37
 Go version:	go1.9.4
 Git commit:	0520e24
 Built:	Wed Mar 21 23:10:09 2018
 OS/Arch:	linux/amd64
 Experimental:	false
 Orchestrator:	swarm

Server:
 Engine:
  Version:	18.03.0-ce
  API version:	1.37 (minimum version 1.12)
  Go version:	go1.9.4
  Git commit:	0520e24
  Built:	Wed Mar 21 23:08:36 2018
  OS/Arch:	linux/amd64
  Experimental:	false

Steps to reproduce:

• ran command docker run -p 8080:80 --name querytree -d d4software/querytree:latest
• observe the container is running properly:

docker container ls
CONTAINER ID        IMAGE                         COMMAND                  CREATED             STATUS              PORTS                      NAMES
0403aa010a58        d4software/querytree:latest   "/bin/sh -c 'dotnet …"   42 seconds ago      Up 41 seconds       0.0.0.0:8080->80/tcp       querytree

• go to web http://localhost:8080/
• create account and login
• create a connection to the MySQL server as shown below (successfully tested):
  
• create a report

What happens:

No tables are fetched from this connection, although tables exist and credentials are right (copy pasted them from our staging environment application configure file)
See below:

Any ideas how can I see some verbose logs on what goes wrong?

Am I doing something wrong?

Readme: Add a section on creating Release builds

met this issue

error MSB3073: The command "lessc --source-map=Styles/aqb/app.css.map Styles/aqb/app.less Styles/aqb/app.css" exited with code 127.

when run dotnet run

Where is the Advanced Report Builder?

I don't know if I am doing something wrong, but I built a container with the docker image and just can't find the Advanced Report Builder. Is it available?

Advanced Query Builder not returning columns

I am using docker tag 7e0bac0. I am able to query my tables fine with the simple query builder form. But whenever I try to use the advanced query builder and I pick a data table I see the following endpoint returns a http status of 200 but I get the below response:

api/cache/25ea74e94b1f463e89fbed92d695a88b/dd81a7de-ee10-44ca-a7a4-4ea6a2186643/?startRow=0&rowCount=10

{"status":"error","errorText":"42601: syntax error at or near \",\"","columns":[],"columnTypes":[],"rows":[],"rowCount":0,"query":null}

Not Able to Access Querytree

Hi,

I'm receiving below error. Please help me to fix this issue.

oracle db support will be great!

Hi,

thanks for this awesome apps, is there any plan for oracle or odbc datasource support?

Queries to a Stored Procedure

When a connection is done to SQL Server, it would be nice to list not only tables but also Stored Procedures.

Document how to use enviroment variables to override appsettings.json settings

Based on d4software.freshdesk.com/a/tickets/1006

Add better documentation about how the enviroment variables can be used in docker

some unknown option shows up

I was trying to set up the filter
and then it comes out

May I know how to fix this?

Unable to see values in drop downs on the filter section

I am able to connect to my postgres DB and see all the tables listed in the select your table section. When I select a table and click next in that section I see the following response in my devtools when hitting this URL:
http://localhost:8081/api/cache/a88ac14f06eb4512b7287a0501f2423a/0a1c9d5d-d9a7-4d02-8332-75cba76bb08f/?startRow=0&rowCount=10

{"status":"error","errorText":"42703: column \"column_0\" does not exist","columns":[],"columnTypes":[],"rows":[],"rowCount":0,"query":null}

When I click on the filter section I see the drop downs but there are no values in those.

Any suggestions on what could be causing this?

Unable to access from a path different from "/"

I was trying to host QueryTree on my domain example.com/reports however the assets are statically mapped to / and I don't find a config inside appsettings.json to set this desired behavior.

Could we draw together a feature for guiding a Pull Request that could implement this?

Thanks for your time reviewing this. :-)

Filet & Sum (Aggregate) - to reapply on same tables

Sum / aggregate / Avg functions would be nice

How to run in ubuntu?

HELP ME .!!

Column Names Not Showing in Summarize Drop Down Menu

The drop down to select columns is empty when I use Summarize option. The column are visible in Filter option. I am using MySQL as a backend table.

I appreciate if you can help me with this issue.

Query across multiple databases

Hi,

I was reviewing the documentation but couldn't identify if this tool is able to query across multiple databases on different servers for a single report.

In my case the databases are mysql and I didn't want to go the federated route so if this is possible then this tool would be perfect!

Where are all the docs?

Looking in the Docs folder see a bunch of .md files, but when clicking on them seeing nothing. Do see a change log that says docs moved from wiki to repo. But where are they?

Filter OR

There is a way to add a filter OR ?

Thanks,

Add the ability to do DISTINCT ON queries

Our database is a temporal design because we want to maintain history. So we never update a row and always just insert another row. But at times we want to bring back the most recent record of a given item. We have a digest key that is a hash of a logical key for an item. We then can query by this digest key and say DISTINCT ON digest_key ordered by record_created_on DESC. This gives us the most recent record for that given digest_key.

Table with Temporal Example:

item_id | digest_key | state_code | item_name | price | record_created_on |
1 | 123 | IL | foobar | 12.00 | 09-20-2018 13:45 |
2 | 123 | IL | foobar | 14.00 | 08-20-2018 13:45 |
3 | 123 | IL | foobar | 13.00 | 07-20-2018 13:45 |
4 | 456 | FL | foobar | 16.00 | 09-20-2018 13:45 |
5 | 456 | FL | foobar | 14.00 | 08-20-2018 13:45 |
6 | 456 | FL | foobar | 15.00 | 07-20-2018 13:45 |

When I run the query to get the latest records for each item it would return the following results:

item_id | digest_key | state_code | item_name | price | record_created_on |
1 | 123 | IL | foobar | 12.00 | 09-20-2018 13:45 |
4 | 456 | FL | foobar | 16.00 | 09-20-2018 13:45 |

Maybe there is a way to obtain this result with the current advanced query builder but if there is I would appreciate some instructions on how to get this result. 😄 Otherwise I think this would be a nice feature to add to QueryTree to allow users to do DISTINCT ON type logic.

This is a great tool so far! Keep up the great work!

Add the option to use email without authentication

Some SMTP servers are configured to not use authentication. My idea is to add a flag to appSettings.json and modify MessageServices.cs

Can't quite filter on postgresql int types

Excel Data Source

Handling Excel as a data source

Add SmtpHostPort as a confurable property

QueryTree/Web/Services/MessageServices.cs

Line 34 in c54fc65

client.Connect(_config.GetValue<string>("Email:SmtpHost"), 587, false);

Google apps for example prefers 465 for SSL, port 587 flags up as insecure and doesn't allow you to send mail

Update https://github.com/d4software/QueryTree/blob/master/docs/mail.md with new field to get gmail working

/docs folder vs GH wiki

The GitHub wiki can be a bit hard to navigate and we don't have full controll over how things work.

What are your thoughts on publishing the docs as markdown files in the repo under a /docs folder?

See https://github.com/PowerShell/PowerShell as an example. It means docs can also be read offline when people clone the project and makes it easy for potential contributors to quickly add things or document things.

We can later even publish these to a jekyll site like docs.querytreeapp.com or something.

I bring this up because I was about to start writing some docs/instructions on how to run querytree in a docker container and access data from DBs inside a container network but the wiki is a bit eh and I've not really seen many OSS projects utilise it very well.

"Summarize" block isn't loaded while editing existing report

If one tries to edit an existing report in "simple" query editor, the "summarize" block in the editor isn't loaded. This happens because of a wrong constant in simple.js.

trying to query greenplum DB using the postgres connector not working

Hello
I have an instance of GreenPlum DB, it is based on postgres 8.3x,
I tried to connect QueryTree but i have no luck, testing the connection looks good but when I try to create a new report, QueryTree does not show any table in the "Select your Tables" panel

Could you kindly help me with this?

Thanks heap!
Best regards
Mark

Keyfile does not persist in volume

querytree.ico missing from footer

Should probbaly also convert this to png while at it

Editing an existing report

Create a new report with no filters connecting to SQL server.

View report works fine.

Edit report, click "Filter your data" then "Add Filter"

The "Value" box is missing making adding new filters impossible.

Making it easier to deploy QueryTree

Hola,

So I started this branch ages ago but thought it'd be good to run the idea past.

With the success and popularity of the docker image being so easy for people to try QT out with. I thought why not make it easy to spin up a live version of QT in azure of heroku too.

https://www.microsoft.com/developerblog/2017/01/17/the-deploy-to-azure-button/

The limiting factor for this though would be to add support for a proper DB instead of sqlite so that QT can run in the cloud and not rely on a filesystem.

So if someone wants to take up the challenge of substituting sqlite for psql or mysql.

A half attempt: https://github.com/pandelisz/querytree

Build & Update nuget fails

updating entity core, design and identity all fail

Severity Code Description Project File Line Suppression State
Error MSB3073 The command "lessc --source-map=Styles/aqb/app.css.map Styles/aqb/app.less Styles/aqb/app.css" exited with code 9009.

QueryTree C:\QueryTree\Web\QueryTree.csproj 74

Error NU1605 Detected package downgrade: Microsoft.Extensions.Caching.Memory from 2.1.1 to 2.0.0. Reference the package directly from the project to select a different version.
QueryTree -> Microsoft.EntityFrameworkCore.Design 2.1.4 -> Microsoft.EntityFrameworkCore.Relational 2.1.4 -> Microsoft.EntityFrameworkCore 2.1.4 -> Microsoft.Extensions.Caching.Memory (>= 2.1.1)
QueryTree -> Microsoft.Extensions.Caching.Memory (>= 2.0.0) QueryTree C:\QueryTree\Web\QueryTree.csproj 1
Error NU1202 Package Microsoft.AspNetCore.All 2.1.5 is not compatible with netcoreapp2.0 (.NETCoreApp,Version=v2.0). Package Microsoft.AspNetCore.All 2.1.5 supports: netcoreapp2.1 (.NETCoreApp,Version=v2.1) QueryTree

C:\QueryTree\Web\QueryTree.csproj 1
Error Package restore failed. Rolling back package changes for 'QueryTree'.

Can't filter data

Missing Link to Advanced Query Builder

Hi there

I followed the steps to clone the project and run QueryTree with dotnet run.

After connecting to the db and signing up. When i click "+" to create a report. I am unable to find the advanced query builder link as shown in the example. The image attached is what I see on my screen:

Is there a solution to it?

Thanks

Invitation not working

Hello.
I already setup SMTP to the docker image but I can't say if it's working.
The main issue here is: whenever I try to invite someone to my team, the screen goes full blank without any error notice (nor on screen, nor on js console).

No option to generate chart

I connected to the database and successfully created a simple report of int x string

units_in_stock | company_name
4 | Tokyo Traders
5 | Lyngbysild
6 | Specialty Biscuits, Ltd.

From the README:
"Finally, you have the option of generating a chart from the data in the results panel."

I can not see this option anywhere on either the View Report or Edit Report screens and there are no errors in the log.

centos.7-x64
.NET Core SDKs installed:
2.1.300 [/usr/share/dotnet/sdk]
.NET Core runtimes installed:
Microsoft.AspNetCore.All 2.1.0 [/usr/share/dotnet/shared/Microsoft.AspNetCore.All]
Microsoft.AspNetCore.App 2.1.0 [/usr/share/dotnet/shared/Microsoft.AspNetCore.App]
Microsoft.NETCore.App 2.1.0 [/usr/share/dotnet/shared/Microsoft.NETCore.App]
Postgres 9.4

Unable to build from source with .Net Core 2.2.105 SDK

Errror given is:

C:\QueryTree Server\Web\QueryTree.csproj(73,5): error MSB3073: Der Befehl "lessc --source-map=Styles/aqb/app.css.map Styles/aqb/app.less Styles/aqb/app.css" wurde mit dem Code 9009 beendet.

Any suggestions?