MSSQL test for Inspec 3.x

Chef InSpec
1

Previously this was thought to be an issue with 'stdout'. Updated....

Since updating our Jenkins/build servers to Inspec 3.0.52 we are seeing a problem with mssql control.

control "cookbook_name-database_exist?" do
impact 1.0
title 'Check if the specified databases exist.'

database_list.each do |db, _params|
next if db.nil?

describe mssql_session.query("(SELECT name FROM master.sys.databases WHERE name = N'#{db}');") do
  its('stdout') { should match(/#{db}/) }
end

end
end

Error is:

[FAIL] cookbook_name-database_exist?: Check if the specified databases exist.
[FAIL] SQL ResultSet stdout
undefined method `stdout' for SQL ResultSet:DatabaseHelper::SQLQueryResult

I read Inspec 3.0 resource docs for mssql and changed to:

describe mssql_session.query("(SELECT name FROM master.sys.databases WHERE name = N'#{db}') as result").row(0).column('result') do
  its('value') { should match(/#{db}/) }
end

But this did not work either.

Can anyone please direct me at docs to understand the msql resource and how its results are structured?

Thanks in advanced for your help.

2

So I worked out that this it returns a "table" of results from the query that can be referenced by row and column.

describe mssql_session.query('SELECT name from master.sys.databases where name='mydb').row(0).column'name')
its('value') { should match /mydb/ }
end

Neat.