Dynamic SQL Query for the things that arent SMO

[SQLDBAWithABeard]

How can we make one SQL query to gather information that we are not getting from SMO when the information that we need is dynamic dependant on the tags chosen (checks being run)?

Think memory dumps

[SQLDBAWithABeard]

@ClaudioESSilva @shaneis any ideas here ?

[ClaudioESSilva]

Our checks, most of the time, just return a scalar value, right? It's an aggregated value like MAX, MIN, COUNT, etc.

I think we can create a SELECT statement that gathers the result of each check on its (derived) column.

Depending on the Tag used we add or not another derived column.

Example:

switch ($tags) {
    'MemoryDump' {
        $query += ",(SELECT COUNT(1) FROM sys.dm_server_memory_dumps WHERE CreationTime > $datetocheckfrom) AS MemoryDump"
    }

    'PublicRolePermission' {
        $query += ",(SELECT Count(*) AS [RowCount]
                        FROM master.sys.server_permissions
                        WHERE (grantee_principal_id = SUSER_SID(N'public') and state_desc LIKE 'GRANT%')
                            AND NOT (state_desc = 'GRANT' and [permission_name] = 'VIEW ANY DATABASE' and class_desc = 'SERVER')
                            AND NOT (state_desc = 'GRANT' and [permission_name] = 'CONNECT' and class_desc = 'ENDPOINT' and major_id = 2)
                            AND NOT (state_desc = 'GRANT' and [permission_name] = 'CONNECT' and class_desc = 'ENDPOINT' and major_id = 3)
                            AND NOT (state_desc = 'GRANT' and [permission_name] = 'CONNECT' and class_desc = 'ENDPOINT' and major_id = 4)
                            AND NOT (state_desc = 'GRANT' and [permission_name] = 'CONNECT' and class_desc = 'ENDPOINT' and major_id = 5)
                        ) AS PublicRolePermission"
}

Then the final select will be something like:

SELECT 'dbachecks' $query

Which expanded will be:

SELECT 'dbachecks'
,(SELECT COUNT(1) FROM sys.dm_server_memory_dumps WHERE creation_time > '2022-01-01') AS MemoryDump
,(SELECT Count(*) AS [RowCount]
    FROM master.sys.server_permissions
    WHERE (grantee_principal_id = SUSER_SID(N'public') and state_desc LIKE 'GRANT%')
        AND NOT (state_desc = 'GRANT' and [permission_name] = 'VIEW ANY DATABASE' and class_desc = 'SERVER')
        AND NOT (state_desc = 'GRANT' and [permission_name] = 'CONNECT' and class_desc = 'ENDPOINT' and major_id = 2)
        AND NOT (state_desc = 'GRANT' and [permission_name] = 'CONNECT' and class_desc = 'ENDPOINT' and major_id = 3)
        AND NOT (state_desc = 'GRANT' and [permission_name] = 'CONNECT' and class_desc = 'ENDPOINT' and major_id = 4)
        AND NOT (state_desc = 'GRANT' and [permission_name] = 'CONNECT' and class_desc = 'ENDPOINT' and major_id = 5)
    ) AS PublicRolePermission

Which can produce something like:

And then we can read this record set and convert it to a [PSCustomObject] and carry on.

How does it look like? Concerns? Doubts? Suggestions?

[shaneis]

Yeah, that works for me.

Will we have to check for the existence of properties in the [PSCustomObject]? Or do we go down the road of "if we're doing this test, we'll have the property"?

Not an issue either way, I suppose :shrug:

[SQLDBAWithABeard]

I think that will work

[SQLDBAWithABeard]

Yes maybe when we convert into object we validate that e have the properties?

Or is that over kill/too much/ not necessary?

[ClaudioESSilva]

As far as I know, in case of any kind of error on the sql side (syntax, not existing object, etc) the whole query will fail so we won't have a recordset.

Maybe checking if we have 1 record is enough. Then we can go down the road.

[SQLDBAWithABeard]

that doesnt sound so good. Unless the reason that the query fails is that hte instance is not contactable

[ClaudioESSilva]

Agree. But, assuming that we are using the T-SQL queries that dbatools does, that should cover us from things like 'this dmv only appeared on SQL XXXX' and therefore return errors. Regarding not contactable, sure, but we can confirm that is the current error.

[shaneis]

Oh yeah, I was more thinking do we want to be very defensive and go "yes, I know we're in this code block but we should double-check if the returned object has the MemoryDump property" Or do we go "Well, we're in this code block so we'd have to have the MemoryDump property"

Either way, I think Claudio has the best option