Report to list Boundary Group with Site System Details

Hi Everyone,

Recently i got in to a trouble that one of the boundary was targeted to wrong site system and i really had a tough time opening all the boundary to see the site systems.

So i have created a report to ease this work and i hope some one out there might require this.

This report will provide the Boundary Group Name, Assignment Site Code, Site System, Boundary Target details, Modified date and admin name.


Create a report using below query and comment me if you have trouble running the report.

------

select sys1.Name, sys1.DefaultSiteCode,
(select SUBSTRING(sys2.ServerNALPath, CHARINDEX('\\', sys2.ServerNALPath) + 2,
CHARINDEX('"]', sys2.ServerNALPath) - CHARINDEX('\\', sys2.ServerNALPath) - 3 ) +
CASE sys2.Flags WHEN '1' Then ' (Slow)' WHEN '0' THEN '' END + '; ' as 'data()'
from vSMS_BoundaryGroupSiteSystems as sys2 where sys1.GroupID=sys2.GroupID
for XML path('')) as 'Site System',
(select sys4.Value + '; ' as 'data()' from vSMS_BoundaryGroupMembers as sys3
left join vSMS_Boundary as sys4 on sys3.BoundaryID=sys4.BoundaryID where sys1.GroupID=sys3.GroupID
for XML path('')) as 'Boundary', sys1.ModifiedOn, sys1.ModifiedBy
from vSMS_BoundaryGroup as sys1

------

-
------------------------------------------
Ur's -> SithaYuvaraj | SCCM Administrator
------------------------------------------
-
This posting is provided AS-IS with no warranties/guarantees and confers no rights.

Use at your own risk
-

SCCM 2012 Report to Compare the Machines in AD without SCCM Client

Hi All,

We were working on report that can compare the objects available in Active Directory and not in SCCM, initially we planned to use LogParser and a VB Script which will fetch Active Directory objects and updates in a TempDB. Post, we can create a report to compare the clients that are available in AD but not in SCCM Database.

But we are not indent to disturb the Database using LogParser. hence we have created a query which will compare the V_R_System and V_GS_Computer_System to identify the clients that are available in Active Directory but not in SCCM or without SCCM Client, and this report also shows you lastLogon details.

I hope this can help some one out there.

SQL Query

---------


SELECT Name0, sys1.Operating_System_Name_and0,  DATEDIFF(dd, [Last_Logon_Timestamp0], getdate()) AS 'days LastLogon'
FROM v_r_system sys1
WHERE Name0 NOT IN (SELECT name0 FROM v_GS_Computer_System)
ORDER BY [days LastLogon]

--------


-
------------------------------------------
Ur's -> SithaYuvaraj | SCCM Administrator
------------------------------------------
-
This posting is provided AS-IS with no warranties/guarantees and confers no rights.
-

SCCM 2012 Report to get All Site Server & System with there Roles

Hi All,

I am going to share you the report i created for SCCM 2012 to generate details about all site server and site system and there hostname and the roles they are running and there parent site code.

and the short form for the roles used in this report are given below…

SQL Server        -    SQL DB
Distribution Point        -    DP
Management Point        -    MP
Device Management Point    -    DMP
System Health Validator    -    SHV
State Migration Point    -    SMP
Provider            -    SMS Prov
Software Update Point    -    SUP
Fallback Status Point    -    FSP
Multicast Service Point    -    MSP
AMT Service Point        -    AMT
AI Update Service Point    -    AI
SRS Reporting Point        -    SRS
Application Web Service    -    APP WS
Portal Web Site        -    Por WS
Enrollment Server        -    ES
Enrollment Web Site    -    EWS
Endpoint Protection Point    -    EPP

—————–

