This is how you would return specific records from a SQL Database (I’m using a SCCM DB). You could use this to return computer inventory data, SCCM administrative info, or reporting information….or you could wrap the returned data into a function and create a support application, like remotely connecting to a computer using CmRcViewer.exe, located in: D:\Program Files\Microsoft Configuration Manager\AdminConsole\bin\i386.
This could be easily ported to PowerShell or C#.
Note: The user account should eventually become a service account…and all resource files should be compiled.
Screenshot
For example, to return Usernames in the User_DISC table…
Script
Dim Connection, Recordset, SQL, Server, field, strAllFields
'Declare the SQL statement that will query the database
SQL = "SELECT * FROM dbo.User_DISC"
'other sql statements
'SQL = "SELECT DISTINCT * FROM dbo.User_Name0 ORDER BY User_Name0 DESC"
'Create an instance of the ADO connection and recordset objects
Set Connection = CreateObject("ADODB. Connection")
Set Recordset = CreateObject("ADODB.Recordset" )
'Open the connection to the database
Connection.Open "DSN=test;UID=MySCCMAccount;PWD= LetMeIn99$;Database=CM_001"
'Open the recordset object executing the SQL statement and return records
Recordset.Open SQL,Connection
'Determine whether there are any records
If Recordset.EOF Then
WScript.echo "There are no records to retrieve; Check that you have the correct SQL query."
Else
'if there are records then loop through the fields
Do While NOT Recordset.Eof
'What I want to return
field = Recordset("User_Name0")
'return more than one column
'field = Recordset("first_name") & ", " & Recordset("first_name")
'Store all returned values into a string
if field <> "" then
strAllFields = chr(13) & field + strAllFields
end if
Recordset.MoveNext
Loop
End If
'Display all values
msgbox "Username " & chr(13) & chr(13) & strAllFields
'Close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing
Notes
