February 25 2011

Create a SCCM collection that excludes members of another collection

To create a SCCM collection that excludes members of another collection, use the below query. In my example the collection ID is P000004C. This is useful when combining with the ‘limit collection’ function.

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 where SMS_R_System.ResourceId not in (select ResourceID from SMS_CM_RES_COLL_P000004C)
 



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

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

Category: SMS / SCCM | 1 Comment on Create a SCCM collection that excludes members of another collection
February 22 2011

Copy AD group membership from one group to another

Here is a good VB script for copying the group membership from one Active Directory group to another:
 
strSGroupDN = InputBox ("Enter the DN of Source Group" & VBCRLF &_
vbcrlf& _
vbcrlf& _
"e.g. CN=Source Group,OU=Users,DC=NWTraders,DC=com")
strDGroupDN = InputBox ("Enter the DN of Destination Group" & VBCRLF &_
vbcrlf& _
vbcrlf& _
"e.g. CN=Destination Group,OU=Users,DC=NWTraders,DC=com")
set dicSeenGroupMember = CreateObject("Scripting.Dictionary")
set objDGroup = GetObject("LDAP://" & strDGroupDN)
DisplayMembers "LDAP://" & strSGroupDN, dicSeenGroupMember
Function DisplayMembers (strGroupADsPath, dicSeenGroupMember)
set objGroup = GetObject(strGroupADsPath)
for each objMember In objGroup.Members
objDGroup.Add("LDAP://" & objMember.distinguishedName)
next
End Function
MsgBox "Group Members have been copied to Destination Group"

Quotation marks are not required when entering values into the input boxes, even if your DN has spaces in it.
 
 

Category: Scripting, Tools | LEAVE A COMMENT
February 18 2011

Lync 2010 Conversation Translator

There is a simple and great extension of the Lync 2010 client that can allow you to have a real-time conversation with someone in a completely different language.

For example, just by adding a few registry entries, you can have an entire Lync conversation with someone who doesn’t speak English and Lync will do the language translation back and forth.

There is more information here but basically you just need to add the following current user registry entries and a new window is added to the Lync client.


Windows Registry Editor Version 5.00

[HKEY_CURRENT_USERSoftwareMicrosoftCommunicatorContextPackages{54C2C31A-A291-4DFA-825A-18994EBE9877}]

"Name"="Conversation Translator"

"ExtensibilityApplicationType"=dword:00000000

"ExtensibilityWindowSize"=dword:00000001

"DefaultContextPackage"=dword:00000000

"InternalURL"="http://input.microsoft.com/translator/OCTranslatorTestPage.html"

"ExternalURL"="http://input.microsoft.com/translator/OCTranslatorTestPage.html"

[HKEY_CURRENT_USERSoftwareMicrosoftWindowsCurrentVersionInternet SettingsZoneMapDomainsmicrosoft.cominput]

"http"=dword:00000002

Fantastic add-on!!
 
 

Category: Geek, OCS / Lync | LEAVE A COMMENT
February 16 2011

Find versions of Lync clients

If you are running Lync Server 2010 and have previously used OCS, you may realize how hard it is to find the versions of Lync that your clients are running. It was very easy to do with OCS.

Stephen Olsen has written a fantastic tool that will show you the version each client is running along with a bunch of other useful information.


Find Lync Versions tool
Find Lync Versions tool


To find out more about this tool, visit his website http://www.stumper66.com/software/lync.html. Also download a copy of the tool from me – http://blog.danovich.com.au/wp-content/uploads/2011/02/FindLyncVersions.zip.
 
 

Category: OCS / Lync | 3 Comments on Find versions of Lync clients
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

 
 

Category: SMS / SCCM | LEAVE A COMMENT
February 3 2011

SCCM report to show all Apple devices (iPhone, iPod, iPad)

As part of the network discovery process, I noticed that it was picking up the iPhone and other Apple machines (Macs, iPads) that were on the corporate network either via being plugged in or on the wireless LAN.

Shortly afterwards, there was a requirement to report on all the Apple devices found in the environment. SCCM to the rescue.

