SCCM Database Queries: Find Executable Files by Name, Version and Install Count

Find all instances of a particular file by its distinct Version number (e.g. "FileVersion" property).  In this example, I'm looking for what versions of Sysinternals' PsExec.exe are in the environment, and how many instances were found for each version.

[Begin T-SQL]
SELECT DISTINCT 
[ExecutableName0],[FileVersion0], COUNT(*) AS QTY
FROM [dbo].[v_GS_INSTALLED_EXECUTABLE]
WHERE ExecutableName0 = 'psexec.exe'
GROUP BY ExecutableName0, FileVersion0
ORDER BY FileVersion0
[End T-SQL]

Find Configuration Manager Collections with Both Direct and Query Rule Members

List all Collections which have both Direct members and Query-Rules assigned within your SCCM 2012 Site.  This also works with Configuration Manager 2007...

[begin T-SQL]
SELECT DISTINCT 
dbo.v_CollectionRuleDirect.CollectionID, 
dbo.v_Collection.Name
FROM 
dbo.v_CollectionRuleDirect INNER JOIN
dbo.v_CollectionRuleQuery ON dbo.v_CollectionRuleDirect.CollectionID = dbo.v_CollectionRuleQuery.CollectionID INNER JOIN
dbo.v_Collection ON dbo.v_CollectionRuleDirect.CollectionID = dbo.v_Collection.CollectionID
ORDER BY dbo.v_Collection.Name
[end T-SQL]

SQL Case Statements for Sum Operations

SELECT DISTINCT TOP (100) PERCENT
      room_type, COUNT(room_id) AS rooms, SUM(room_area) AS sqft,
      CASE
                WHEN room_type = 1 THEN 'Office'
                WHEN room_type = 2 THEN 'Storage'
                WHEN room_type = 3 THEN 'Equipment'
                WHEN room_type = 4 THEN 'Computer'
                WHEN room_type = 5 THEN 'Medical'
                WHEN room_type = 6 THEN 'Public'
                ELSE 'Unknown'
        END AS TypeName
FROM dbo.tbl_rooms
GROUP BY room_type
ORDER BY room_type

Returns something like the following:

room_type rooms     sqft
Office      210    6,540
Storage      54      983
Equipment    86    1,002
Computer      3      852
Medical     640   39,553
Public        6   10,401
Unknown       0        0

VBScript ADO with XML and CSV Data Files

