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

Day to Day Operations SCCM / ConfigMgr 2007 Reports

I recommend below ConfigMgr Reports to be familiar and these might be useful in day to day operations

 

Asset Intelligence:-

License 03A - Count of licenses by license status
Hardware 07A - USB devices by manufacturer

Software 02C - Software by Category and Family

Software 01A - Summary of installed software in a specific collection

This report provides a summary of installed software ordered by the number of instances found from inventory.

Desired Configuration Management – Compliance

Summary compliance for a collection by computer

Hardware

Count physical disk configurations

Computers with low free disk space (less than specified MB free)

Computers with low free disk space (less than specified % free)

Count memory configurations

Computers with low memory (less than or equal to specified MB)

Computers with a specific amount of memory

Memory information for a specific computer

Network

Count IP addresses by subnet

IP - Information for a specific computer

NAP

List of NAP-capable and NAP-upgradable computers

List of Network Access Protection policies

Operating System

Windows Server computers

Computers with a specific operating system and service pack

Computers with a specific operating system

Count operating systems and service packs

Count operating system versions

Services - Computers running a specific service

SMS/SCCM Client

Count SMS client versions

Servers with no client Installed

Client Deployment Failure Report

Client Deployment Success Report

Client Deployment Status Details

Client Assignment Failure Details

Count clients assigned and installed for each site

Computers with a specific SMS client version

Count clients for each site

Computers with duplicate MAC addresses

Computers that may share the same SMS Unique ID

Clients that have not reported recently (Collection)

& Clients that have not reported recently (in a specified number of days)

Site TO Site Reports:

Sites by hierarchy with time of last site status update

Computers in a specific site
Status messages for a branch distribution point
Site system roles and servers for a specific site

Software Inventory

Software registered in Add Remove Programs for a specific collection
Computers with specific software registered in Add Remove Programs
Computers with a specific product
Count of all instances of software registered with Add or Remove Programs
Computers with a specific product name and version
Count computers with a specific filename

Software Distribution:-

Status of a specific advertisement
All advertisements for a specific collection
All advertisements
Distribution status of a specific package
All packages on a specific distribution point
All distribution points
All active package distributions

Patching:-

Compliance 1 - Overall Compliance
Compliance 4 - Deployment (per update)
Compliance 3 - Update list (per update)
Compliance 4 - Deployment (per update)
Management 1 - Updates required but not deployed
Management 2 - Updates in a deployment
States 1 - Enforcement states for a deployment
States 2 - Evaluation states for a deployment
Scan 1 - Last scan states by collection
Scan 2 - Last scan states by site

Auditing

All audit messages for a specific user
All Task Sequence Advertisements
Deployment status of all task sequence advertisements
Progress of a running task sequence
Progress of OS deployment task sequences
Status of all unknown computers
Computers for a specific user name
WOL

All sites that are enabled for Wake On LAN
All computers targeted for Wake On LAN activity
All objects pending wake-up activitiy

Customized reports:

All computers based on AD site:

SELECT DISTINCT SYS.Name0,SYS.AD_Site_Name0,

SYS.User_Domain0,SYS.User_Name0,

SYS.Operating_System_Name_and0

FROM v_R_System SYS

WHERE SYS.AD_Site_Name0 LIKE @variable

ORDER BY SYS.AD_Site_Name0, SYS.Name0

--Prompt Query

SELECT DISTINCT SYS.Name0,SYS.AD_Site_Name0,

SYS.User_Domain0,SYS.User_Name0,

SYS.Operating_System_Name_and0

FROM v_R_System SYS

WHERE SYS.AD_Site_Name0 LIKE @variable

ORDER BY SYS.AD_Site_Name0, SYS.Name0

Lins with reports =è Hardware - General - Computer information for a specific computer; below is the screenshot

Also I prefer one to have below custom reports

Compliance - Detailed list - specific collection

Computer Information - specific collection

Computer information for a collection of computers

Computers with specific software

Distribution Points in All DP Group

Missing Patches

Network settings

Network settings for a specific collection

Servers with SCCM Client version
All Workstation Patching compliance reports
All Desktops & Laptops model & make reports

FIND Collections and it’s Name in the console SQL Query

SELECT     Name, CollectionID
FROM         dbo.v_Collection

SQL Server 2008 Express Edition Limitations

