I can't be the only one who's ever tried to do this....

Re:I can't be the only one who's ever tried to do this.... (skissinger)

Thu, 31 May 2012 15:32:44 GMT

don't use MSI. Use a script. This script:
on error resume next
strcomputer = "."
set objwmi = GetObject("winmgmts:\\" & strComputer & "\root\microsoft\sqlserver\computermanagement10")
set colItems = objwmi.execQuery("Select * from sqlserviceadvancedproperty")
for each item in ColItems
if lcase(item.propertyname) = "version" then
   strvalue1 = lcase(item.propertystrvalue)
   if (left(strvalue1,4) = "10.5") then
    wscript.echo "True"
   end if
end if
next
wscript.quit

wmi's root\microsoft\sqlserver\computermanagement10 is where sql08 lives (non R2 and R2), and 10.5<more numbers> means R2. (10.0 is non-R2)

Re:I can't be the only one who's ever tried to do this.... (ccraig260)

Thu, 31 May 2012 11:39:36 GMT

Thanks for the help. It looks like the logic for the CI is working. Now I'm returning a non-compliant; not because of the value, but because of the detection method. "Assume Application is installed" returns an error, using the MSI as the detection method returns a non-compliant. the issue is that I can't seem to find the proper .msi that is the right one for the install of SQL 2008 R2.

any Ideas? I'll keep plugging away.

Re:I can't be the only one who's ever tried to do this.... (skissinger)

Thu, 31 May 2012 06:49:09 GMT

that configuring it, really... not looking. anyway, that helped with my google-fu to find the sql query that'll work in a CI.
not tested, but this should work:
on the CI, create Setting.
Setting Type = SQL Query
Data Type = Integer (I think, if it doesn't validate right, try a different type)
Use default instance
database = master
column = value
the sql statement is this:
SELECT value from master.sys.configurations where name = 'max server memory (MB)'

switch to Compliance Rules tab, and the new rule would be:
either not equals 2147483647, or less than that number. Or if that customer KNOWS that they always want <30gb for sql (or whatever number), you can put in whatever less than # makes sense. You could check the box about report noncompliance if setting doesn't exist--I don't think you really need to in this case--all SQL versions have this setting. A non-compliant for no instances of would really just be for your initial testing--just to make sure the CI is actually composed right and returns something.

Re:I can't be the only one who's ever tried to do this.... (ccraig260)

Wed, 30 May 2012 20:55:48 GMT

Thanks for the assist.
2147483647 = NON-Compliant

The direct SQL I used was:

EXEC sp_configure 'show advanced options', '1'
RECONFIGURE WITH OVERRIDE
EXEC sp_configure'max server memory (MB)'

Thanks,
Chris

Re:I can't be the only one who's ever tried to do this.... (skissinger)

Wed, 30 May 2012 18:42:24 GMT

What is the query you run directly in sql?
Then, what means "compliant" to that customer? that it NOT be 2147483647? or that is SHOULD be 2147483647?

I can't be the only one who's ever tried to do this.... (ccraig260)

Wed, 30 May 2012 16:58:34 GMT

Greetings,

Let me start with the fact that I am not very proficient with Powershell, WMI Querying, or advanced T-SQL. I have a customer who wants to be able to use DCM/Compliance to report on whether or not the setting for SQL max server memory is set to the default "2147483647" or not. I can run a small SQL query within SQL Management Studio to get the info, but I don't know the "IF...Then" to return Compliant/non-compliant. This will be used in a Configuration Item for Compliance.

Has anyone tried to do this and been successfull?

Thanks,

Chris Craig