April 2008 - Posts

• SMS2003 - How to easily create reports - For reporting first-timers and the like

  I was pondering (like you do) how accessible report creation is in SMS2003. It requires a few skills to be under-your-belt (SQL, SQL tools, areas in SMS) before you can master this subject enough to produce quality reports. And, well I was taught (back when I was a padawan, I eventually killed my master in true Star Wars Sith Lord fashion while he slept after a heavy nights SMS2003 SP3 troubleshooting, the old fool! ;-) a trick for creating reports that lined up my knowledge a bit more and helped kick-start my efforts in creating new and modifying existing reports. In the linked document I talk about how to use SQL2000 Enterprise Manager and present some screenshots to help guide the novice along to the conclusion ... a complex (in that it uses a JOIN) report. Here's the file description: A word document (due to so many screenshots) showing how to easily create SQL2000 queries for use in SMS2003 Reporting I get a lot of SQL query creation questions, so thought writing up a short document on how to use SQL2000 Enterprise Manager to build complicated (well reasonably) SQL queries would be helpful. If you're experienced with SQL, then you won't learn much at all from this document. If you've shied away from writing SMS2003 Reports due to lack of SQL knowledge then this is a great aid to getting "skilled up" enough to put you on the path that'll take you forwards to carving out epic-style reports If you're new to writing reports for SMS2003 then this will really help you get a foot on the first rung of the proverbial ladder. Go get that word doc and check it out ... Hope it helps somehow,   Posted Apr 15 2008, 09:14 AM by Robs Blog Filed under: SMS2003, Reporting

• SMS2003, creating an Advertisement with a single assignment

  A collegue of mine spent hours researching how to create a simple advertisement assignment. He was using the SDK in VBScript and was frustrated at the lack of easy-to-digest information out there. He'd arrived at MSDN, Technet, poured over the SMS2003 SDK help file and even googled around. He almost had the problem solved when he came across references (via google books) to "SMS2003 Recipes". Unfortunately he just couldn't adapt the examples to his needs. I thought he was just doing things wrong but after spending a bit of time seeing "what's out there" I realised this is another one of those niche areas that needs a bit more illumination with the "laymans" hat on. So here's VBScript sample code to create a new advertisement, and then create a single Assignment for the Advertisement. Note: I'll not be covering every aspect of what you can do with an Advertisement using the SDK. Instead i'll just reference the most common things configured for an Advertisement. If you want to configure it further, google is your friend ;-) First you need to connect to your SMS2003 Site server. Here's a peice of code that does that using just the servers machine name:  Function ConnectToServer(sServer)  Dim oSiteDetails, oSiteDetail, sSiteCode  Set objLocator = CreateObject("WbemScripting.SWbemLocator")  If Err.Number = 0 Then   Err.Clear   WScript.Echo "Connecting to server " & sServer & " as the target SMS2003 Site server"   Set objSMS = objLocator.ConnectServer(sServer, "Root/SMS")   WScript.Echo vbTAB & "Connected, now attempting to connect to the Sites WMI Namespace"   objSMS.Security_.ImpersonationLevel = 3   Set oSiteDetails = objSMS.ExecQuery("select Machine, SiteCode from SMS_ProviderLocation where ProviderForLocalSite=True")    If Err.Number = 0 Then       For Each oSiteDetail In oSiteDetails        sSiteCode = oSiteDetail.SiteCode     Next     WScript.Echo vbTAB & "Connected, Identified Site Code as " & sSiteCode & " for " & sServer    End If   Set objSMS = objLocator.ConnectServer(sServer, "root/SMS/site_" + sSiteCode)   If Err.Number = 0 Then    WScript.Echo vbTAB & "Connected to the SMS Site Namespace for " & sServer & " (" & sSiteCode & ")"   End If   ConnectToServer = Err.Number  End If End Function Now you have a handle to the SMS2003 Site server, you can create the advertisement: DIM array(), newAdvertisement, instToken, objSMS, objLocator, oSiteDetail, oSiteDetails ReDIM array(0) If ConnectToServer("<SERVER NAME>") <> 0 Then WScript.Echo "Error occured connecting to SMS2003 Site server"  WScript.Quit(1) End If Set newAdvertisement = objSMS.Get("SMS_Advertisement").SpawnInstance_() newAdvertisement.AdvertisementName = "Advert Name" newAdvertisement.Comment = "Advert Comment" newAdvertisement.CollectionID = "<COLLECTION ID>" newAdvertisement.PackageID = "<PACKAGE ID>" newAdvertisement.ProgramName = "<PROGRAM NAME>" newAdvertisement.ExpirationTime = "20080401220000.000000+***" newAdvertisement.PresentTime = "20080401210000.000000+***" Set instToken = objSMS.Get("SMS_ST_NonRecurring").SpawnInstance_() instToken.DayDuration = 0 instToken.HourDuration = 0 instToken.IsGMT = False instToken.MinuteDuration = 0 instToken.StartTime = "20080401210000.000000+***" Set array(0) = instToken newAdvertisement.AssignedSchedule = array NewAdvertisement.AssignedScheduleEnabled = True newAdvertisement.ExpirationTimeEnabled = True newAdvertisement.IncludeSubCollection = False newAdvertisement.Put_ Now you need to put the Function ConnectToServer at the bottom of your script, the code for creating the advertisement at the top,  change all references that need real information such as <COLLECTION ID> etc then finally adjust the ExpirationTime, PresentTime and StartTime. How you change these date\times is to use another function to massage the date\time in to the format expected by SMS2003. This format looks like this: "20080401210000.000000+***" and you can see a good example from Greg Ramsey and Warren Byles "SMS2003 Recipes" book via this link on how to produce these dates.  This should get you off to a good start. Any problems leave comments and i'll try to defend my dodgy code SMS2003 Advertisement SDK VBScript Assignment Schedule Simple Example Automation Posted Apr 01 2008, 10:40 AM by Robs Blog Filed under: SMS2003, Scripting