Use Full SQL Tables / Views

UseFull SQL Tables/ Views	Description / Use
v_Add_Remove_Programs
v_Advertisement
v_AdvertisementInfo
v_ClientCollectionMembers
v_Collection
v_Collection
v_ConfigurationItems
v_DistributionPoint
v_DistributionPointGroup
v_GS_ADD_REMOVE_PROGRAMS
v_GS_ADD_REMOVE_PROGRAMS_64
v_GS_COMPUTER_SYSTEM
v_GS_DISK
v_GS_Memory_Details0
v_GS_OPERATING_SYSTEM
v_GS_PATCHSTATE
v_GS_SERVICE
v_GS_SoftwareProduct
v_GS_SYSTEM
v_GS_WORKSTATION_STATUS
v_GS_X86_PC_MEMORY
v_OS_Details
v_Package
v_PackageStatus
v_Program
v_Query
v_R_System
v_R_User
v_R_UserGroup
v_RA_System_IPAddresses
v_RA_System_IPSubnets
v_RA_System_IPXAddresses
v_RA_System_MACAddresses
v_RA_System_SMSAssignedSites
v_RA_System_SMSInstalledSites
v_RA_System_SystemContainerName
v_RA_System_SystemGroupName
v_RA_System_SystemOUName
v_RA_System_SystemRoles
v_Report
v_Site
v_SiteBoundary_ADSite
v_SiteBoundary_IPSubnet
v_StateNames
v_UpdateBundles
v_UpdateListStatus_Live
v_UpdatePrograms

R3 is RELEASED !!!!!!

 

http://blogs.technet.com/b/systemcenter/archive/2010/10/14/system-center-configuration-manager-2007-r3-unleashed.aspx

You must know these collections as handy : Collections End to End

1.Client all system collection

2.Non client systems

3.Inactive systems

4.Obsolete system

5.Duplicate client Yes or No

6. Last hardware inventory 14 days

7.Last software inventory 14 days

8.Last Data discovery cycle.

9. XYZ package, XYZ Advertisement success systems’ collection

10. XYZ package, XYZ Advertisement Failed systems’ collection

11.XYZ subnet collection system

12.Add XYZ system’s to a collection of Existing

13. All SMS server system collection

14. All windows server, workstation,DP,BDP collection

15. All system’s with AD site based

16. Collection limiting to sub collection, linking

17.System’s are in “A” collection But not in “B” Collection & Vice versa

18. In collection “XYZ” Software installed system

19. In collection “XYZ” File inventory(s\w inventory based) installed system

20. In collection “XYZ” file specific method(H\w inventory based) system

21.XYZ patch Installed & Not Installed system

22. All windows update Agent version 7.6 below

23.XYZ user/group collection

SQL Reporting Services: SRS

SQL Reporting services have some benefits over the current reporting solution:

• Provide best in class reporting capability by integrating SQL Reporting Services, with the leading change and configuration management product: SCCM 2007
• Enable Ad-hoc reporting - Make it easy for both SCCM administrators, and non administrators to find the information they need to make the right decisions for their business
• Support alternative databases as the reporting database, such as a replicated or backup database
• Allow custom report creation via a report authoring wizard.
• Enable report browsing and viewing via the SRS Report Viewer.
• Take advantage of rendering in all supported SRS formats, report caching, and subscriptions.

This article will explain setting up SRS with Configuration Manager based on SQL 2008. I’ve taken most of the steps from this article (many thanks to Michael Wiles). I’ve added some screenshots and applied it to SQL 2008, while the original article discusses SQL 2005. If you need more background info on SRS check out that link.

Install and configure SQL Reporting Services in SQL 2008

 

Be sure to check Reporting Services as part of your SQL installation

At step 21 of this part of this guide the setup provides you with three choices:

• Native mode default configuration
• SharePoint mode default configuration
• Unconfigured Reporting Services installation

You should select Unconfigured Reporting Services installation at this point.

After the installation finishes you should configure SRS for the use with Configuration Manager 2007.

1. In the start menu select Microsoft SQL Server 2008 / Configuration Tools, then click Reporting Services Configuration Manager.

2. In the Reporting Services Configuration Connection select the Server Name and the Report Server Instance, then select Connect.

