sms: script to display advertisement information...

we've been frustrated for awhile by the lack of flexibility in the admin console. particularly, i'm referring to the advertisement view. if you're trying to figure out additional details beyond "available after", "expires after", and "status" you won't be able to do it through the gui. i won't lie and say you can't get this information at all. you could... i mean, you could open every advertisement and look at the schedule. that would only take a few minutes right? while you're doing that, i'm going to get more coffee.

alright, back. awake now. frustration has got to be the mother of scripting. :) seems like that's the only time we choose to go that route... and so frustration birthed a new script to help us along. basically a coworker wrote one up that would detail all advertisements and all additional information that you would normally want to know and presents the data in a spreadsheet.

so what kind of stuff would frustrated sms admins like to see anyway? here's our pick: advertisement id, name, start time, mandatory time, expiration time, target collection, and # of clients.

we thought # of clients would be particularly helpful in determining which collections are still used. as an example, you set an advertisement with a single mandatory start time (no recurrence) and point it to a collection of clients. this collection uses a query which would continue to remove clients as they no longer met the criteria, right? let's say the advertisement has been out there for a month. glancing at the spreadsheet, you see 3 clients. so out of whatever value you started at, you get down to 3... seems pretty successful. you check out those 3... they're no longer active. you can get lost trying to figure out which advertisements are no longer required, jumping back and forth from the collection to the ad. now you'll know pretty quickly.

advertisement id and target collection, we realize are visible and available to view in the console. however, in the spreadsheet, these values are actually hyperlinks to a web report with that information. the expiration time column also highlights the advertisements that have an expiration end time that has already been exceeded. why leave that crap around forever? :) anyway, here's an example of what you'd see...

and... here's the script if you want to try it out:

'Define variable and constants
IMMEDIATE = 2^(5)

Dim objAdvertisement
Dim smsadvertisementid()
Dim siteserver
Dim reportingurl
Dim sitecode
Dim starttime
Dim mandatorytime()
Dim adName
Dim adID
Dim expiretime
Dim collection
Dim program
Dim adlisting()
Dim a_adID()
Dim a_starttime()
Dim a_mandatorytime()
Dim a_expiretime()
Dim a_collcount()
Dim a_program()
adlistingcounter=0

siteserver = "mysmsserver" '<--------------------------------------Add your site server name here
reportingurl = "smsreporting_xyz" '<-------------------------------Revise to point to your reporting URL

'Error trapping in case a site server is not specified
If siteserver = "" Then
WScript.Echo "You have not specified a site server. Please define the siteserver variable within the script. Exiting."
WScript.Quit
End If

'Attach to SMS WMI on site server

Set ProviderLoc = GetObject _
   ("winmgmts:{impersonationLevel=impersonate}!\\" & siteserver & "\root\sms:SMS_ProviderLocation")

If Err.Number<>0 Then
   Wscript.Echo "Couldn't get SMS Provider"
   Wscript.Quit
End If

'Determine namespace

WScript.Echo "Determining WMI namespace..."

For Each Location In ProviderLoc.Instances_
    If Location.ProviderForLocalSite = True Then
    WScript.Echo "NAMESPACE=" & Location.NamespacePath
       Set objSWbemServices = GetObject("winmgmts:" & Location.NamespacePath)
       Exit For
  End If
Next

'Get ads
Set colAdvertisements = objSWbemServices.ExecQuery( "Select * From SMS_Advertisement")

adcounter = 0
for Each oAdvertisement In colAdvertisements
ReDim Preserve smsadvertisementid(adcounter)
smsadvertisementid(adcounter)= oAdvertisement.Advertisementid
adcounter=adcounter+1
Next

adcounter = adcounter -1

WScript.Echo "There are " & adcounter & " advertisements in place on " & siteserver & "."




'Put ad info into arrays

