My Neat SCCM 2007 Report for Hardware Inventory for a Collection.

Hey, at least I think it's neat.

I'm sharing it because it does some SQL-fu (that honestly I don't remember where I found it) that takes the multi-line values for pieces of hardware or information that have multiple instances and comma-separates them on the same line, rather than having multiple rows for a single machine.

I also think it filters out IPv6 addresses and differentiates between Wired and Wireless Mac addresses (or, at least, it tries to).

Give it a whirl.

If it doesn't work for you, tweak it until it does. Or steal pieces from it. Whatever you want to do.

SQL Query:
----------------------------------------------------------------------------------------------
select distinct
v_R_System.Netbios_Name0 as 'Computer Name',
v_GS_NETWORK_ADAPTER_CONFIGUR.DHCPEnabled0,
 REPLACE(
               (  SELECT MACAddress0 + ', '
                    FROM v_GS_NETWORK_ADAPTER as nic
                   WHERE nic.resourceID = v_r_system.resourceID
  and nic.description0 not like '%Wireless%'
                   and nic.adaptertype0 = 'Ethernet 802.3'
  and nic.description0 not like '%Virtual Adapter%'
  and nic.description0 not like '%miniport%'
  and nic.description0 not like '%1394%'
  and nic.macaddress0 is not null
                ORDER BY MACAddress0
                     FOR XML PATH('') )+'..',
               ', ..',
               '')                                     as [Wired MAC Addresses],
REPLACE(
               (  SELECT MACAddress0 + ', '
                    FROM v_GS_NETWORK_ADAPTER as nic
                   WHERE nic.resourceID = v_r_system.resourceID
  and nic.description0 like '%Wireless%'
          and macaddress0 is not null
                ORDER BY MACAddress0
                     FOR XML PATH('') )+'..',
               ', ..',
               '')                                     as [Wireless MAC Addresses],
REPLACE(
               (  SELECT IP_Addresses0 + ', '
                    FROM dbo.v_RA_System_IPAddresses as ip
                   WHERE ip.resourceID = v_r_system.resourceID
  AND ip.IP_Addresses0 LIKE '%.%'
              AND ip.IP_Addresses0 <> '0.0.0.0'
                ORDER BY ip_Addresses0
                     FOR XML PATH('') )+'..',
      ', ..',
      '')
               as [IPv4  Addresses],

 REPLACE((  SELECT CAST( Size0 as varchar(7) ) + ', '
                    FROM dbo.v_GS_DISK as disk
                   WHERE disk.resourceID = v_r_system.resourceID
                ORDER BY size0
                     FOR XML PATH('') )+'..',
               ', ..',
               '')                                    as [Hard Disk Sizes in MB],

v_GS_PC_BIOS.SerialNumber0,
v_GS_COMPUTER_SYSTEM.Manufacturer0,
v_GS_COMPUTER_SYSTEM.Model0,
v_GS_PROCESSOR.Name0 as 'Processor Description',
v_GS_PROCESSOR.addresswidth0 as 'OS Type',
/*v_GS_DISK.Size0 as 'Hard Disk Size in MB', */
v_GS_OPERATING_SYSTEM.Caption0 as 'Operating System',
v_GS_OPERATING_SYSTEM.CSDVersion0 as 'OS Subversion',
v_GS_PC_BIOS.SoftwareElementID0 as 'BIOS Description',
v_GS_PC_BIOS.SMBIOSBIOSVersion0,
v_GS_X86_PC_MEMORY.TotalPhysicalMemory0/1024 As C083,
v_GS_WORKSTATION_STATUS.LastHWScan,
v_r_system.resourceid

from v_R_System
inner join v_GS_NETWORK_ADAPTER_CONFIGUR on v_GS_NETWORK_ADAPTER_CONFIGUR.ResourceID = v_R_system.ResourceID
inner join v_GS_NETWORK_ADAPTER on v_GS_NETWORK_ADAPTER.ResourceID = v_R_system.ResourceID