3. In the Report Server Status verify that the Service Status is set to Running. If it is not, click Start, and then click Apply.

4. Select Web Service URL and make sure you specify the name you want to call the virtual directory created by Reporting Services (or use the default ReportServer). Optionally you can select the IP-address you want to use, the port and SSL setting. When done click Apply.

5. Select Database, select Create a new report server database in the Report Server Database Configuration Wizard and click Next

6. Select the Database Server Name and Authentication Type that matches your SQL server and credentials.

7. Supply the Database Name and Language. Be sure to select Native Mode for Report Server Mode. Click Next.

8. Select the proper credentials from the drop list for Authentication Type

9. In the Summary screen review your settings and Click Next.

10. Review your database in the Report Server Database screen.

11. Select Report Manager URL and make sure you specify the name you want to call the virtual directory created by Reporting Services (or use the default Reports). Optionally you can select the IP-address you want to use, the port and SSL setting. When done click Apply.

12. Email settings are not mandatory to setup, but can be configured appropiately if necessary.

13. Execution Account is necessary to configure if you will be running unattended reports. Optionally apply credentials and Click Apply.

20. Click Exit to close Reporting Services Configuration Manager.

Configure Configuration Manager 2007

SQL Reporting Services need the configuration of a Reporting Services Point role.

1. In the Configuration Manager console, navigate to System Center Configuration Manager / Site Database / Site Management / <site code>-<site name> / Site Settings / Site Systems.

2. In this guide I will add the reporting services point site role to the existing site system. Right-click the site system name, and click New Roles. On the General page of the New Site Role Wizard, configure the general settings for this site system, then click Next.

3. On the System Role Selection page of the wizard, select Reporting Services point (don’t select Reporting Point by mistake, which is the traditional reporting functionality), then click Next.

5. On the Reporting Services Point page, specify the folder that will be created on the report server to contain the SQL Reporting Services reports used in Configuration Manager and then click Next.

6. Review the information shown on the Summary page, then click Next.

7. Click Close to exit the wizard.

8. In the Configuration Manager console, navigate to System Center Configuration Manager / Site Database / Computer Management / Reporting / Reporting Services, and expand the node.

9. Right-click the reporting services point server you want to configure, then click Properties.

10. On the Data Source Settings tab of the Report Server Properties dialog box, specify the Configuration Manager 2007 database server and database name (not the one create earlier for reporting!) to be used as the data source for SQL Reporting Services reports. Click the test button to verify that you have correct data entered.

11. On the Data Source Authentication tab of the Report Server Properties dialog box, specify the credentials used to access reports on the report server. I’ve use Windows Integrated Security here.

12. On the Data Source Security tab of the Report Server Properties dialog box, specify permissions for the users who have access to the data source specified in the Data Source Settings tab.

13. On the Security tab of the Report Server Properties dialog box, specify the users who have access to the selected report server.

14. Click OK to close the Report Server Properties dialog box.

Import standard reports in Reporting Services

The last step in configuring SRS for Configuration Manager 2007 is to convert the standard reports to Reporting Services reports.

1. In the Configuration Manager console, navigate to System Center Configuration Manager / Site Database / Computer Management / Reporting / Reporting Services

2. Right-click the Reporting Services server that the standard reports will be copied to, then click Copy Reports to Reporting Services.

3. On the Data Source Settings page of the Copy Reports Wizard, specify the Configuration Manager 2007 database server and database name from which to copy the reports. Click Next.

4. On the Data Source Authentication page of the Copy Reports Wizard, choose the authentication method required to run the copied reports on the reporting point server. Click Next.

5. On the Select Reports page of the Copy Reports Wizard, select the reports you want to copy to the reporting services point. Select Overwrite existing reports if you want to overwrite any reports that already exist on the reporting services point. Click Next.

6. On the Security page of the Copy Reports Wizard, specify which users can access the copied reports and the roles they must belong to. For more information about the options on this page, see Report Server Properties: Security Tab. Click Next

7. On the Summary page of the Copy Reports Wizard, review the reports that will be created, and then click Next.

8. After the reports are copied, click Next.

