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
FROM dbo.v_LU_CPU
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
        Next
        rs.MoveNext
    Loop
Else
    wscript.echo "no records found, bummer."
End If

found = False
rs.Close
conn.Close
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