May 4 2011

Schedule SCCM reports to be emailed without SQL reporting services

I recently had a requirement to be able to email SCCM reports on a schedule in an environment without SQL reporting services. I achieved this by creating a VB script to create an email that has the report ASP page as the body of the email. The script I used and scheduled as a Windows scheduled task was:


Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = "Computers that do NOT have ......"
objMessage.From = "from@email.address"
objMessage.To = "to@email.address"
'==This section provides the configuration information for the remote SMTP server.
'==Normally you will only change the server name or IP.
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.server.address.here"
'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage.Configuration.Fields.Update
'==End remote SMTP server configuration section==
'The line below shows how to send a webpage from a remote site
objMessage.CreateMHTMLBody "http://serverURL/SMSReporting_P00/Report.asp?ReportID=422"
objMessage.Send

Just change the variables for your environment.
 
 

May 3 2011

SCCM report for computers with a service STOPPED

In a recent case, I needed to run a report to work out which systems had a particular server (antivirus) installed but was in the STOPPED state. After modifying the sms_mof file:

[SMS_Report (TRUE) ]
string State;

and running a hardware inventory on all machines, I could then create a report with the following query:

Select Distinct
SYS.Netbios_Name0,
sys.AD_Site_Name0,
sys.User_Domain0,
sys.User_Name0,
SERV.DisplayName0,
SERV.State0
From
v_R_System SYS Join
v_GS_SERVICE SERV On SYS.ResourceID = SERV.ResourceID
Where
SERV.DisplayName0 = 'OfficeScanNT RealTime Scan' and SERV.State0 = 'Stopped'
Order By
SYS.Netbios_Name0