9. On the Confirmation page of the Copy Reports Wizard, review the information, and then click Close to exit the wizard.

Reports are now available Site Database / Computer Management / Reporting / Reporting Services / <Site Server> / All Reports

To run a particular report just Right Click the report and select Run.

Popular and useful tools for ConfigMgr 2007

• SCCM Right Click Tools
• SCCM Client Center
• OSD++
• SMSMap
• SCCMAutoDoc
• SCCM 2007 SDK
• SCCM-Tools.com
• BitsAdmin
• WMI Administrative Tools
• WMI Diagnosis Utility

 

 

http://www.petervanderwoude.nl/post/The-best-(free)-tools-for-ConfigMgr-2007!.aspx

Systems Part of What Collections

--Systems Part of What Collections

SELECT v_R_System.Name0, v_Collection.Name FROM v_FullCollectionMembership INNER JOIN v_R_System ON v_FullCollectionMembership.ResourceID = v_R_System.ResourceID INNER JOIN v_Collection ON v_FullCollectionMembership.CollectionID = v_Collection.CollectionID WHERE (v_R_System.Name0 = 'Systemname')

For finding the 90 days old computers in the AD

 

 

For finding the 90 days old computers in the AD

DSQUERY COMPUTER "DC=MydomainName,DC=COM" -STALEPWD 90 -LIMIT 5000 >> C:\output.csv

for finding the 90 days old computers in the AD & action to delete with the output

DSQUERY COMPUTER "OU=COMPUTERS,DC=LANDESK,DC=COM" -STALEPWD 90 -LIMIT 5000 | DSRM -NOPROMPT >C:\Output.csv

Good Posting for Multicasting for OSD : End to End

you can find here http://tinyurl.com/289ho4y

Multicasting is a new feature of R2 in SCCM and is a welcome addition to the OSD feature.  Multicast allows for image deployment with a much reduced network load.  If, for example, you are deploying a 500 MB image to 20 workstations that have just arrived from the OEM then with normal OSD imaging you would see network traffic equal to around 10 Gig!  Using multicasting would result in a significant decrease in network utilization.  Depending on the configuration, as little as about 500 MB of traffic required to deploy to all 20 machines!  This is the same amount of network traffic you would consume deploying to just a single imaging system using typical OSD imaging before multicast!

How do you setup multicast?  Let's walk through the configuration that is required step by step.

First, we need to enable multicasting.  Multicast requires a distribution point and the 'transport server'  component of WDS installed on a Windows 2008 server.  From there select the properties of the distribution point site system role and make sure the option for BITS, HTTP and HTTPS transfer is selected as shown

Note the new multicast tab that is added when R2 is installed.  Select the multicast tab and enable multicasting.

There are two options when using multicast - autocast and scheduled multicast. 

To enable autocast just select to enable multicast.  In this configuration the multicast session starts as soon as the first machine powers up and requests the image.  As additional machines are also booted up for imaging they will 'join' the current multicast session already in progress and consume the remainder of the stream.  When the stream ends it will start again and the systems that joined late will consume the parts they missed.  While autocasting isn't as efficient as scheduled multicast it is still significantly more efficient that standard OSD image delivery.

Scheduled multicast allows for more control of the multicast session.  Here you choose either a time delay before starting the multicast session or a minimum number of clients that must join the session before it starts.  The multicast session will start whenever either of the two requirements are met.  The idea here is to allow the administrator time to get all of the systems started and ready and then all systems can load the image simultaneously - providing the best usage of network   resources. Scheduled multicast is enabled by selecting the 'enable scheduled multicast' check box.

There are other configuration options on this screen but no extra configuration is required unless by your network.  The 'out of the box' settings work fine for most environments.

In addition, while DHCP and Windows 2008 based WDS (transport component as noted earlier - the PXE boot piece does not need to be on the same server) are needed, there is no special configuration requirement for either to make use of multicasting.

The next step is to configure the image package to be deployed by multicast.  In properties of the imported image, select the 'distribution settings' tab as shown

Select the option to allow the package to be deployed by multicast and optionally select the other two options.  In my testing I tried to select to only allow the image to be transferred via multicast but this didn't seem to work.  When I disabled multicast the image would still deploy, even with this option set.