SQL Server 2008 Express Edition is a fantastic option for those seeking the power of a full-featured relational database but with low performance requirements. It replaces the Microsoft Data Engine (MSDE) as the free version of SQL Server for application development and lightweight use. It remains free and retains the limitations of MSDE with respect to client connections and performance. It’s a great tool for developing and testing applications and extremely small implementations, but that’s about as far as you can run with it.

SQL Server 2008 Express has the following features and limitations:

• Limited to one CPU
• Limited to 1GB of RAM
• Database size limited to 4GB
• Does not offer database mirroring
• Does not offer log shipping
• Does not allow merge publication
• Does not offer Oracle replication
• Does not provide the Database Tuning Advisor, SQL Agent or SQL Profiler
• Does not offer SQL Server Analysis Services or Advanced Analytics

If you’re able to live within those limitations, SQL Server 2008 Express may be the best choice for you. It’s available for free download from the Microsoft website.
Another option for database developers is SQL Server 2008 Developer Edition. Unlike Express Edition, Developer Edition has no limitations on database size, performance characteristics or functionality. However, it’s only licensed for use by a single developer in a non-production development environment. Translated: you may use it for your own use in developing applications, but you may not allow others to access either the database or those applications. Before you put the applications into production, you must transfer the database to a fully licensed version of SQL Server.

Where Name0 in

Deleting Machines Directly From The SMS Database

To delete an individual machine from the SMS database using the SMS console it is necessary to create a collection using the direct membership rule wizard or base your new collection on a newly created Query. The first method is slow and can be time consuming, the second option just doubles your work and it the least desired method for most people.

The other automatic means in which you can remove or otherwise delete the machine(s) from the SMS database is to let the SMS_SQL_MONITOR service automatically remove the machine after it has reached its predefined Site maintenance task ‘Delete Aged Discovery Data’ and ‘Delete Aged Inventory History ‘ specifications if you have left it enabled. It is enabled by default and is set to 90 days. You can however change this to a more reasonable time period appropriate for your sites hierarchy if needed.

Below you will find a SQL query that will allow you to delete machines from the SMS database as the task “Delete Special” performs the removal process. It is important to note here that when you delete machines from a collection within the SMS console the machine is deleted from the database however the History table is not purged or removed. When you use the task Delete Special the machine is not only removed from the SMS database but the history for the machine is also deleted.

NOTE: You must uninstall the SMS client software to ensure that it does not report back in. If you are simply removing the machine name(s) from the SMS database because your site support staff has informed you that the machines were retired or re-imaged then this is not necessary. It is important to also note here that this is not a Microsoft supported means for deleting machines from the SMS database and should be used in a non production environment.

When you are done use the query in my earlier post entitled: ‘Searching Your SQL Database For A Specified Column String’ and search for the machine(s) that you just deleted to ensure that they have in fact been purged.

To delete multiple machines using the query that follows you can simply change the line that reads: Where Name0 = 'Machine_Name' To the following: Where Name0 in ('Machine_One', 'Machine_Two') as in the Deleting Multiple Machines SQL Query found at the end of this post.

• Deleting An Individual Machine SQL Query:

Insert DeletedMachines (SmsId)

Select IsNull(Sms_Unique_Identifier0,'')

From System_Disc

Where Name0 = 'Machine_Name'

And Sms_Unique_Identifier0 is not null

Delete System_Disc from System_Disc

Where Name0 = 'Machine_Name'

Delete System_Data from System_Data

Where Name0 = 'Machine_Name'

• Deleting Multiple Machines SQL Query:

Insert DeletedMachines (SmsId)

Select IsNull(Sms_Unique_Identifier0,'')

From System_Disc

Where Name0 in ('Machine_One', 'Machine_Two')

And Sms_Unique_Identifier0 is not null

Delete System_Disc from System_Disc

Where Name0 in ('Machine_One', 'Machine_Two')

Delete System_Data from System_Data

Where Name0 in ('Machine_One', 'Machine_Two')

how to move the Site Database in Microsoft System Center Configuration Manager 2007 from a computer

INTRODUCTION
This article describes how to move the Site Database in Microsoft System Center Configuration Manager 2007 from a computer that is running Microsoft SQL Server 2005 to another drive on the same computer, or to another computer that is running SQL Server 2005.

MORE INFORMATION
In certain situations, you may have to move the Site Database from a computer that is running SQL Server 2005 to another drive, or to another computer that is running SQL Server 2005. For example, the following situations may require that you move the Site Database:

