mssql_session - "its" not working for more than one column

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:

[1] pry(#<#Class:0x000000000c4a9690>)> sqlserver_version
=> #<DatabaseHelper::SQLRow:0x000000000c4baa80 @query=SQL ResultSet, @row={"level"=>"CU20", "version"=>"Microsoft SQL Server 2017 (RTM-CU20) (KB4541283) - 14.0.3294.2 (X64) "}>
[2] pry(#<#Class:0x000000000c4a9690>)> sqlserver_version.column("level").value
=> "CU20"
[3] pry(#<#Class:0x000000000c4a9690>)> sqlserver_version.column("version").value
=> "Microsoft SQL Server 2017 (RTM-CU20) (KB4541283) - 14.0.3294.2 (X64) "

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.

Hello @jeyoung -

Welcome to discourse!

InSpec doesn't support passing arguments to the property methods. So it's probably the ("version") that isn't working.

You may need to move the SQL code into a custom resource (see: https://www.inspec.io/docs/reference/dsl_resource/ ) that provides version and level properties

Then your test would be

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:

  1. sqlserver_details.rb for the custom resource in the libraries folder:

class SQLServerDetails < Inspec.resource(1)
name 'sqlserver_details'

def initialize (full_version, edition, product_level, product_update_level)
@params = {}
@params['full_version'] = full_version
@params['edition'] = edition
@params['product_level'] = product_level
@params['product_update_level'] = product_update_level
end

def method_missing(name)
@params[name.to_s]
end
end

  1. 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

end
end

I would move the SQL statement into the resource. e.g.

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