in addition to enabling multicast for the image, you can also enable multicast on any package that is part of the image deployment.  On each package select properties and then select the 'distribution settings' tab.  the same options are available here as were on the image package as shown

As shown on both the image and the package, multicast is something that takes place in Windows PE only.  So, setting these options per package will not result in the package being delivered via multicast through normal software distribution.

Finally, enable the task sequence advertisement to deliver the image via multicast.  On the properties of the advertisement select the 'distribution points' tab as shown.

To enable multicast MAKE SURE you have selected to 'download content locally when needed by running task sequence'.  This requirement isn't documented and I spent hours trying to understand why my multicast sessions weren't starting up before realizing this setting was required.

Thats it - we are now ready to go for delivering images via multicast.  Let's walk through a couple of scenarios using autocast and scheduled multicast.  In our scenarios we will use PXE booted systems but multicast also works fine when booting from media - the experience is just slightly different when in Windows PE.

Autocast - single machine
To test multicast generally you will use a single machine.  So, what is your indication that multicast is actually working?  When the image begins to deploy you will see that a multicast session is requested, the image is downloaded locally via multicast and then reassembled as shown in the following three screenshots.

Autocast - two machines
As mentioned, in autocast the first requesting machine will start the multicast as shown above.  Any subsequent machines that boot up during the existing multicast session will join in progress and then loop back to the start and request the initial bits again.  The screenshots below show two sysems - the one on the left was started ahead of the one on the right but both are downloading the same image.  The one on the right started mid-stream.

After downloading the image the one on the left proceeds to extract while the one on the right finishes up getting the image. 

Scheduled multicast works similarly except here the multicast session will not start until either time has expired or the minimum number of machines have joined the multicast session.  The screenshot below shows a system waiting for either more systems to join or the timeout to expire.

So, while this is all going on - what is happening in the background to make this all work?  The SMSTS log varies slightly in each scenario but the core details are the same.  The SMSTS log section below is from a machine participating in an autocast multicast session.

In the log snip below we see the imaging system flag the multicast enabled distribution point that it wants to use for the multicast session, construct details for the multicast session request and then send the request to the multicast point

Continuing in the log we see the request information submitted to the multicast point followed by a reply with information the imaging system needs to join the multicast session.  Once we get the proper information back from the multicast point we then request and establish the multicast session

Next we see the response from the multicast server, the session get setup and the download begin

Once downloaded we begin to reconstruct the wim file and start applying the image.

When initially setting up the multicast session we say reference to sending information to the multicast point.  If we look in the mcsisapi log we see the request received, processed and the resulting reply sent back to the imaging agent as shown

There is certainly more 'behind the scenes' detail that takes place but this give a good picture, end to end, of how to configure, use and understand the process of using multicasting in R2.

Discovery Mystery

Discovery Methods

Six methods of discovery are available in Configuration Manager 2007:

• Network Discovery
• Heartbeat Discovery----------------------------------------------------this must enabled in all sites
• Active Directory System Group Discovery
• Active Directory Security Group Discovery
• Active Directory System Discovery
• Active Directory User Discovery

 

As Configuration Manager 2007 discovers resources, it creates records in the Configuration Manager database. This record is called a data discovery record (DDR) and the file generated has a .DDR extension. The specific information contained in each record varies depending on the resource "discovered," but it can include data such as the NetBIOS name of a computer, IP address and IP subnet of a computer or device, operating system, MAC address, and so on.

Depending on the discovery method used, resource DDRs are periodically regenerated to keep the discovery data up to date in the database and to verify that the resource is still a valid resource within the Configuration Manager 2007 site.

 

Now these methods what will discover?.. below are the discover use of each

Active Directory System Group Discovery

Organizational unit

Global groups

Universal groups

Nested groups

Nonsecurity groups

Active Directory System Discovery

• Computer name
• Operating system
• Object class
• DNS Host name
• Domain

Active Directory User Discovery

• User name
• DNS host name
• Object class
• Active Directory domain
• Active Directory container name

Network Discovery

• NetBIOS name
• IP addresses
• Resource domain
• System roles
• SNMP community name
• MAC addresses

