May 17 2012

SCCM report – Packages that have been updated since original content was distributed

 

I needed to quickly identify all of the packages in a SCCM environment that had been updated since their original source (version 1) was distributed, along with the date this occured. The following simple SQL query formed a nice reports. This displays all packages Source Date and Version, ordered by Source Date where sourceversion is greater than 1 (updated since original content was distributed):

 

select PackageID,Name,PkgSourcePath,SourceDate,SourceVersion from v_Package where SourceVersion > 1 order by SourceDate DESC

 
 



----------------------------------------------------------------------------
I use a maximum of one Google Ad per post to help offset some of my blog hosting costs.

----------------------------------------------------------------------------

August 15 2011

SCCM report for SQL server Developer, Enterprise and Standard version installations

I had a requirement to report on SQL Server installations in our environment as part of our Microsoft EA true-up process. This included SQL 2000, 2005 & 2008 installations. The difficulty was that you can’t run a simple Add / Remove Programs report because it will report all of the SQL client tools installations as well as the Express and Windows Internal Databases version. I needed to actually report on just the SQL Server Developer, Enterprise and Standard version installations.

After using the MOF edit by Sherry Kissinger (http://myitforum.com/cs2/blogs/skissinger/archive/2010/12/20/installed-sql-05-and-08-version-information-via-configmgr-hardware-inventory.aspx), I then create a report that filtered out much of the details and gave me a summary of SQL 2000-2005-2008-2008R2 Server Developer, Enterprise and Standard version installations. The query for the SCCM report is:

select
sys1.Netbios_name0 as [Machine],
max(Case sql.PropertyName0 when 'SKUName' then
sql.PropertySTRValue0 end) as [SQL 2008 / R2 Edition],
max(Case sql2.PropertyName0 when 'SKUName' then
sql2.PropertySTRValue0 end) as [SQL 2005 Edition],
sys1.ad_site_name0 as [Site Name],
v_R_User.Full_User_Name0 As [Primary Machine User]
from v_r_system sys1
left join v_gs_sql_property0 sql on sys1.resourceid=sql.ResourceID
left join v_gs_sql_property_legacy0 sql2 on sys1.ResourceID=sql2.ResourceID
left Outer Join v_R_User On sys1.User_Name0 = v_R_User.User_Name0
where
(sql.PropertyName0 in ('SKUNAME','SPLevel','version','fileversion')
or
sql2.PropertyName0 in ('SKUNAME','SPLevel','version','fileversion'))
and
(
sql.PropertyStrValue0 LIKE '%standard%' or
sql2.PropertyStrValue0 LIKE '%standard%' or
sql.PropertyStrValue0 LIKE '%enterprise%' or
sql2.PropertyStrValue0 LIKE '%enterprise%' or
sql.PropertyStrValue0 LIKE '%develop%' or
sql2.PropertyStrValue0 LIKE '%develop%'
)
group by sys1.Netbios_name0, sys1.User_Name0, sys1.ad_site_name0, v_R_User.Full_User_Name0

 
 

June 1 2011

SCCM report – count physical vs virtual servers

This report displays the number of servers in the environment and gives a breakdown and percentage view of physical vs virtual:


Declare @Total as int
Declare @Physical as int
Declare @Virtual as int
set @Virtual =
(Select distinct count (*)
From
v_R_System full Join
v_GS_COMPUTER_SYSTEM On v_GS_COMPUTER_SYSTEM.ResourceID =
v_R_System.ResourceID
where v_R_System.Operating_System_Name_and0 LIKE '%server%'
and v_R_System.Client0 = 1
and (v_GS_COMPUTER_SYSTEM.Manufacturer0 = 'Microsoft Corporation' or v_GS_COMPUTER_SYSTEM.Manufacturer0 = 'VMware, Inc.'))
set @Total =
(Select distinct count (*)
From
v_R_System full Join
v_GS_COMPUTER_SYSTEM On v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceID
where v_R_System.Operating_System_Name_and0 LIKE '%server%'
and v_R_System.Client0 = 1 )
set @Physical = (select @Total - @Virtual)
Select @Physical as 'Physical servers',
@Virtual as 'Virtual servers',
@Total as 'Total servers',
(select (@Virtual * 100) / @Total) as 'Percentage (%) that are virtual'

 
 

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

 
 

February 7 2011

SCCM report for all server details including age

I recently had a requirement to report on server ages for the machines in our environment. Adding the ‘BIOS Manufacture date’ and ‘OS Install Date’ should be a good indication of this. The query will also return some other helpful fields like model, manufacturer, RAM and processor:

SELECT distinct
CS.name0 as 'Server Name',
OS.Caption0 as 'OS',
CU.Manufacturer0 as 'Manufacturer',
CU.Model0 as 'Model',
RAM.TotalPhysicalMemory0/1024 as [RAM (MB)],
processor.Name0 as 'Processor',
BIOS.ReleaseDate0 as 'BIOS Manufacture Date',
OS.InstallDate0 as 'OS Install Date'

from
v_R_System CS
FULL join v_GS_PC_BIOS BIOS on BIOS.ResourceID = CS.ResourceID
FULL join v_GS_OPERATING_SYSTEM OS on OS.ResourceID = CS.ResourceID
FULL join V_GS_X86_PC_MEMORY RAM on RAM.ResourceID = CS.ResourceID
FULL JOIN v_GS_PROCESSOR Processor on Processor.ResourceID=CS.ResourceID
FULL join v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = CS.ResourceID
FULL join v_GS_COMPUTER_SYSTEM CU on CU.ResourceID = CS.ResourceID

WHERE CS.Operating_System_Name_and0 LIKE '%nt%server%' and CS.Client0 = 1

group by
CS.Name0,
OS.Caption0,
CU.Manufacturer0,
CU.Model0,
RAM.TotalPhysicalMemory0,
BIOS.ReleaseDate0,
OS.InstallDate0,
Processor.Name0,
BIOS.ReleaseDate0
Order by CS.Name0