This script will allow you to choose a collection name from a drop down menu list and list the collection member names to the active window.
Note: Change the following to reflect your site:
strComputer = "SiteServerName"
strSiteCode = "XXX"
strSqlServer = "SqlDatabaseServer"
strDatabase = "XXX"
HTA Script:
<Html>
<Head>
<Title>HTA Script</Title>
<Style>
Body {Background-Color: CornSilk}
</Style>
<HTA:Application
Caption = Yes
Border = Thick
ShowInTaskBar = No
MaximizeButton = Yes
MinimizeButton = Yes>
<script Language = VBScript>
Sub ListItems(ItemList)
Window.Document.Title = "List Collection Members"
Set objWMIService = GetObject("winmgmts://" & strComputer & "\root\sms\site_" & strSiteCode)
Set colItems = objWMIService.ExecQuery("Select * from SMS_Collection")
Document.Write "<select name=""" & ItemList & """>"
For Each objItem in colItems
Document.Write "<option value=""" & objItem.Name & """>"
Document.Write objItem.Name
Document.Write "</option>"
Next
Document.Write "</select>"
End Sub
Sub DisplayItems
strHTML = "<table border='1' style='border-collapse: collapse' bordercolor='SaddleBrown' id='Table1' >"
strHTML = strHTML & "<tr>"
strHTML = strHTML & "<br>"
strHTML = strHTML & "<td width='10%' bgcolor = 'SeaShell'><font color = 'Blue'><b>Collection Name</td>"
strHTML = strHTML & "<td width='10%' bgcolor = 'SeaShell'><font color = 'Blue'><b>Member Name</td>"
strHTML = strHTML & "</tr>"
strCollectionName = GetItems.Value
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open "Provider=SQLOLEDB;Data Source =" & strSqlServer & ";" & _
"Trusted_Connection=Yes;Initial Catalog =SMS_" & strDatabase
objRecordSet.Open "Select C.CollectionName, M.Name FROM Collections C " & _
"Join CollectionMembers M ON C.SiteID = M.SiteID " & _
"Where C.CollectionName =" & "'" & strCollectionName & "'", objConnection, adOpenStatic, adLockOptimistic
On Error Resume Next
objRecordSet.MoveFirst
Do Until objRecordset.EOF
Window.Document.Title = UCase(GetItems.Value) & " - Collection Members "
strHTML = strHTML & "<td width='1%'>" & objRecordset.Fields.Item("CollectionName") & "</td>"
strHTML = strHTML & "<td width='1%'>" & objRecordset.Fields.Item("Name") & "</td>"
objRecordset.MoveNext
Loop
objRecordSet.Close
objConnection.Close
strHTML = strHTML & "</table>"
DataArea.InnerHTML = strHTML
</script><Body>
<p><h3 align = center><font color='Orange'>Please Visit myITforum.Com</font></h3><div></div>
<script language="VBScript">ListItems("GetItems")</script>
<input id=DisplayButton class="button" type="button" value="Run Script" name="DisplayItems_button" onClick="DisplayItems">
</body></html>
<Span Id = "DataArea"></Span></Body><Div Align = "Center">
<P><A Href="http://myitforum.com/cs2/blogs/dhite">Created For myITforum By Don Hite</A>
No Comments