/*inner join v_RA_System_MACAddresses on mac.ResourceID = v_R_System.ResourceId
inner join v_RA_System_IPAddresses on ip.ResourceID = v_R_System.ResourceId */
inner join v_GS_PC_BIOS on v_GS_PC_BIOS.ResourceID = v_R_System.ResourceId
inner join v_GS_COMPUTER_SYSTEM on v_GS_COMPUTER_SYSTEM.ResourceID = v_R_System.ResourceId
inner join v_GS_OPERATING_SYSTEM on v_GS_OPERATING_SYSTEM.ResourceID = v_R_System.ResourceId
inner join v_GS_X86_PC_MEMORY on v_GS_X86_PC_MEMORY.ResourceID = v_R_System.ResourceId
inner join v_GS_PROCESSOR on v_GS_PROCESSOR.ResourceID = v_R_System.ResourceId
inner join v_GS_WORKSTATION_STATUS on v_GS_WORKSTATION_STATUS.ResourceID = v_R_System.ResourceId
inner join v_GS_DISK on v_GS_DISK.ResourceID =v_R_System.ResourceId
inner join v_FullCollectionMembership on v_FullCollectionMembership.Name = v_R_System.Name0
WHERE v_FullCollectionMembership.CollectionID = @ID
and v_GS_NETWORK_ADAPTER_CONFIGUR.IPAddress0 != '0.0.0.0'
and v_GS_NETWORK_ADAPTER.AdapterType0 = 'Ethernet 802.3'
and v_GS_NETWORK_ADAPTER_CONFIGUR.DefaultIPGateway0 is not NULL
and v_GS_NETWORK_ADAPTER.Manufacturer0 != 'Microsoft'
and v_GS_NETWORK_ADAPTER.Manufacturer0 != 'Deterministic Networks'
and v_GS_NETWORK_ADAPTER.Name0 != 'Intel Wireless Connection Agent Miniport'
and v_GS_NETWORK_ADAPTER.Name0 != 'Odyssey Network Services Miniport'
and v_GS_NETWORK_ADAPTER.Name0 != 'Cisco Systems VPN Adapter'
and v_GS_NETWORK_ADAPTER.Name0 != 'ZRTP Miniport'
and v_GS_NETWORK_ADAPTER.Name0 != 'Nortel IPSECSHM Adapter'
and v_GS_NETWORK_ADAPTER.Name0 not like 'Bluetooth'

------------------------------------------------------------------------------
prompt info
----------------------------------------------------------------------------------

Name: ID
Prompt Text: Please select a collection
Provide a SQL Statement:

begin
if (@__filterwildcard = '')
SELECT DISTINCT CollectionID, Name FROM v_Collection
ORDER BY Name
else  SELECT DISTINCT CollectionID, Name
FROM v_Collection
WHERE CollectionID like @__filterwildcard ORDER BY Name
end

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

Outsmarting 32-bit redirection on 64-bit Systems via SCCM 2007

I've encountered this a couple of times in my travels, and I thought it was worth mentioning.

When deploying scripts that modify the File System and Registry to 64-bit hosts using SCCM 2007, the built-in File System Redirector can cause some unexpected behavior:

http://msdn.microsoft.com/en-us/library/windows/desktop/aa384187(v=vs.85).aspx

This behavior pops up mostly when dealing with the registry and %ProgramFiles% location on 64-bit machines.

Recently, I was asked to deploy a registry key change to a number of machines to re-write a set of ODBC values to point to a new server location.

In general, ODBC values for Windows XP and above live in the following location:

HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI

If one were to deploy a script/reg key/whatever in SCCM 2007 to 32-bit machines to modify a value at this location, it would work without any issue.

For example, my SCCM program would be something like:

cmd.exe /c update-odbc-regkeys.bat

However, if I were to deploy the same SCCM program to a 64-bit machine, the magical behind the scenes file-redirector would transparently write the keys to the following location instead:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI

Sometimes this is the desired behavior, sometimes it is not.

In order to get the same script to bypass the FS Redirector behavior, I can modify my SCCM Program as follows:

%windir%\sysnative\cmd.exe /c update-odbc-regkeys.bat