•You experience hardware issues on the server that you currently use. Additionally, you do not consider the server to be reliable.
•You have to move the Site Database and the log file to a different volume because the database requires more space, or because you want to improve performance.
•The server that you currently use is leased. The lease on the server is scheduled to expire soon.
•New hardware standards have been developed and approved. You must upgrade the computer that is running SQL Server 2005 to the new hardware specifications.
SQL Server 2005 supports the following:

•You can move files and log files from one computer to another computer if both computers are running SQL Server 2005.
•You can move data files and log files from one instance of SQL Server 2005 to another instance of SQL Server 2005 if both instances are on the same computer.
•You can move data files and log files from one volume to another volume on a computer that is running SQL Server 2005.
For more information about these functionalities in SQL Server 2005, visit the following Microsoft Web site: http://technet.microsoft.com/en-us/library/ms203721.aspx

PREREQUISITES
Before you move the Site Database from a computer that is running SQL Server 2005 to another drive or another computer that is running SQL Server 2005, follow these steps:

1.Back up all the databases from their current locations. This includes the master database.
2.Verify that you have system administrator permissions on both the computers that are running SQL Server 2005.
3.Verify that you have configured the computer where you want to move the Site Database exactly like the computer that currently hosts the Site Database.
4.Verify that you know the name and the current location of the Site Database.
5.Stop the following Configuration Manager services on the Microsoft System Center Configuration Manager 2007 site server:
• SMS_EXECUTIVE Service
• SMS_SITE_COMPONENT_MANAGER Service
• SMS_SITE_SQL_BACKUP Service
• SMS_SITE_VSS_WRITER
Note: You can stop all of these services using the Preinst.exe utility by running the following command without the quotes:

"Preinst.exe /STOPSITE"

Preinst.exe is included with Microsoft System Center Configuration Manager 2007 Server and is located in the following path:

Drive:\Program Files\Microsoft Configuration Manager\bin\i386\00000409 (the last folder is dependent upon the language of the product, 00000409 is for the English version).

Note: If you are running the SMS Provider and the Site Database on the same SQL 2005 Server, and you are moving the Site Database to a new server, you will also need to modify the SMS Provider Configuration in order to move it as well, For more information about moving the SMS Provider in Microsoft System Center Configuration Manager 2007, visit the following Microsoft Web site: http://technet.microsoft.com/en-us/library/bb693923.aspx.

MOVING THE DATABASE
To move the Site Database from a computer that is running SQL Server 2005 to another drive or another computer that is running SQL Server 2005, follow these steps:

Step 1: Detach the database
1. On the computer that currently hosts the Site Database, click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

2. Click the appropriate values in the Server type list, in the Server name list, and in the Authentication list. Then, click Connect.

3. Expand the Databases folder, right-click the SMS_ folder, point to Tasks, and then click Detach. Note that the Detach command is visible only if the following conditions are true:

• You are a member of the sysadmin fixed server role.
• The server to which you are connected is running SQL Server 2005.

4. Verify the status of the Site Database. Note that to successfully detach the Site Database, the status in the Databases to detach box in the Status column must read: "The database is ready to be detached." Optionally, you can update statistics before the detach operation. To do this, select the check box under the Update Statistics column in the Databases to detach box.

5. To close any existing connections in the Site Database, select the check box under the Drop Connections column in the Databases to detach box.

6. Click OK. The database node of the detached Site Database disappears from the Databases folder.

7. After the Site Database is detached, copy the SMS_.mdf file and the SMS_.ldf file to the drive and path you want to move it to, or to a folder on the computer to which you want to move the Site Database.

Note: The following path is the default path of the SMS_.mdf file:

Drive :\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

The following path is the default path of the SMS_.ldf file:

Drive :\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Step 2: Attach the database

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

2. Click the appropriate values in the Server type list, in the Server name list, and in the Authentication list. Then, click Connect.

3. Right-click the Databases folder, and then click Attach. Note The Attach command is visible only if the following conditions are true:

•You are a member of the sysadmin fixed server role.
•The server to which you are connected is running SQL Server 2005.
4. In the Attach Databases dialog box, click Add to specify the database that you want to attach.

5. Locate and then click the SMS_.mdf file. Then, click OK.

Note The following path is the default path of the SMS_.mdf file: Drive :\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

6. On the View menu, click Refresh to view the database node of the attached Site Database.

Note: If you moved the Site Database files to another drive on the same computer, then your move is complete, and you can start the following services:

•SMS_EXECUTIVE Service
•SMS_SITE_COMPONENT_MANAGER Service
•SMS_SITE_SQL_BACKUP Service
•SMS_SITE_VSS_WRITER
Step 3: Update the database server name

If you moved the Site Database to another server, you need to run the Microsoft System Center Configuration Manager Setup Wizard on the Microsoft System Center Configuration Manager 2007 Server to modify the SQL Server configuration to specify the new SQL Server name.

1. Ensure the primary site server computer account has administrative privileges over the new site database server computer.

2. Close any open Configuration Manager console connections to the site server.

3. On the primary site server computer, use the hierarchy maintenance tool (Preinst.exe) to stop all site services with the following command: Preinst /stopsite.

4. On the primary site server computer, click Start, click All Programs, click Microsoft System Center, click Configuration Manager 2007, and click ConfigMgr Setup, or navigate to the .\bin\i386 directory of the Configuration Manager 2007 installation media and double-click Setup.exe.

5. Click Next on the Configuration Manager Setup Wizard Welcome page.

6. Click Perform site maintenance or reset this site on the Configuration Manager Setup Wizard Setup Options page.

7. Select Modify SQL Server configuration on the Configuration Manager Setup Wizard Site Maintenance page.

8. Enter the appropriate SQL Server name and instance (if applicable) for the new site database server as well as the site database name on the Configuration Manager Setup Wizard SQL Server Configuration page.

9. Configuration Manager Setup performs the SQL Server configuration process.

10. Restart the primary site server computer, and verify the site is functioning normally.

Note: If you also need to move the Software Update Services Database (SUSDB) you will need to stop IIS Admin Service, and Update Services Service and follow Steps 1 and 2 above to detach, then move the SUSDB.MDF and SUSDB.LDF files, then attach the SUSDB.MDF, in the new drive location or on the new SQL Server 2005.

Enjoy

Collection of Computers based on Users

Collection of Computers based on Users

 

SMS has a nice feature that allows you to create a collection based on users. That way when a users logs into a computer SMS will then run an advertisement for whatever computer they are on. The problem is maybe you want to have a collection of computers not the users. The worst flaw this the collection query I have created below and using collections based on users is that if a person logs in to another machine, for whatever reason, SMS will find them and push software to them.
That in mind, here is a collection that will take any OU group of users and create a collection of computers.


---------------------------------------
select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.UserName in (select UniqueUserName from SMS_R_User where UserOUName = "xxxx /SECURITY USERS & GROUPS" )
--------------------------------------
Windows User account and group discovery must be enabled and run on your server to use this.

How does this work. It first grabs the list of users. Then matches them to the comptuers that SMS currently sees them logged into. To change this to Last Logged in user you need to change the SMS_G_System_Computer_System to SMS_R_System.LastLogonUserName for computers. This will give you last logged in user.

"Limit to Collection" for all your computer Workstations or further limit it to based on departments

If you don't know what group then you can create a query of just (select UniqueUserName from SMS_R_User where UserOUName = "") click values until you have the value you need and paste it into the larger query.
-------------------
Thanks,
http://sccm07.blogspot.com/

Creating 1000 User Accounts

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

--Creating 1000 User Accounts

 

Set objRootDSE = GetObject("LDAP://rootDSE")
Set objContainer = GetObject("LDAP://cn=Users," & _
    objRootDSE.Get("defaultNamingContext"))

For i = 1 To 1000
    Set objLeaf = objContainer.Create("User", "cn=UserNo" & i)
    objLeaf.Put "sAMAccountName", "UserNo" & i
    objLeaf.SetInfo
Next

WScript.Echo "1000 Users created."

 

 

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Creating an Active Directory User Account-------------------------------------------------------------------------

 

Creates a user account in Active Directory. This script only creates the account, it does not enable it.

Set objOU = GetObject("LDAP://OU=management,dc=fabrikam,dc=com") Set objUser = objOU.Create("User", "cn=MyerKen") objUser.Put "sAMAccountName", "myerken" objUser.SetInfo

 

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

Creating a User, a Group, and an OU

 

Demonstration script that: 1) creates a new Active Directory organizational unit; 2) creates a new user account and new security group; and, 3) adds the new user as a member of that security group.