Heartbeat Discovery :- Heartbeat Discovery is active only on computers that have already been installed as Configuration Manager clients.

It is important to ensure that any schedule you create causes the DDRs to be updated frequently enough so that the original DDR isn't viewed by Configuration Manager as obsolete or deleted from the database.Heartbeat Discovery updates existing DDRs rather than creating new ones. By default, it generates an updated DDR for each client every seven days, although this timing is configurable.

Heartbeat Discovery runs on installed Configuration Manager clients according to the schedule you specify. With this method enabled, the Client Component Installation Manager (CCIM) on the client causes the Cliex32.dll to generate a DDR, which is then written to the management point. This file is the same size as a normal DDR (approximately 1 KB per client), and so it will generate approximately the same network traffic.

 

Active Directory Security Group Discovery

he Configuration Manager 2007 Active Directory Security Group Discovery method searches for security groups by polling the closest Active Directory domain controller. The Active Directory domain can be in mixed mode or native mode.

 

Discovery Troubleshooting Flowcharts http://technet.microsoft.com/en-us/library/bb735871.aspx

 

Log files related to discovery

Adsysdis.log Active Directory System Discovery log file showing when the discovery method runs, and its results. Look for the number of DDRs created and any "bogus" entries.

Adsysgrp.log Active Directory System Group Discovery log file showing when the discovery method runs, and its results. Look for the number of DDRs created.

Adusrdis.log Active Directory User Discovery log file showing when the discovery method runs, and its results. Look for the number of DDRs created.

 

DISCOVERY HAS A DEPENDENCE OF CLEINT PUSH, IF DISCOVERY IS NOT ENABLED OR NOT DISCOVERED ANY SYSTEMS THEN CLIENT PUSH WILL NOT PUSH ON ANY SYSTEM’S

Scripts:- http://technet.microsoft.com/en-us/library/cc180843.aspx

If you deleted any system from SCCM / SMS console you can initiate the client discovery data cycle then client can be reappear in the console

 

Third party Discovery Tools :- enhanced discovery tool

 

The major discovery drop backs in SCCM is it will not do a delta discovery it will do from scratch.. that means it will not discover specific to the changes that has changed from last cycle.. to accomplish this you need to depend on Enhanced discovery tool http://www.systemcentertools.com/esd.html 

now with SCCM R3 you can do delta discovery

SCCM Client Services and applets

If SMS/SCCM Client is installed we will get SMS Agent host (ccmexec.exe ) service will be installed all the related information log file is ccmexec.log file

if client installed it will come in Control Panel

 

if the client OS version is 32 bit then you can find the applets in the control panel directly, if it is 64 bit OS then you will get in 32-bit Control Panel items (The reason is sccm is a 32 bit application) also client will be installed in windows\ccmsetup for client installation

and for client logs in 64 located in

 

64 bit client log files location screenshot is below

 

SMS / SCCM console not connecting

Above is a example screenshot

There are 3 areas of consideration when troubleshooting access to the SMS provider and the site server. 

1. Do you have the necessary privileges to the SMS provider on your site server?

2. Do you have the necessary security rights to the database?

3. Do you have the necessary privileges as far as WBEM is concerned?

4. Is WBEM working? 

http://www.myitforum.com/articles/6/view.asp?id=250
http://support.microsoft.com/kb/317872

Verify that this computer has network connectivity to the SMS Provider

http://technet.microsoft.com/en-us/library/bb932213.aspx

 

 

Check the adminui.log and smsprov.log and smsdbmon.log

Installation & Configuration of SCCM2007 Secondary Site

1.     Add the Configuration Manager Secondary Site Server Computer account to the Group “SCCM-GROUP”. As mentioned in my previous article this group must be added on to the System Management Container Security tab with Full permissions in active directory users and computers snap in as shown below.

2.     Add the computer account of Configuration Manager Secondary site server to the Local Group “SMS_SiteToSiteConnection_INC” which is present on the primary Site server.

Procedure:

Logon to primary Site Server- Open Computer management drill down to Local Users and Groups, Select groups and in that we will find a Local Group named as “SMS_SiteToSiteConnection_INC” as shown below. Go to the properties page and add secondary site server computer account.

