:::: MENU ::::
Monthly Archives: August 2017

Using Machine – OutPut :-User Name,AD site code,AD discoverd date,Active Client,Client Status,Obsolete Client,HW SCAN DIFFERENCE

SELECT Name0 AS [Machine Name], User_Name0 AS [User Name],AD_site_name0 as [AD site code] ,creation_date0 as [AD discoverd date],
CASE Active0 WHEN ‘0’ THEN ‘InActive’ WHEN ‘1’ THEN ‘Active’ ELSE ‘Unknown’ END AS [Active Client],

CASE Client0 WHEN ‘0’ THEN ‘No’ WHEN ‘1’ THEN ‘Yes’ ELSE ‘Unknown’ END AS [Client Status],

CASE obsolete0 WHEN ‘0’ THEN ‘No’ WHEN ‘1’ THEN ‘Yes’ ELSE ‘Unknown’ END AS [Obsolete Client], DATEDIFF(D,V_GS_WORKSTATION_STATUS.LASTHWSCAN,GETDATE()) AS [HW SCAN DIFFERENCE]

FROM v_R_System LEFT JOIN V_GS_WORKSTATION_STATUS ON V_R_SYSTEM.RESOURCEID = V_GS_WORKSTATION_STATUS.RESOURCEID

WHERE (Name0 IN (‘XXXXX’,’SSS’))


SQL LeftJoin Template

Select Name, [Disk_Space] from

(Select * From V_FullcollectionMembership where CollectionID = ‘ XXXX’ ) AllMachines

LeftJoin
( XXXXXXX  ) DiskSpace

On AllMachines.Name=Diskspace.[Computer Name]

____________________________________________________________
Example :—
______

select Name, [DISK_SPACE]
from
(select *
from v_FullCollectionMembership
where collectionID = ‘XXX00BB5’) allMachines
left join
(SELECT DISTINCT
v_R_System.Netbios_Name0 AS [Computer Name], v_R_System.User_Name0 as [ User name],
DisplayName AS [Baseline Item],
v_StateNames.StateName AS [Baseline Status],

case v_StateNames.StateName when ‘Compliant’ then ‘Yes’ when ‘Non-Compliant’ then ‘No’ when ‘Unknown’ then ‘Unknown’ when ‘Error’ then ‘Error’ ELSE v_StateNames.StateName END AS [DISK_SPACE]

FROM   dbo.v_CICurrentComplianceStatus INNER JOIN
dbo.v_CIAssignmentToCI ON v_CICurrentComplianceStatus.CI_ID = v_CIAssignmentToCI.CI_ID INNER JOIN
dbo.v_CIAssignment ON v_CIAssignmentToCI.AssignmentID = v_CIAssignment.AssignmentID INNER JOIN
dbo.v_LocalizedCIProperties ON v_CICurrentComplianceStatus.CI_ID = v_LocalizedCIProperties.CI_ID INNER JOIN
dbo.v_StateNames ON v_CICurrentComplianceStatus.ComplianceState = v_StateNames.StateID INNER JOIN
dbo.v_R_System ON v_CICurrentComplianceStatus.ResourceID = v_R_System.ResourceID INNER JOIN
dbo.v_FullCollectionMembership ON dbo.v_R_System.ResourceID = dbo.v_FullCollectionMembership.ResourceID
WHERE (v_StateNames.TopicType = 401) AND (v_CIAssignment.CollectionID = ‘XXX00B86’)
AND (dbo.v_FullCollectionMembership.CollectionID = ‘XXX00B86’)
and ( dbo.v_LocalizedCIProperties.DisplayName = ‘Machines With Low Disk Space (Less Than 10GB)’)
) DiskSpace
on allMachines.Name = DiskSpace.[Computer Name]


SCCM ConfigMgr 1610 Recover from Regular SCCM Maintenance Backup Folder

Unlike Old other version of SCCM it’s not supported recover wizard from SCCM software media SCCM Site recover wizard. We have to Drill down, SCCM Backup Folder CD>LATEST then launch the setup.hta for recover the SCCM Site. Below are the step by step details.

So its for same if you want to move your SCCM system to New Box or Crashed your SCCM server you want to recover from SCCM Backup Scenarios.

 

Assuming SCCM Server Name = MYSCCM

 

  • Install the Operating system server OS
  • Change the System Name to “MYSCCMNew”
  • Join into Domain
  • Install the Pre-requirements like IIS,SQL,ADK,features(BITS, remote deferential ),etc
  • Copy all data from your MYSCCM to MYSCCMNEW with similar Folder structure with same permissions better to use Robocopy.(Folder structure it includes the SCCM Installation Drive)
  • Once you copy all data includes your SCCM Maintenance Task Scheduler back (which includes CD.LATEST) change the system name to MYSCCM just like old SCCM Server. At that time make you sleep the OLD SCCM server to Avoid the duplicate machine record
  • Then launch the setup.hta from backup folder and follow the regular wizards
  • You can able to recover the TS, Apps, Collections all your settings.
  • But once you recovered the Site again try to run the AD Schema Extension to be on safe side.
  • And update all Applications, Packages ,Boot files,etc
  • You may face Image certificate issue, Please re issue the certificate to work includes pxe and create new offline media