After visiting the IEEE website (http://standards.ieee.org/develop/regauth/oui/public.html) and doing a search for ‘Apple’, I found the OUI (first 6 hex characters of the MAC address) that all Apple product would have (keep in mind some manufacturers subcontract component manufacture and others include registered firm OUIs in their products).

I then wrote a collection membership rule SQL query to find all the devices that have these OUIs as the start of their MAC address:

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 where SMS_R_System.MACAddresses like "58:55:CA%" or SMS_R_System.MACAddresses like "00:03:93%" or SMS_R_System.MACAddresses like "00:03:93%" or SMS_R_System.MACAddresses like "00:05:02%" or SMS_R_System.MACAddresses like "00:0A:27%" or SMS_R_System.MACAddresses like "00:0A:95%" or SMS_R_System.MACAddresses like "00:10:FA%" or SMS_R_System.MACAddresses like "00:11:24%" or SMS_R_System.MACAddresses like "00:14:51%" or SMS_R_System.MACAddresses like "00:16:CB%" or SMS_R_System.MACAddresses like "00:17:F2%" or SMS_R_System.MACAddresses like "00:19:E3%" or SMS_R_System.MACAddresses like "00:1B:63%" or SMS_R_System.MACAddresses like "00:1C:B3%" or SMS_R_System.MACAddresses like "00:1D:4F%" or SMS_R_System.MACAddresses like "00:1E:52%" or SMS_R_System.MACAddresses like "00:1E:C2%" or SMS_R_System.MACAddresses like "00:1F:5B%" or SMS_R_System.MACAddresses like "00:1F:71%" or SMS_R_System.MACAddresses like "00:1F:F3%" or SMS_R_System.MACAddresses like "00:21:E9%" or SMS_R_System.MACAddresses like "00:22:41%" or SMS_R_System.MACAddresses like "00:23:12%" or SMS_R_System.MACAddresses like "00:23:32%" or SMS_R_System.MACAddresses like "00:23:6C%" or SMS_R_System.MACAddresses like "00:23:DF%" or SMS_R_System.MACAddresses like "00:24:36%" or SMS_R_System.MACAddresses like "00:25:00%" or SMS_R_System.MACAddresses like "00:25:4B%" or SMS_R_System.MACAddresses like "00:25:BC%" or SMS_R_System.MACAddresses like "00:26:08%" or SMS_R_System.MACAddresses like "00:26:4A%" or SMS_R_System.MACAddresses like "00:26:B0%" or SMS_R_System.MACAddresses like "00:26:BB%" or SMS_R_System.MACAddresses like "00:30:65%" or SMS_R_System.MACAddresses like "00:50:E4%" or SMS_R_System.MACAddresses like "00:A0:3F%" or SMS_R_System.MACAddresses like "00:A0:40%" or SMS_R_System.MACAddresses like "04:1E:64%" or SMS_R_System.MACAddresses like "08:00:07%" or SMS_R_System.MACAddresses like "10:93:E9%" or SMS_R_System.MACAddresses like "10:9A:DD%" or SMS_R_System.MACAddresses like "18:E7:F4%" or SMS_R_System.MACAddresses like "24:AB:81%" or SMS_R_System.MACAddresses like "28:E7:CF%" or SMS_R_System.MACAddresses like "34:15:9E%" or SMS_R_System.MACAddresses like "3C:07:54%" or SMS_R_System.MACAddresses like "40:A6:D9%" or SMS_R_System.MACAddresses like "40:D3:2D%" or SMS_R_System.MACAddresses like "44:2A:60%" or SMS_R_System.MACAddresses like "58:1F:AA%" or SMS_R_System.MACAddresses like "58:55:CA%" or SMS_R_System.MACAddresses like "58:B0:35%" or SMS_R_System.MACAddresses like "5C:59:48%" or SMS_R_System.MACAddresses like "60:33:4B%" or SMS_R_System.MACAddresses like "60:FB:42%" or SMS_R_System.MACAddresses like "64:B9:E8%" or SMS_R_System.MACAddresses like "70:CD:60%" or SMS_R_System.MACAddresses like "78:CA:39%" or SMS_R_System.MACAddresses like "7C:6D:62%" or SMS_R_System.MACAddresses like "7C:C5:37%" or SMS_R_System.MACAddresses like "88:C6:63%" or SMS_R_System.MACAddresses like "8C:58:77%" or SMS_R_System.MACAddresses like "8C:7B:9D%" or SMS_R_System.MACAddresses like "90:27:E4%" or SMS_R_System.MACAddresses like "90:84:0D%" or SMS_R_System.MACAddresses like "98:03:D8%" or SMS_R_System.MACAddresses like "A4:67:06%" or SMS_R_System.MACAddresses like "B8:FF:61%" or SMS_R_System.MACAddresses like "C4:2C:03%" or SMS_R_System.MACAddresses like "C8:2A:14%" or SMS_R_System.MACAddresses like "C8:AA:CC%" or SMS_R_System.MACAddresses like "C8:BC:C8%" or SMS_R_System.MACAddresses like "CC:08:E0%" or SMS_R_System.MACAddresses like "D4:9A:20%" or SMS_R_System.MACAddresses like "D8:30:62%" or SMS_R_System.MACAddresses like "D8:A2:5E%" or SMS_R_System.MACAddresses like "DC:2B:61%" or SMS_R_System.MACAddresses like "E0:F8:47%" or SMS_R_System.MACAddresses like "E8:06:88%" or SMS_R_System.MACAddresses like "F0:B4:79%" or SMS_R_System.MACAddresses like "F8:1E:DF%"

I then had a collection that has all the devices that have Apple MACs. Then I simply created a report that shows all the members in this collection.

This report will display all of the Apple devices discovered on the network. This may include computers that have Apple devices connected (eg iPod touch) as their MAC addresses are recorded by SCCM.

Useful.
 
 

Category: SMS / SCCM | 5 Comments on SCCM report to show all Apple devices (iPhone, iPod, iPad)