By invoking my script using the "sysnative" path instead, the aforementioned script would then modify the registry values at the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ location, not HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\.

This can also be handy to ensure that something is written to ":\Program Files" instead of ":\Program Files (x86)".

It should be noted that modifying the command line to invoke the "sysnative" call *only* works on 64-bit machines. So for SCCM 2007, you would need a separate program that calls the modified command-line with platform restrictions limiting it to 64-bit targets. Also, for good measure, you should ensure that your "original" program has platform restrictions limiting it only to 32-bit targets.

Does this behavior change in SCCM 2012? I believe so, as the there is a native 64-bit client in 2012, but I'm still stuck on 2007 (as I'm sure quite a few people are...) so I can't say for certain.

This is a very in-depth topic, and this post barely scratches the surface, but hopefully can point someone else in the right direction.

sources:

http://msmvps.com/blogs/installsite/archive/2012/08/22/using-sysnative-to-access-the-64-bit-system-folder-from-a-32-bit-application.aspx
http://practicaladmin.wordpress.com/2010/07/16/powershell-x64-and-filesystem-redirection/#more-62
http://msdn.microsoft.com/en-us/library/windows/desktop/aa384187(v=vs.85).aspx
http://blog.danovich.com.au/2010/04/21/considerations-when-scripting-for-64-bit-machines-with-sccm/

Enabling Wake-On-Lan for Dell Optiplexs and have it actually work! (hopefully!)

I'll worry about formatting later.

My co-worker asked me to try to get Wake-On-LAN from power-off state working on the Optiplex models in our lab.

I did some digging and poking and eventually got it to work and I thought I would share my findings.

This has been tested and works on Optiplex models: 780, 790 and 7010. It might work on older/newer models, YMMV.

I've found there are there are three settings that need to be in place for this to function:

1. Enable WOL on the wired network card in the OS.
2. Enable WOL (also called "remote wake up") for the wired NIC in the BIOS.
3. Disable "Low Power Mode" (for the 780) or "Deep Sleep Mode" (for the 790 and 7010) in the BIOS.

Until all three of these were done, the machines would only wake up from Standby, not from full shutdown.

The settings for 2 and 3 can be done manually via the BIOS configuration, or using the "Dell Client Configuration Toolkit" (CCTK) tool.

http://en.community.dell.com/techcenter/systems-management/w/wiki/1952.dell-client-configuration-toolkit-cctk.aspx

Dell released a new version (2.2.1) of the CCTK tool on October 18, 2013 that supports making changes to the applicable power control settings using the --deepsleepctrl switch.

The deepsleepctrl switch also allows for changes to the "Low Power Mode" setting on the 780s, but the documentation does not explicitly state this (even though the terminology slightly changed).

The CCTK documentation mentions that the deepsleepctrl switch supports three values: "s5only", "s4ands5", "disable". Optiplex 780 models do not support the "s5only" value as far as I can tell, but the other two work.

Once you have CCTK up and running, the CCTK command line to perform steps 2 and 3 would look something like this:

cctk.exe --deepsleepctrl=disable --wakeonlan=enable

This CCTK command line run on your machines would perform the required BIOS level configurations to get WOL working on these machine models.

CCTK also has the ability to create self-contained executable files (SCE) that you can deploy to your machines without having to install CCTK on each device.

HOWEVER, in my environment, my attempt to deploy a CCTK SCE via a  SCCM 2007 software distribution failed in my lab with an exit code of 10 for all my machines for whatever reason.

I was able to work around this by either disabling logging by adding a "/nolog" argument to the CCTK SCE Program in SCCM:

EnableWOL-2Settings.exe /nolog

or redirecting to another location such as this:

EnableWOL-2Settings.exe /l="%windir%\temp\enableWOL.txt"


Anyway, hopefully this can help out someone else.

-m

Sources:
www.symantec.com/connect/articles/wol-not-working-dell-models-760-780-960-and-990
http://en.community.dell.com/techcenter/enterprise-client/f/4448/t/19441236.aspx