Suggestions for how to build a SQL query, for non-technical users

PowerShell Help
1

Morning all

So, am writing a front-end querying module for a hardware inventory with a CosmosDB REST backend. However, I’m trying to make it so it’ll be easy for non-technical users to use to query the results.

For those unaware, you can query CosmosDB (basically, a NoSQL Mongo sort of thing, schemaless, with documents posted in JSON format) with a SQL-alike language.

Sample queries:

SELECT f.hostname, c.Name, c.InstalledFROM Computers f
JOIN c IN f.FeaturesAndRoles WHERE f.hostname = ‘HH-HOST-BETA’ and c.Installed and c.Name = ‘Hyper-V’

SELECT f.hostname, c.Name, c.InstalledFROM Computers f
JOIN c IN f.FeaturesAndRolesWHERE c.Installed and c.Name = ‘Hyper-V’

SELECT f.hostname, c.Name, c.InstalledFROM Computers f
JOIN c IN f.FeaturesAndRolesWHERE c.Installed and contains(c.Name, ‘Active’)

Now, traditionally if it were me, I’d provide the likes of a -Query parameter to my Query-Cosmos command, and pass in my own SQL command in there. But, I want my non-technical users instead not to have to do that.

I’d thought of (in the above examples) enabling them to somehow run queries like:

Query-Cosmos -Hostname 'HH-HOST-BETA' -FeaturesIncludes 'Hyper-V' | Select HostName, Name, Installed
Query-Cosmos -Hostname 'HH-HOST-BETA' -FeaturesIncludes 'Hyper-V' -KBInstalled '937576' -SoftwareInstalled "Acrobat"

and having my code do all the heavy lifting of assembling the SQL in the background, all nice and friendly.

But I can’t help but think there’s some other better way that means I don’t have to take care of all the different use cases (I built my database using this script) and added some more classes to it, removed some, added FeaturesAndRoles, such like. So I don’t want to have to try and skin-up a parameter for each and every one of those potential combinations.

A VERY cut-down sample of the data created by that script is linked here.

So, from that sample, you can see the sort of queries I might like to contrive to make could include “Get all servers in <domain> where active directory feature is installed, the spooler is not running and KB925673 is installed”.

Does anyone have any suggestions on the best method to approach to handle this? Like I say, I can write the queries no issue - it’s a best-method to approach the assembling of such things, maybe there’s some technique I’m unaware of. The only two ideas I’ve had are either getting them to make a legible query (and CosmosDB just returns a 400 with no hints as to what’s wrong if it doesn’t like it, so that’s out), or to have a parameter list that will go a long way towards my back-end code then turning that into a legit query. There must be another option I’ve not thought of (or more likely, a simple way to code the second option), I hope?

Appreciate this might be a tall order, but hey - you never know… !

Thanks!

2

Building the query dynamically is just string concatenation, but trying to avoid a 400 is testing and testing and more testing. If there are a ton of options, look at what the most likely queries a user would do and make params and code to build it properly. A free text param can be added, just be aware of SQL Injection attacks, but READ access lowers the risk. That’s another question is permissions, does everyone have READ access? Here is a basic example:

function Invoke-CosmosCmd {
    param (
        [string]$HostName,
        [String]$FeaturesIncludes
    )

$sqlCmd = @"
SELECT f.hostname, 
       c.Name, 
       c.Installed
FROM Computers f
JOIN c IN f.FeaturesAndRoles
WHERE c.Installed 
"@

    if ($PSBoundParameters.ContainsKey('HostName')) {
        $sqlCmd += "rnAND f.HostName = '{0}'" -f $HostName
    }

    if ($PSBoundParameters.ContainsKey('FeaturesIncludes')) {
        $sqlCmd += "rnAND c.Name = '{0}'" -f $FeaturesIncludes
    }


    $sqlCmd
}

Invoke-CosmosCmd -HostName 'HH-HOST-BETA'-FeaturesIncludes 'Hyper-V'
3

Thanks Rob, yeah that’s pretty much what I was suggesting as my fallback position, I was kinda hoping there might be some funky alternative I hadn’t thought of.

Now that I’ve seen your code tho, I don’t think it would be too cumbersome for me to write up the basics in terms of the fields that they use, so it might not be so bad as I’d first thought.

Because this is only for people within my company, and I’d be using the read-only key, SQL injection might not really be something to worry about too much. If I’m going to get into this, I’ll try and do it properly with parameters instead however.

Thanks for the input, much appreciated !

4

A while back, I made a module that sort of does what you’re asking: https://github.com/rohnedwards/DatabaseReporter

To test it out, you could take the DatabaseReporter.ps1 file from the project and create a file called CosmosDb.psm1 that’s in the same folder with these contents:

# Load the DatabaseReporter engine:
. $PSScriptRoot\DatabaseReporter.ps1

$DebugMode = $true

<# If CosmosDB has a .NET library that implements the SqlConnection interface, you can set that up here so the command can return actual objects from your DB. For now, this is a dummy SqlConnection string so the module won't complain. #>
$DBConnectionString = 'Data Source=localhost\SQLEXPRESS;Initial Catalog=AdventureWorks2012;Integrated Security=SSPI;'
$DBConnectionObject = [System.Data.SqlClient.SqlConnection]::new($DBConnectionString)
Set-DbReaderConnection $DBConnectionObject

DbReaderCommand Query-Cosmos {
    [MagicDbInfo(FromClause = "
      FROM Computers f
      JOIN c IN f.FeaturesAndRoles
    ",
    PSTypeName='CdbTest')]
    param(
        [MagicDbProp(ColumnName='f.hostname')]
        [MagicDbFormatTableColumn()]
        [string] $HostName,
        [MagicDbProp(ColumnName='c.Name')]
        [MagicDbFormatTableColumn()]
        [string] $Name,
        [MagicDbProp(ColumnName='c.Installed')]
        [MagicDbFormatTableColumn()]
        [bool] $Installed
    )
}

 

Then to use it, you could do something like this:

PS> Import-Module c:\path\to\CosmosDb.psm1
PS> Query-Cosmos -ReturnSqlQuery -HostName HH-HOST-BETA -Name Hyper-V

SELECT
f.hostname AS HostName,
c.Name AS Name,
c.Installed AS Installed
FROM
Computers f
JOIN c IN f.FeaturesAndRoles
WHERE
((f.hostname LIKE @HostName0)) AND
((c.Name LIKE @Name0))

/*
Parameters:
@HostName0: HH-HOST-BETA
@Name0: Hyper-V
*/

Note that the -ReturnSqlQuery is being used. Without that, the command will fail because it will try to talk directly to a DB with the invalid DbConnection that was set up. If you use the SQL query string directly, then it would need to be slightly tweaked to not use parameterized queries. If CosmosDB has .NET support (specifically if you can add a library that implements a DbConnection for it), and if it supports parameterized queries, it might work without any changes.

Let me know if you can get the test working, and if you’re interested in using it, I can help you make a few tweaks to the DatabaseReporter.ps1 file.