I was poking around to find a way to read CSV, TXT (various delimiters) through VBScript for use in scripted data migrations, as well as to render the data in ASP, and found some interesting things.  I have to give credit to [http://www.mombu.com/microsoft/scripting-wsh/t-query-xml-file-with-ado-in-wsh-131431.html] for the XML example.  I only modified it slightly to fit in with how I tend to code ADO processes.  I also would like to applaud the efforts of Connection Strings.com for their fantastic reference site for connection information for almost anything that can possibly store information.

Reading XML Data

Const dsn = "Provider=MSDAOSP;Data Source=MSXML2.DSOControl;"
Const adChapter = 136

Dim conn, rs
Dim iLevel : iLevel = 0

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

conn.Open dsn

rs.Open CreateObject( "WScript.Shell" ).CurrentDirectory + _
    "\test.xml", conn

WalkHier iLevel, rs

rs.Close
conn.Close

Sub WalkHier(ByVal iLevel, ByVal rs)
    iLevel = iLevel + 1
    Dim PriorLevel : PriorLevel = iLevel
    Dim i, adoChildRS
    While Not rs.EOF
        For i = 0 To rs.Fields.Count - 1
            If rs.Fields(i).Name <> "$Text" Then
                If rs.Fields(i).Type = adChapter Then
                    Set adoChildRS = rs.Fields(i).Value
                    WalkHier iLevel, adoChildRS
                Else
                    wscript.echo iLevel & ": rs.Fields(" & i & ") = " & _
                        rs.Fields(i).Name & " = " & rs.Fields(i).Value
                End If
            End If
        Next
        rs.MoveNext
    Wend
    iLevel = PriorLevel
End Sub

Reading CSV Data

dsn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='s:\scripts\';Extended Properties=""text;HDR=Yes;FMT=Delimited(,);"""

query = "SELECT * FROM test.csv"

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
    Do Until rs.EOF
        For i = 0 to rs.Fields.Count - 1
            wscript.echo rs(i).name & " = " & rs(i).value
        Next
        rs.MoveNext
    Loop
Else
    wscript.echo "error: no records found"
End If
rs.Close
conn.Close
Set rs = Nothing
Set cmd = Nothing
Set conn = Nothing

LDAP Query for User Accounts Created Since a Specific Date

Just modify the date string to use the YYYYMMDDHHMMSS.0Z format. So, for June 1, 2009, you would specify "20090601000000.0Z"

(&(objectCategory=user)(whenCreated>=20090601000000.0Z))

LDAP Query for Printers = HP DesignJet Plotters

(&(&
(uncName=*)
(objectCategory=printQueue)
(objectCategory=printQueue)
(driverName=*DesignJet*)
))

LDAP Query for Windows Server 2003 SP1 Computers in AD

(&(&(&(&(&(&(&(&(&(&
(objectCategory=Computer)
(operatingSystem=Windows Server 2003*)
(operatingSystemServicePack=Service Pack 1)
))))))))))

VBScript: ADOX Create Access Database, Table and Insert Row

Adapted from portions of code on MSDN at http://msdn.microsoft.com/en-us/library/ms681497(VS.85).aspx

'----------------------------------------------------------------
' first we create the database file itself
'----------------------------------------------------------------

Const dbFile = "c:\database.mdb"
strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbFile

Const adVarWChar = 202
Const adSingle = 4
Const adLockOptimistic = 3
Const adOpenDynamic = 2
Const adCmdTable = &H0002

On Error Resume Next
wscript.echo "info: creating database..."
Dim objCatalog
Set objCatalog = CreateObject("ADOX.Catalog")
objCatalog.Create strDSN
If err.Number = 0 Then
    wscript.echo "info: database created successfully"
Else
    wscript.echo "fail: error = " & err.Number & " (" & err.Description & ")"
    wscript.Quit(1)
End If

'----------------------------------------------------------------
' next we create a table in the new database
'----------------------------------------------------------------

wscript.echo "info: creating table..."

Set objTable = CreateObject("ADOX.Table")
objCatalog.ActiveConnection = strDSN

With objTable
    .Name = "tbl_Employees"
    .Columns.Append "empID", adVarWChar, 12
    .Columns.Append "fname", adVarWChar, 50
    .Columns.Append "lname", adVarWChar, 50
    .Columns.Append "dept", adVarWChar, 50
    .Columns.Append "phone", adVarWChar, 50
    .Columns.Append "email", adVarWChar, 50
    .Columns.Append "jobID", adSingle
    .Columns.Append "birthDay", adVarWChar, 20
End With

objCatalog.Tables.Append objTable

If Err.Number <> 0 Then
    wscript.echo "fail: " & err.Number & ": " & err.Description
    Set objTable = Nothing
    Set objCatalog = Nothing
    wscript.Quit(1)
Else
    wscript.echo "info: table created successfully"
End If

Set objTable = Nothing
Set objCatalog = Nothing

'----------------------------------------------------------------
' comment: SQL INSERT
'----------------------------------------------------------------
wscript.echo "info: entering a new row..."

On Error Resume Next
Set rs = CreateObject("ADODB.Recordset")
rs.Open "tbl_Employees", strDSN, adOpenDynamic, adLockOptimistic, adCmdTable

If err.Number <> 0 Then
    rs.Close
    Set rs = Nothing
    wscript.echo "fail: error = " & err.Number & " (" & err.Description & ")"
    wscript.Quit(1)
End If

rs.AddNew
rs("empID").value = "100400"
rs("fname").value = "JOHN"
rs("lname").value = "DOE"
rs("dept").value  = "SALES"
rs("phone").value = "800-555-1212"
rs("email").value = "john_doe@dumbass.local"
rs("jobID").value = 4002
rs("birthDay").value = "#03/01/1966#"
rs.Update
If err.Number <> 0 Then
    rs.Close
    Set rs = Nothing
    wscript.echo "fail: error = " & err.Number & " (" & err.Description & ")"
    wscript.Quit(1)
End If

rs.Close
Set rs = Nothing

KiXtart: Enumerate Access Database Tables with ADOX

$dbfile = "\\servername\share\folder\database.mdb"

Function ListTablesADOX($db)
    Dim $Conn, $strConn, $Catalog, $Table, $Column
    $strConn = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=$db"
 
    ? "Database: $db @CRLF"
 
    $Conn    = CreateObject("ADODB.Connection")
    $Catalog = CreateObject("ADOX.Catalog")
    $Table   = CreateObject("ADOX.Table")
    $Column  = CreateObject("ADOX.Column")
 
    $Conn.Open($strConn)
    $Catalog.ActiveConnection = $Conn

    For Each $Table In $Catalog.Tables
        ? "Table: " + $Table.Name
        For Each $Column In $Table.Columns
            ? Chr(9) + "Column: " + $Column.Name
        Next
    Next
    $Conn.Close
EndFunction

$=ListTablesADOX($dbfile)