Set objDomain = GetObject("LDAP://dc=fabrikam,dc=com") Set objOU = objDomain.Create("organizationalUnit", "ou=Management") objOU.SetInfo   Set objOU = GetObject("LDAP://OU=Management,dc=fabrikam,dc=com") Set objUser = objOU.Create("User", "cn= AckermanPilar") objUser.Put "sAMAccountName", "AckermanPila" objUser.SetInfo   Set objOU = GetObject("LDAP://OU=Management,dc=fabrikam,dc=com") Set objGroup = objOU.Create("Group", "cn=atl-users") objGroup.Put "sAMAccountName", "atl-users" objGroup.SetInfo   objGroup.Add objUser.ADSPath

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

Deleting a User Account from Active Directory

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

Deletes the user account for MyerKen from the HR organizational unit in a hypothetical domain named fabrikam.com.

Set objOU = GetObject("LDAP://ou=hr,dc=fabrikam,dc=com") objOU.Delete "user", "cn=MyerKen"

Determining When an Account Expires

Returns the expiration date for a user account.

On Error Resume Next Set objUser = GetObject _   ("LDAP://cn=myerken,ou=management,dc=fabrikam,dc=com")   dtmAccountExpiration = objUser.AccountExpirationDate    If err.number = -2147467259 Or _  dtmAccountExpiration = "1/1/1970" Then   WScript.echo "No account expiration specified" Else   WScript.echo "Account expiration:" & _     objUser.AccountExpirationDate End If

 

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

Determining When a Password Expires

 

Determines the date when a user password will expire.

Const SEC_IN_DAY = 86400 Const ADS_UF_DONT_EXPIRE_PASSWD = &h10000   Set objUserLDAP = GetObject _   ("LDAP://CN=myerken,OU=management,DC=fabrikam,DC=com") intCurrentValue = objUserLDAP.Get("userAccountControl")   If intCurrentValue and ADS_UF_DONT_EXPIRE_PASSWD Then   wscript.echo "The password does not expire." Else   dtmValue = objUserLDAP.PasswordLastChanged    Wscript.echo "The password was last changed on " & _   DateValue(dtmValue) & " at " & TimeValue(dtmValue) & VbCrLf & _   "The difference between when the password was last set" & VbCrLf & _   "and today is " & int(now - dtmValue) & " days"   intTimeInterval = int(now - dtmValue)      Set objDomainNT = GetObject("WinNT://fabrikam")   intMaxPwdAge = objDomainNT.Get("MaxPasswordAge")   If intMaxPwdAge < 0 Then     WScript.Echo "The Maximum Password Age is set to 0 in the " & _       "domain. Therefore, the password does not expire."   Else     intMaxPwdAge = (intMaxPwdAge/SEC_IN_DAY)     Wscript.echo "The maximum password age is " & intMaxPwdAge & " days"     If intTimeInterval >= intMaxPwdAge Then       Wscript.echo "The password has expired."     Else       Wscript.echo "The password will expire on " & _       DateValue(dtmValue + intMaxPwdAge) & " (" & _       int((dtmValue + intMaxPwdAge) - now) & " days from today" & ")."     End If   End If End If

Determining When a Password was Last Set

Identifies the last time a user password was set.

Set objUser = GetObject _     ("LDAP://CN=myerken,OU=management,DC=Fabrikam,DC=com") dtmValue = objUser.PasswordLastChanged WScript.echo "pwdLastSet is: " & dtmValue

Determining User Account Status

Identifies whether a user account is enabled or disabled.

Set objUser = GetObject _   ("LDAP://cn=myerken,ou=management,dc=fabrikam,dc=com")   If objUser.AccountDisabled = FALSE Then       WScript.Echo "The account is enabled." Else       WScript.Echo "The account is disabled." End If

Determining When a User Account Expires

Reports the date that the MyerKen Active Directory user account expires.

On Error Resume Next Set objUser = GetObject _     ("LDAP://cn=MyerKen,ou=Management,dc=NA,dc=fabrikam,dc=com") dtmAccountExpiration = objUser.AccountExpirationDate    If Err.Number = -2147467259 Or dtmAccountExpiration = "1/1/1970" Then     WScript.Echo "No account expiration specified" Else     WScript.Echo "Account expiration: " & objUser.AccountExpirationDate End If

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

Moving a User Account

Moves a user account from one OU to another.

Set objOU = GetObject("LDAP://ou=sales,dc=na,dc=fabrikam,dc=com") objOU.MoveHere _     "LDAP://cn=BarrAdam,OU=hr,dc=na,dc=fabrikam,dc=com", vbNullString

 

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

