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
---------------------------------------------------------------------------------------
