What I'm trying to do is get two columns back from a SQL Server database so that I can check versioning. We have different versions and levels of SQL Server in our organization, and I want to be able to check the year and cumulative update version. I can get one value back and act on that with "its", but the problem is that I want this in a describe.one scenario, so that version/cumulative update combinations can be tested as valid. Examples would be (2016 with CU11) or (2017 with CU20). I have tried a lot of different combinations (and the command resource using SQLCMD), but nothing has worked, so I am posting here what the most logical version (to me) is.
control "CONTROL-06" do
title "Validate SQLSERVER version, service pack, and cumulative update"
describe.one do
sqlserver_version = mssql_session.query("SET NOCOUNT ON; SELECT SERVERPROPERTY('ProductUpdateLevel') as level, @@version as version ").row(0)
describe sqlserver_version do
its('column("version").value') { should match /SQL Server 2016/ }
its('column("level").value') { should cmp "CU11" }
end
describe sqlserver_version do
its('column("version").value') { should match /SQL Server 2017/ }
its('column("level").value') { should cmp "CU20" }
end
#require 'pry'; binding.pry;
end
end
The error I get back is:
[FAIL] CONTROL-06: Validate SQLSERVER version, service pack, and cumulative update (4 failed)
[FAIL] SQL Row column("version").value
undefined method column("version")' for #<DatabaseHelper::SQLRow:0x000000000c4baa80> [FAIL] SQL Row column("level").value undefined method column("level")' for #DatabaseHelper::SQLRow:0x000000000c4baa80
[FAIL] SQL Row column("version").value
undefined method column("version")' for #<DatabaseHelper::SQLRow:0x000000000c4baa80> [FAIL] SQL Row column("level").value undefined method column("level")' for #DatabaseHelper::SQLRow:0x000000000c4baa80
If I uncomment the: require 'pry'; binding.pry; line, then I can get values back for the level and version columns, so I know the query is pulling things back with the column method:
It just seems like the "its" doesn't recognize something. Is there another syntax I should be using to get this to work? I am running Inspec version 4.16.0 from a local windows machine that has SQL Server 2017 installed on it.
describe.one do
describe sqlserver_version do
its('version') { should match /SQL Server 2016/ }
its('level') { should cmp "CU11" }
end
describe sqlserver_version do
its('version') { should match /SQL Server 2017/ }
its('level') { should cmp "CU20" }
end
end
If this approach sounds good, let me know if you need any help with the code for the custom resource.
Thank you for your response! I was able to get it working with a custom resource. The only thing that is weird is if I try to SELECT the @@version first, it whacks all of the other columns. With that column last, everything is coming out ok. Example output when @@version is first in the SELECT:
@row={"full_version"=>"Microsoft SQL Server 2017 (RTM-CU20) (KB4541283) - 14.0.3294.2 (X64) ", "edition"=>nil, "product_level"=>nil, "product_update_level"=>nil}>
Details of working solution:
sqlserver_details.rb for the custom resource in the libraries folder:
class SQLServerDetails < Inspec.resource(1)
name 'sqlserver_details'
def method_missing(name) @params[name.to_s]
end
end
My test.rb inspec file:
control "CONTROL-07" do
title "Validate SQLSERVER version, service pack, and cumulative update"
desc "Using custom resource to check combination of database parameters"
describe.one do
sqlserver_query = mssql_session.query("SET NOCOUNT ON; SELECT SERVERPROPERTY('Edition') as edition, SERVERPROPERTY('ProductLevel') as product_level, SERVERPROPERTY('ProductUpdateLevel') as product_update_level, @@version as full_version ").row(0)
sqlserver_custom = sqlserver_details(sqlserver_query.column("full_version").value,
sqlserver_query.column("edition").value,
sqlserver_query.column("product_level").value,
sqlserver_query.column("product_update_level").value)
describe sqlserver_custom do
its ('full_version') { should match /SQL Server 2017/ }
its ('edition') { should match /Standard/ }
its ('product_level') { should cmp "RTM" }
its ('product_update_level') { should cmp > "CU19" }
end
describe sqlserver_custom do
its ('full_version') { should match /SQL Server 2016/ }
its ('edition') { should match /Developer/ }
its ('product_level') { should cmp >= "SP1" }
its ('product_update_level') { should cmp >= "CU7" }
end
I moved the SQL statement into the resource, but I get this error:
[SKIP] undefined method `' for nil:NilClass
when I try to run it. I tried a bunch of different iterations. Do I need to do something to get it to register the mssql_session resource? Just guessing because the error is too generic for me to know what is wrong.
While I was unable to get the SQL statement to work in the resource, I was able to get the following to work using a string array instead of using a custom resource. I can't find anything like this documented. Is there any reason to think that this wouldn't work in future versions of Inspec?
control "CONTROL-15" do
title "Validate SQLSERVER version, service pack, and cumulative update"
desc " Validate SQLSERVER version, service pack, and cumulative update "
qry = mssql_session.query("SET NOCOUNT ON; SELECT SERVERPROPERTY('Edition') as edition, SERVERPROPERTY('ProductLevel') as product_level, SERVERPROPERTY('ProductUpdateLevel') as product_update_level, @@version as full_version ").row(0)
fv = qry.column("full_version").value
ed = qry.column("edition").value
pl = qry.column("product_level").value
pul = qry.column("product_update_level").value
str = fv + "|" + ed + "|" + pl + "|" + pul
strArray = str .split("|")
describe.one do
describe (strArray) do
its ([0]) { should match /SQL Server 2017/ }
its ([1]) { should match /Developer|Standard/ }
its ([2]) { should cmp >= "RTM" }
its ([3]) { should cmp >= "CU19" }
end
describe (strArray) do
its ([0]) { should match /SQL Server 2016/ }
its ([1]) { should match /Developer|Standard/ }
its ([2]) { should cmp >= "SP1" }
its ([3]) { should cmp >= "CU7" }
end
end
end