select distinct sys1.ServerName, sys5.Caption0 as 'Operating System',
CASE sys4.SystemType0
WHEN 'x64-based PC' THEN 'x64'
WHEN 'x86-based PC' THEN 'x86'
END as 'Type',
sys2.SiteCode as 'Site Code', sys2.SiteName as 'Site Name', sys2.ReportingSiteCode as 'Parent Site Code',
(select CASE sys3.RoleName
WHEN 'SMS SQL Server' THEN 'SQL DB'
WHEN 'SMS Component Server' THEN NULL
WHEN 'SMS Distribution Point' THEN 'DP'
WHEN 'SMS Site Server' THEN NULL
WHEN 'SMS Site System' THEN NULL
WHEN 'SMS Management Point' THEN 'MP'
WHEN 'SMS Device Management Point' THEN 'DMP'
WHEN 'SMS System Health Validator' THEN 'SHV'
WHEN 'SMS State Migration Point' THEN 'SMP'
WHEN 'SMS Provider' THEN 'SMS Prov'
WHEN 'SMS Software Update Point' THEN 'SUP'
WHEN 'SMS Fallback Status Point' THEN 'FSP'
WHEN 'SMS Multicast Service Point' THEN 'MSP'
WHEN 'SMS AMT Service Point' THEN 'AMT'
WHEN 'AI Update Service Point' THEN 'AI'
WHEN 'SMS SRS Reporting Point' THEN 'SRS'
WHEN 'SMS Application Web Service' THEN 'APP WS'
WHEN 'SMS Portal Web Site' THEN 'Por WS'
WHEN 'SMS Enrollment Server' THEN 'ES'
WHEN 'SMS Enrollment Web Site' THEN 'EWS'
WHEN 'SMS Endpoint Protection Point' THEN 'EPP'
END + ', ' as 'data()'
from v_systemresourcelist as sys3
where (sys1.ServerName = sys3.ServerName) and ( NALPath not like '%SMSPXEIMAGES$%')
order by sys3.RoleName
for xml path('')) as 'Site Roles' from v_systemresourcelist as sys1
left join v_site as sys2 on sys2.ServerName=sys1.ServerName
left join v_gs_computer_system as sys4 on sys4.Name0 + '.' + sys4.Domain0=sys1.ServerName
left join v_gs_operating_system as sys5 on sys5.resourceid=sys4.resourceid
order by sys1.ServerName





—————————————————————————
 Ur’s -> SithaYuvaraj | SCCM Administrator
—————————————————————————

This posting is provided AS-IS with no warranties/guarantees and confers no rights. 

SCCM 2012 Report for All Active Content Replication with % Completed

Hi All,

We had a requirement to create a report to include the Package transfer details in SCCM 2012 Report.

In SCCM 2012, the logs are really magical and it captures more information then SCCM 2007

When you Open All Active Content Replication report you will come to know how many packages are in Que

But you need to open the status message or Package Transfer log to identify which package is actually getting transfered and how much has been completed.

I have created a report which will include this information in the All Active Content Replication Report which will become handy in your day to day tasks.

You can edit the Default report and click save as and copy paste the below code in the datasheet.

Feel free to let me know if you have any difficulty in getting this report.

SQL Query

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

select
pkg.Name as C062,
pkg.PackageID,
SUBSTRING(dp.ServerNALPath, CHARINDEX('\\', dp.ServerNALPath) + 2, CHARINDEX('"]', dp.ServerNALPath) - CHARINDEX('\\', dp.ServerNALPath) - 3 ) AS C070, 
dp.SiteCode,
dp.LastRefreshTime,
stat.SourceVersion,
stat.LastCopied,
stat.SummaryDate,
(select top 1 msg.InsString3
from v_StatMsgWithInsStrings msg
join v_StatMsgModuleNames modNames on msg.ModuleName = modNames.ModuleName
join v_StatMsgAttributes attpkg on msg.RecordID=attpkg.RecordID and msg.Time=attpkg.AttributeTime
join v_StatMsgAttributes attdp on msg.RecordID=attdp.RecordID and msg.Time=attdp.AttributeTime
where attpkg.AttributeValue =pkg.PackageID and msg.MessageID='8204'
and msg.InsString2 =SUBSTRING(dp.ServerNALPath, CHARINDEX('\\', dp.ServerNALPath) + 2, CHARINDEX('"]', dp.ServerNALPath) - CHARINDEX('\\', dp.ServerNALPath) - 3 )
order by msg.Time desc) as '% Completed',
stat.InstallStatus
from v_Package pkg
join v_DistributionPoint dp on pkg.PackageID=dp.PackageID
join v_PackageStatusDistPointsSumm stat on dp.ServerNALPath=stat.ServerNALPath
                                             and dp.PackageID=stat.PackageID
where stat.State!=0
order by pkg.Name, dp.SiteCode

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



—————————————————————————
 Ur’s -> SithaYuvaraj | SCCM Administrator
—————————————————————————

This posting is provided AS-IS with no warranties/guarantees and confers no rights.