Requiring a Password Change

Forces a user to change their password the next time they logon.

Set objUser = GetObject _     ("LDAP://CN=myerken,OU=management,DC=Fabrikam,DC=com") objUser.Put "pwdLastSet", 0 objUser.SetInfo

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

Retrieving User Account Account Properties

Retrieves user account attributes found on the Account page of the user account object in Active Directory Users and Computers.

 On Error Resume Next Set objUser = GetObject _   ("LDAP://cn=myerken,ou=management,dc=fabrikam,dc=com") objUser.GetInfo   strUserPrincipalName = objUser.Get("userPrincipalName") strSAMAccountName = objUser.Get("sAMAccountName") strUserWorkstations = objUser.Get("userWorkstations")   Set objDomain = GetObject("LDAP://dc=fabrikam,dc=com") objDomain.GetInfoEx Array("dc"), 0 strDC = objDomain.Get("dc")   WScript.echo "userPrincipalName: " & strUserPrincipalName WScript.echo "sAMAccountName: " & strSAMAccountName WScript.echo "UserWorkstations: " & strUserWorkstations WScript.echo "dc: " & strDC

 

 

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

Unlocking an Active Directory User Account

Unlocks the MyerKen Active Directory user account.

Set objUser = GetObject _     ("LDAP://cn=MyerKen,ou=Management,dc=NA,dc=fabrikam,dc=com") objUser.IsAccountLocked = False objUser.SetInfo

 

 

 

Enjoy,
http://sccm07.blogspot.com/

Using VBScript to PLAY with Active Directory

Using VBScript to Export All Objects in the Marketing OU

In this example, you use a text editor such as Notepad to create a VBScript program. The script searches the Marketing OU and creates a text file that lists all of the user objects and a subset of their attributes.

To create the export script

1. Copy the following text into your text editor:

   'Global variables  Dim oContainer  Dim OutPutFile  Dim FileSystem  'Initialize global variables  Set FileSystem = WScript.CreateObject("Scripting.FileSystemObject")  Set OutPutFile = FileSystem.CreateTextFile("marketing.txt", True)  SetoContainer=GetObject("LDAP://OU=marketing,DC=reskit,DC=com")  'Enumerate Container  EnumerateUsers oContainer  'Clean up  OutPutFile.Close  Set FileSystem = Nothing  Set oContainer = Nothing  WScript.Echo "Finished"  WScript.Quit(0)  Sub EnumerateUsers(oCont)  Dim oUser  For Each oUser In oCont  Select Case LCase(oUser.Class)  Case "user"  If Not IsEmpty(oUser.distinguishedName) Then  OutPutFile.WriteLine "dn: " & oUser.distinguishedName  End If   If Not IsEmpty(oUser.name) Then   OutPutFile.WriteLine "name: " & oUser.Get ("name")  End If  'need to do this because oUser.name would get back the Relative   Distinguished name (i.e. CN=Jo Brown)  If Not IsEmpty(oUser.st) Then   OutPutFile.WriteLine "st: " & oUser.st  End If  If Not IsEmpty(oUser.streetAddress) Then   OutPutFile.WriteLine "streetAddress: " & oUser.streetAddress  End If  Case "organizationalunit" , "container"  EnumerateUsers oUser  End Select  OutPutFile.WriteLine   Next  End Sub 

2. Save the file as Export.vbs.

3. At the command prompt type export.vbs and press Enter. This creates a file named Marketing.txt, which contains a list of users and some of their attributes, such as distinguished name, name, state, and street address.

With appropriate modification, this script can be used with any application that supports COM and Visual Basic technologies. Such applications include Microsoft Visual Basic, Microsoft Excel, and Microsoft Access. Scripting can also be hosted by Internet Explorer and Internet Information Services 5.0, which is part of Windows 2000 Server.

Using VBScript to Modify All Objects in the Marketing OU

In this example, the Marketing organization has moved to a new office address. A simple VBScript program is used to perform a batch modification for all user objects in the Marketing organization. The script alters the state, street, locality, and postal code attributes.

1. Copy the following text into your text editor:

   Dim  oContainer Set  oContainer=GetObject("LDAP://  OU=marketing,DC=reskit,DC=com")  ModifyUsers oContainer  'cleanup  Set oContainer = Nothing  WScript.Echo "Finished"  Sub ModifyUsers(oObject)  Dim oUser oObject.Filter = Array("user") For Each oUser in oObject oUser.Put "st","New York" oUser.Put "streetAddress","825 Eighth Avenue" oUser.Put "postalCode","10019" oUser.Put "l","New York" oUser.SetInfo Next  End Sub 