3.     Create a Sender Address with the Secondary Site Code and Primary Site Information.

Procedure:

Open SCCM2007 Primary Site Server management Console, drill down to Site Management-Site Code- Site Settings- Addresses.

Right Click on Addresses and create a new Standard Sender Address

On the new Standard Sender Address Wizard, enter the Destination Site Code that is the new site code which we will give at the time of Secondary Site server installation. In our case we will give INS.

Give the Site Server name that is the Secondary Site Server Name.

If you leave the Site address account empty, the sender will use Primary Site Computer account to establish communication between both the sites (Primary and Secondary). If you leave the default, Primary site computer account must have full permissions on the secondary site server.

We can also set a windows user account that is having full admin privileges on both the site servers.

In our case we will leave as it is to use the Primary site Computer account.

In our case we selected open from Sunday to Saturday and click next

Leave the defaults and click Finish, you can also modify these settings based on your organization needs.

Once done you will see the newly created connector as shown below.

4.     Check the remote installation permissions of the account with which we are installing SCCM2007.

As discussed previously, we are using the Site server system account to establish communication between 2 sites, make sure we add the SCCM-Group into the Local Administrators group on the Configuration Manager Secondary Site Server.

To verify these

Log onto the Secondary site server and add the SCCM-GROUP account in the Local Administrators group of SCCM2007 Secondary site server.

5.     The SCCM2007 Primary site server Computer account must have full admin privileges on the Secondary Site Server computer account.

6.     The Service account with which we are installing secondary site server must have local admin privileges on the Secondary Site Computer account.

7.     Install IIS 6.0 (IIS is used for Management Point and Fallback Status Point)

Installing secondary site is two methods one from SCCM Console and second one from direct media from secondary site. we will run with the second option.

 

On the installation Prerequisite Check windows select Secondary site and click ok

click OK

 

Click ok, and

Initiate the Configuration Manager 2007 installation

Click next on the available setup options page

 

 

Accept the License agreement and click next

 

 

On the site type windows select Secondary site and click next

 

Provide the installation directory for binary files and click next

On the Site Settings Windows Insert the site code which we entered at  the time of Sender creation and provide the site name

In our case site code is INS and Site Name is MacroALLY Secondary Site Server

 

On the Parent Site Settings window, enter the Primary site code, which are INC and the Parent Site Server Name. Click next to proceed

 

On the Update Prerequisite Components page leave the defaults and click next

On the Updated Prerequisites path, enter the path to the folder were SCCM will download the updates and put it. Click next

On the settings summary page review the settings, if any changes go back to modify or proceed further with installation by clicking next

Setup will evaluate your system and cross checks all the prerequisites once more, if the status is green proceed further by clicking Begin install.

Setup starts installing the Configuration Manager Secondary site server

Click Finish and restart the server

open the console  now after restart

And the Sender will be updated with the necessary information as shown below

 

Vise Versa in the Secondary site settings-Address, you will also see a sender address created for Primary server as shown below

Configuration Manager Secondary Site is installed Secondary Site Information is updated in the System management Container in AD which is shown below

How confirm that Secondary site is working /Primary to secondary communication is fine:

Verify the Log Files to find out weather Secondary site is communicating with primary server properly.

Log files to monitor

ConfigMgrSetup.Log : Located on the root of system drive (Secondary site server )

Sender.log: Located on the Secondary site server installation directory (c:\Program Files\Microsoft Configuration Manager\Logs\Sender. Log) used to track weather the site is sending site information to the primary parent site.

After ensuring there are no communication problems from the above 2 logs, go ahead to check the despoiler.log on the primary site server to ensure it has received the secondary site information and is processing the secondary site information.

Despoole.log: located on the Primary site server (c:\Program Files\Microsoft Configuration Manager\ Logs\Despool.log)

Hman.log: Logs actions related to the hierarchy structure of the sites, located on the primary server (c:\Program Files\Microsoft Configuration Manager\ Logs\HMan.log).

Sitecomp.log: Logs information related to secondary site information publishing to Active Directory along with the primary site information, Located on the primary Site Server (c:\Program Files\Microsoft Configuration Manager\ Logs\SiteComp.log)

With this you are done with Secondary site Server Installation.