Export SCCM 2007 Query Results Via VBScript

Question

I'm wondering if it's possible to export the results of a SCCM 2007 query via VBScript. If it is any suggestions would be greatly appreciated.

I know I can export reports through SCCM via VBScript by using the web-view of the report and faking the export=yes and submitting the form. Is something like this possible with a query?

Working code as a VBScript function that I ended up with:

'sccmQueryAsArray
'Returns: results in an array built based on the columns specified in the call of the function
'   parseInto: an array provided by the calling function that will be filled with the SCCM query results
'   sccmSiteServerName: a string representing the sccm site server we are connecting to
'   sccmSiteCode: a string representing the sccm site code we are connecting to
'   columns: a one dimensional array containing the names of the columns you want to the function to create an array for
'   query: the actual full length query that will be passed to WMI object
'       example call: sccmQueryAsArray data,"server1","sit",array("Name","UsergroupName"),"select Name, UsergroupName, WindowsNTDomain from sms_r_usergroup where AgentName = 'SMS_AD_SECURITY_GROUP_DISCOVERY_AGENT'"
'       results in an array "data" being modified to 2 columns and as many rows as is returned by the query the array would look like:
'           (0)(x) where 0 = the first element in the columns row or Name
'           (1)(x) where 1 = the second element in the columns row or UsergroupName
function sccmQueryAsArray( ByRef parseInto, ByVal sccmSiteServerName, ByVal sccmSiteCode, ByVal columns, ByVal query )

redim preserve parseInto(ubound(columns),0) 'the array that the query information will be parsed into and then returned

dim objWMIService: set objWMIService = getObject("winmgmts://" & sccmSiteServerName & "\root\sms\site_" & sccmSiteCode) 'set up the connection string
dim colGroups: set colGroups = objWMIService.ExecQuery(query) 'execute the query and put the results into colGroups

dim z: z = 0
dim objGroup: for each objGroup in colGroups
    dim y: for y = 0 to ubound(columns) step 1
        dim x: x = "objGroup." & columns(y)
        parseInto(y,z) = eval(x)
    next
redim preserve parseInto(ubound(parseInto,1),ubound(parseInto,2)+1): z = z + 1
next

sccmQueryAsArray = parseInto

end function

score 0 · Accepted Answer · answered Jan 11 '13 at 15:36

Why not just execute the query in VBScript?

You can attach to the SCCM server in VBScript, and use ExecQuery to send WQL queries to the server.

To use a simple example of converting an SCCM query into a VBScript, you might have a query that lists all AD security groups. Right-click that query, click Edit Query Statement and click Show Query Language.

You should have something like this:

select Name, UsergroupName, WindowsNTDomain from sms_r_usergroup where AgentName = 'SMS_AD_SECURITY_GROUP_DISCOVERY_AGENT'

Now click Show Query Design and copy down the names of the Attributes (ie column headers) that you're interested in, you should get a list something like this:

Name, User Group Name, Domain

To get the actual SQL column names for those (particularly for ones with space in them) just look at the WQL query in the previous step where you can see the column names listed straight after the select command. You should now have something like this:

Name, UsergroupName, WindowsNTDomain

Now just take all of that and put it into a VBScript, like:

'Central SCCM Site Server name
strComputer = "SCCM01"

'Central SCCM Site Code
strSiteCode = "ABC"

'Set up the connection String
Set objWMIService = GetObject("winmgmts://" & strComputer & "\root\sms\site_" & strSiteCode)

'Get the info with a query
Set colGroups = objWMIService.ExecQuery("select Name, UsergroupName, WindowsNTDomain from sms_r_usergroup where AgentName = 'SMS_AD_SECURITY_GROUP_DISCOVERY_AGENT'")

'output the info
For Each objGroup in colGroups
    Wscript.echo objGroup.Name & ", " & objGroup.UsergroupName & ", " & objGroup.WindowsNTDomain
Next

(obviously edit the site server and site code lines to match your environment).

Ok your code is absolutely amazing. I've modified the target and site code to work for my environment. The next part is changing your Agentname to match a custom query I've made that should work correct? — Mr. Lost IT Guy, Jan 11 '13 at 16:08
Using WMI Explorer to see what I can query through here to get what I am after. — Mr. Lost IT Guy, Jan 11 '13 at 16:13
@Mr.LostITGuy yes that's a very simplified example to show how it's done, I've run a lot of other queries like that through VBscript, many of them much more complex. One thing to beware of is quoting, SCCM Queries tend to quote text with `"` obviously you then get a double-quoting problem when you put it in the query above, just change the `"` to `'` and you should be good. — GAThrawn, Jan 11 '13 at 16:15
Yes I did the replace on the quotes and with the help of WMI Explorer (shameless plug for that dev) I was able to find the necessary information to get a custom query parsed into an array in vbscript for further manipulation. Giving you the hard earned correct! — Mr. Lost IT Guy, Jan 11 '13 at 16:24
One last question on this. How could I parse through each column in the for each loop (objGroup) without knowing the name of the columns? for each objGroup in colGroups 'for each column within objGroup 'print out value of that column 'next next — Mr. Lost IT Guy, Jan 11 '13 at 19:31

score 0 · Answer 2 · answered Feb 09 '13 at 05:56

Great post. I just wanted to add that if you would like to use PowerShell instead of VBS, you should check this out:

http://blogs.technet.com/b/manageabilityguys/archive/2009/11/27/more-on-sccm-and-powershell.aspx

SCCM 2012 comes with PowerShell built-in, so there shouldn't be a need to perform this for the new product.

Export SCCM 2007 Query Results Via VBScript

2 Answers2