2. Save the file as Modify.vbs.

3. At the command prompt, type modify.vbs and press Enter. This processes all objects in the Marketing organizational unit and modifies all users, altering the state, street address, postal code, and locality attributes.

Using VBScript to Create a User Object in the Marketing OU

In this example, you use VBScript to add a new user to the Marketing organization. This example illustrates how easy it is to use ADSI and VBScript to programmatically access the directory. Note that in this example, only a limited set of attributes are configured during the user creation.

To create the script and add the user

1. Copy the following text into your text editor:

   Dim oContainer 'Parent container  of new   user Dim  oUser 'Created user  'Get parentcontainerSetoContainer=GetObject("LDAP://OU=marketing,  DC=reskit,DC=com")  'Create user  Set oUser = oContainer.Create("User","CN=Jo Brown")  'Assign properties values to user  oUser.Put "samAccountName","Jo"  oUser.Put "givenName","Jo"  oUser.Put "sn","Brown"  oUser.Put "userPrincipalName","jo@reskit.com"  oUser.SetInfo  'Clean up  Set oUser = Nothing  Set oContainer = Nothing  WScript.Echo "Finished" 

2. Save the file as Adduser.vbs.

3. At the command prompt, type adduser.vbs and press Enter. This creates a new user named Jo Brown in the Marketing OU.

Using VBScript to Delete a User

In this example, you use VBScript to delete a user from the Marketing organization.

1. Copy the following text into your text editor:

   Dim oContainer 'Parent container of object to be  deleted 'Get parent  container Set  oContainer=GetObject("LDAP://OU=marketing,  DC=reskit,DC=com")  'Delete user  oContainer.Delete "user","CN=Jo Brown"  'Clean up  Set oContainer = Nothing  WScript.Echo "Finished" 

2. Save the file as Deluser.vbs.

3. At the command prompt, type deluser.vbs and press Enter. This deletes the user Jo Brown from the Marketing OU.
   -------------------
   Thanks,
   http://sccm07.blogspot.com/
   

Enable File and Printer Sharing Through Windows Firewall

Set objFirewall = CreateObject("HNetCfg.FwMgr")
Set objPolicy = objFirewall.LocalPolicy.CurrentProfile

Set colServices = objPolicy.Services
Set objService = colServices.Item(0)
objService.Enabled = TRUE

 

-------------------
Thanks,
http://sccm07.blogspot.com/

Excelent Artical for WMI Troubleshooting Tips

WMI Troubleshooting Tips

 

 

http://blogs.technet.com/configmgrteam/archive/2009/05/08/wmi-troubleshooting-tips.aspx

-------------------
Thanks,
http://sccm07.blogspot.com/

Feature Comparison of MSDE and SQL Server 2005 Express

Feature Comparison of MSDE and SQL Server 2005 Express

While the core database capabilities of MSDE and SQL Server 2005 Express are similar, the set of features and database capabilities of MSDE and SQL Server 2005 Express are different. The following table provides a feature comparison between MSDE and SQL Server 2005 Express.

Feature	MSDE	SQL Server 2005 Express Edition
Maximum number of instances	16	16
Maximum # of processors	2	1
Maximum RAM	2 GB	1 GB
Maximum database size	2 GB	4 GB
Workload governor	Yes	No
Graphical management tool	No	Yes
User instances	No	Yes
SQL Agent	Yes	No
DTS runtime	Yes	Yes (Web download)
Replication	Merge only	Merge subscription Snapshot subscription Transactional subscription
BI features (Analysis Services, Integration Services)	No	No
Report Server	No	Yes (Installed with SQL Server 2005 Express with Advanced Services)
Service Broker	No	Client only
Full-text search	No	Yes (Installed with SQL Server 2005 Express with Advanced Services)
Windows 9x support	Yes	No
MDAC Required	Yes	No
Business Intelligence Development Studio (BIDS)	No	Yes (Installed with SQL Server 2005 Express Toolkit)

-------------------
Thanks,
http://sccm07.blogspot.com/

SCCM 2007 with SQL Server 2005 Reporting Services

 

SCCM 2007 with SQL Server 2005 Reporting Services

 

Listen to Adam's podcast on these rules with Greg Low