For displaycounter = 0 To UBound (smsadvertisementid)
getadproperties (smsadvertisementid(displaycounter))
For a = 0 To UBound (mandatorytime)
ReDim Preserve a_adID (adlistingcounter)
a_adID (adlistingcounter)= adID
ReDim Preserve a_adName (adlistingcounter)
a_adName (adlistingcounter)= adName
ReDim Preserve a_collection (adlistingcounter)
a_collection (adlistingcounter)= collection
ReDim Preserve a_collcount (adlistingcounter)
a_collcount (adlistingcounter) = getcollcount (collection)
ReDim Preserve a_starttime (adlistingcounter)
a_starttime (adlistingcounter) = starttime
ReDim Preserve a_mandatorytime (adlistingcounter)
a_mandatorytime (adlistingcounter) = mandatorytime (a)
ReDim Preserve a_expiretime (adlistingcounter)
a_expiretime (adlistingcounter) = expiretime
ReDim Preserve a_program (adlistingcounter)
a_program (adlistingcounter) = program
adlistingcounter = adlistingcounter+1

Next

Next

adlistingcounter = adlistingcounter-1



'Build Spreadsheet from arrays

WScript.Echo "Adding data to spreadsheet..."
rowval = 2
colval=0
Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Advertisement ID"
objExcel.Cells(1, 2).Value = "Name"
objExcel.Cells(1, 3).Value = "Start Time"
objExcel.Cells(1, 4).Value = "Mandatory Time"
objExcel.Cells(1, 5).Value = "Expiration Time"
objExcel.Cells(1, 6).Value = "Target Collection"
objExcel.Cells(1, 7).Value = "Clients"


Set objRange = objExcel.Range("A1","G1")
objRange.Font.Bold = True

For excelcounter = 0 To adlistingcounter
objExcel.Cells(rowval, 1).Value = a_adID(excelcounter)
objExcel.Workbooks(1).Worksheets(1).Hyperlinks.Add _
   objExcel.Cells(rowval, 1), "http://" & siteserver & "/" & reportingurl & "/Report.asp?ReportID=111&AdvertID=" & a_adID(excelcounter)
objExcel.Cells(rowval, 2).Value = a_adName(excelcounter)
objExcel.Cells(rowval, 3).Value = a_starttime(excelcounter)
objExcel.Cells(rowval, 4).Value = a_mandatorytime(excelcounter)
objExcel.Cells(rowval, 5).Value = a_expiretime(excelcounter)
objExcel.Cells(rowval, 6).Value = a_collection(excelcounter)
objExcel.Workbooks(1).Worksheets(1).Hyperlinks.Add _
objExcel.Cells(rowval, 6), "http://" & siteserver & "/" & reportingurl & "/Report.asp?ReportID=135&ID=" & a_collection(excelcounter)
objExcel.Cells(rowval, 7).Value = a_collcount(excelcounter)


If a_expiretime(excelcounter)<> "Never" Then
   If DateDiff ("n",a_expiretime(excelcounter),Now) > 1 Then
   Set objRange = objExcel.Cells(rowval, 5)
   objRange.Interior.ColorIndex = 36


  
   End If
End If
rowval = rowval+1
Next

objExcel.Cells(1, 1).EntireColumn.AutoFit()
objExcel.Cells(1, 2).EntireColumn.AutoFit()
objExcel.Cells(1, 3).EntireColumn.AutoFit()
objExcel.Cells(1, 4).EntireColumn.AutoFit()
objExcel.Cells(1, 5).EntireColumn.AutoFit()
objExcel.Cells(1, 6).EntireColumn.AutoFit()
objExcel.Cells(1, 7).EntireColumn.AutoFit()

WScript.Echo "Done!"


Function getadproperties (advID)
adID=advID
WScript.Echo "Retrieving properties of advertisement " & advid

Set objAdvertisement = GetObject _
   ("WinMgmts:" & Location.NamespacePath & ":SMS_Advertisement.AdvertisementID='" & advID & "'")


