Sunday, October 14, 2012

Config Manager Queries: CPU Types

I probably should revive my old ScriptZilla blog for stuff like this, but the heck with it.  I'm just posting all this here from now on.  After all: It is the brain-skattering blogness that I'm kicking around, if that even makes any sense.

This is a simple SQL query to fetch all the unique CPU manufacturers and names within your inventoried ball of confusion...

SELECT DISTINCT Manufacturer, Name, COUNT(Name) AS QTY
GROUP BY Manufacturer, Name 
ORDER BY Manufacturer, Name

Here's an example using VBScript (example is using a DSN-less connection with an explicit SQL user account and password. You can obviously run this under SSPI or "trusted" context, or using a stored DSN)

dsn = "DRIVER=SQL Server;SERVER=DBServer1;database=SMS_ABC;UID=username;PWD=password;"

Set conn = CreateObject("ADODB.Connection")
Set cmd  = CreateObject("ADODB.Command")
Set rs   = CreateObject("ADODB.Recordset")

conn.Open dsn

rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
rs.LockType = adLockReadOnly

Set cmd.ActiveConnection = conn

cmd.CommandType = adCmdText
cmd.CommandText = query
rs.Open cmd

If Not(rs.BOF And rs.EOF) Then
    xrows = rs.RecordCount
    Do Until rs.EOF
        For i = 0 to rs.Fields.Count -1
            wscript.Echo rs.Fields(i).Name & vbTab & rs.Fields(i).Value
    wscript.echo "no records found, bummer."
End If

found = False
Set rs = Nothing
Set cmd = Nothing
Set conn = Nothing

I was going to post a PowerShell example, but going from v2 to v3 I'm finding all sorts of confusing recommendations about the "best way" to invoke a simple T-SQL "SELECT" query against a remote SQL Server that my head is already spinning. Even some that just recommend installing custom cmdlet extensions, and whatnot. If anyone wants to point me to a nice, simple, concise, example (i.e. equal or fewer lines of code than the VBScript example above) please post a reply. Gracias!
Post a Comment