Duration: 50 minutes 39 seconds
Size: 14.5MB

Watch Adam's video on DNRTV on these rules:

Part A (53:13) - 181MB
Part B (49:12) - 171MB
Part C (42:19) - 158MB

 

Complete covarage can be found in the http://www.ssw.com.au/Ssw/Standards/Rules/RulesToBetterSQLReportingServices.aspx 

-------------------
Thanks,
http://sccm07.blogspot.com/

SQL Tables For accurate licence info

For accurate licence

 

 

select * from V_R_System_valid

select * from dbo.v_LU_MSProd

Select * from dbo.v_GS_INSTALLED_SOFTWARE

-------------------
Thanks,
http://sccm07.blogspot.com/

FIND SQL Server Version with SQL Query

 

 

SELECT LTRIM(RIGHT(LEFT(@@VERSION,38),9))
SELECT 'SQL Server '    

+ CAST(SERVERPROPERTY('productversion') AS VARCHAR) + ' - '    

+ CAST(SERVERPROPERTY('productlevel') AS VARCHAR) + ' ('    

+ CAST(SERVERPROPERTY('edition') AS VARCHAR) + ')'

-------------------
Thanks,
http://sccm07.blogspot.com/

WQL to SQL Query

WQL to SQL

Want to see What is the query????????

 

This SQL Query can be used to see how WQL is converted to SQL by SMS for collections

 

Select

Collections

.SiteID

,

Collections.CollectionName

,

Collection_Rules.QueryName

,

Collection_Rules_SQL.WQL

,

Collection_Rules_SQL.SQL

From

Collections,Collection_Rules, Collection_Rules_SQL

Where

Collections

.CollectionID = Collection_Rules.CollectionID

AND

Collections.CollectionID = Collection_Rules_SQL.CollectionID-------------------
Thanks,
http://sccm07.blogspot.com/

SCCM Query for SCCM Clients Model and Manufactures Name

Need to extract SCCM Clients Model and Manufactures Name?

 

You have to dig the Computer_System_DATA Table. Here are two shared SQL Querys.

 

Select DISTINCT Model0 from Computer_System_DATA

The above one will show all the Models

The below Query will show everything in the Database

Select

* from Computer_system_data

-------------------
Thanks,
http://sccm07.blogspot.com/

SCCM 2007 / SMS 2003 SQL Package Related Query

-----------------------------------------------------------------------------------------------------------------------------------
SQL Query:-->
-----------------------------------------------------------------------------------------------------------------------------------
To know the package Name and ID

SELECT     PackageID, Name
FROM         dbo.v_Package
-----------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------
SQL Query:-->

If you want to filter with the package ID then modify the Query to

SELECT     PackageID, Name
FROM         dbo.v_Package
WHERE     (PackageID = 'YourPKGID')

-----------------------------------------------------------------------------------------------------------------------------------
-------------------
Thanks,
http://sccm07.blogspot.com/

Troubleshooting SMS 2003 MP and SLP

Troubleshooting SMS 2003 MP and SLP

This article provides methods to verify the operation of Management Points and Server Locator Points in Microsoft SMS 2003

Test a management point is functioning correctly:

• http://MP_Server/sms_mp/.sms_aut?mplist - This returns a blank screen if the MP is working correctly.
• http://MP_Server/sms_mp/.sms_aut?mpcert - This returns a hex string if the MP is working correctly.
• Use MPGetPolicy from the 'SMS 2003 Toolkit 2' to connect to the remote Management Point and verify policy is being retrieved correctly

Test a Server Locator Point is functioning correctly:

• http://SLP_Server/sms_slp/slp.dll - This returns 'Bad Query String!' if the SLP is functioning correctly
• http://SLP_Server/sms_slp/slp.dll?site&IP=%IPSubnet% - This returns XML output showing the relevant site code if successful
• http://SLP_Server/sms_slp/slp.dll?site&AD=%ADSite% - This returns XML output showing the relevant site code if successful
  
  

Thanks,
http://sccm07.blogspot.com/

Color Coder and Syntax Highligher for Transact SQL

Yep to change the coloring in SQL simple link just past the code and get the output of your SQL color code

 

Type or paste your Transct SQL code in the text box and click on the submit button to create a color coded and syntax highlighted HTML version of the SQL.

 

This HTML is suitable for use in reports, documentation etc.

 

http://www.brettb.com/TransactSQLColorCoder.asp