starttime = WMIDateStringToDate (objAdvertisement.presenttime)
adName = objAdvertisement.AdvertisementName
ExpirationTimeEnabled = objAdvertisement.ExpirationTimeEnabled
collection = objAdvertisement.collectionid
program = objAdvertisement.programname
If Not ExpirationTimeEnabled Then
expiretime="Never"
Else
expiretime=WMIDateStringToDate (objAdvertisement.ExpirationTime)
End If


intAdvertFlags = objAdvertisement.AdvertFlags








ExpirationTimeEnabled = objAdvertisement.ExpirationTimeEnabled
timecounter = 0
for each objSched In objadvertisement.assignedschedule
select Case objSched.Path_.Class

        Case "SMS_ST_NonRecurring"

           strInfo =  "Non-Recurring Assignment: "

           strInfo = strInfo & "Occurs at " & WMIDateStringToDate (objSched.StartTime)

           if objSched.IsGMT Then

               strInfo = strInfo & " GMT"

           end If

          

       case "SMS_ST_RecurInterval"

           strInfo = "Recurring Interval Assignment: "

           strInfo = strInfo & "Every " & objSched.DaySpan & " days, " & objSched.MinuteSpan & " minutes, "

           strInfo = strInfo & "beginning on " & WMIDateStringToDate(objSched.StartTime)

           if objSched.IsGMT then

               strInfo = strInfo & " GMT"

           end if     

      

       case "SMS_ST_RecurMonthlyByDate"

           strInfo = "Recurring Monthly By Date: "

           strInfo = strInfo & "Occurs on the " & objSched.MonthDay & " day, every " & objSched.ForNumberOfMonths & " months, "

           strInfo = strInfo & "beginning on " & WMIDateStringToDate(objSched.StartTime)

           if objSched.IsGMT Then

               strInfo = strInfo & " GMT"

           end if         

      

       case "SMS_ST_RecurMonthlyByWeekday"

           strInfo = "Recurring Monthly By Weekday: "

           strInfo = strInfo & "Occurs on the " & objSched.Day & " day, every " & objSched.ForNumberOfMonths & " months, " & "for week order " & objSched.WeekOrder & ","

           strInfo = strInfo & "beginning on " & WMIDateStringToDate(objSched.StartTime)

           if objSched.IsGMT Then

               strInfo = strInfo & " GMT"

           end if             

      

       Case "SMS_ST_RecurWeekly"

           strInfo =  "Recurring Monthly By Weekday: "

           strInfo = strInfo & "Occurs on the " & objSched.Day & " day, every " & objSched.ForNumberOfWeeks & " weeks, "

           strInfo = strInfo & "beginning on " &  WMIDateStringToDate(objSched.StartTime)

           if objSched.IsGMT Then

               strInfo = strInfo & " GMT"

           end if             
      
       Case Else
      
                   strInfo =  "Unable to retrieve mandatory time"
              

   End Select
  

ReDim Preserve mandatorytime (timecounter)
mandatorytime(timecounter) = strinfo
timecounter = timecounter +1
Next
If timecounter = 0 Then
strinfo = "No assignment"
      

End if
If (intAdvertFlags And IMMEDIATE) Then
  
strInfo = "As soon as possible"
ReDim Preserve mandatorytime (timecounter)
mandatorytime(timecounter) = strinfo
End If








End Function

Function WMIDateStringToDate(dtmInstallDate)
WMIDateStringToDate = CDate(Mid(dtmInstallDate, 5, 2) & "/" & Mid(dtmInstallDate, 7, 2) & "/" & Left(dtmInstallDate, 4) & " " & Mid (dtmInstallDate, 9, 2) & ":" & Mid(dtmInstallDate, 11, 2) & ":" & Mid(dtmInstallDate, 13, 2))

End Function




Function getcollcount (collid)
num_clients = 0
Set colQueryCollectionResults=objSWbemServices.ExecQuery _
   ("SELECT Count (Name) FROM SMS_FullCollectionMembership WHERE CollectionID='" & collid & "'")

For Each objResult In colQueryCollectionResults
   
getcollcount = objresult.count   
Next


End Function