Hi ,

I have a below SQL query to get the machines information with one MAC address .In most cases there are probably more MAC-addresses than one because a laptop has a separate network adapters for physical and wireless connections so I need to list them both. Can you a anyone sugget or Let me know If you have  that query with included all these information

SELECT
A.Name0,
MAX (B.SerialNumber0) ,
A.Manufacturer0,
A.Model0,
 C.Name0 ,
D.TotalPhysicalMemory0 ,
MAX ( E.Size0 ) ,
MAX (F.MACAddress0) ,
MAX (F.IPAddress0) ,
G.AD_Site_Name0 ,
MAX (A.UserName0) ,
H.Caption0 ,
H.CSDVersion0,
G.Creation_Date0 ,
I.LastHWScan,
BL.DriveLetter0,
BL.ProtectionStatus0,
BL.PersistentVolumeID0,
CASE
WHEN BL.ProtectionStatus0 = 1 THEN 'Encrypted'
WHEN ((BL.ProtectionStatus0 = 0) AND (BL.PersistentVolumeID0 is not NULL)) THEN 'Suspended'
ELSE '*** UNENCRYPTED ***'
END AS 'Encryption_Status'

FROM
v_GS_COMPUTER_SYSTEM A,
v_GS_PC_BIOS B,
v_GS_PROCESSOR C,
v_GS_X86_PC_MEMORY D,
v_GS_DISK E,
v_GS_NETWORK_ADAPTER_CONFIGUR F,

v_GS_OPERATING_SYSTEM H,
v_GS_WORKSTATION_STATUS I,
v_GS_BITLOCKER_VOLUME_ENC BL INNER JOIN
v_R_System G on G.ResourceID=BL.ResourceID

WHERE
A.ResourceID = B.ResourceID AND
A.ResourceID = C.ResourceID AND
A.ResourceID = D.ResourceID AND
A.ResourceID = E.ResourceID AND
A.ResourceID = F.ResourceID AND
A.ResourceID = G.ResourceID AND
A.ResourceID = H.ResourceID AND
A.ResourceID = I.ResourceID AND
A.ResourceID = BL.ResourceID AND
((BL.DriveLetter0 like 'C:') AND
(G.Netbios_Name0 like 'W%'))

GROUP BY A.Name0, A.Manufacturer0, A.Model0, C.Name0, D.TotalPhysicalMemory0, G.AD_Site_Name0, A.UserName0, H.Caption0, H.CSDVersion0, G.Creation_Date0, I.LastHWScan,BL.DriveLetter0,
BL.ProtectionStatus0,BL.PersistentVolumeID0

Result will be in the below format

Name Serial numbers Manufacturer Model Name Total Physical Memory (MB) IP Address AD Site Name Last Loggedon Users name Caption CSD Version Creation_Date0 Last Hardware Scan Encryption_Status

• Edited by Karthick_Jokirathinam Thursday, February 28, 2013 2:56 PM

I clean this up a lot and remove all of the unnecessary stuff.

http://smsug.ca/blogs/garth_jones/archive/2013/02/28/mac-address-report.aspx

Hi , thanks for your reply.. i am getting following error "Incorrect syntax near 'IPAddress0' after run this query. can u please have a look at that.

Hi , thanks for your reply.. i am getting following error "Incorrect syntax near 'IPAddress0' after run this query. can u please have a look at that.

I just tested it again, it worked perfectly. Did you uncomment any of the lines? IF so did you check the syntax?

okie.. i will test it again. Just to confirm  will the given query list the MAC addresses for Physical & wireless network adapters separately?

Report needs same as in the below. 

Name   Serial numbers   Manufacturer   Model Name   Total Physical Memory(MB)   IP Address   AD Site Name  Last LoggedonUsers name  Caption  CSD Version  Creation Date0  Last Hardware Scan  Encryption Status   MAC Address(Physical adapter)   MAC Address(Wireless adapter)

• Edited by Karthick_Jokirathinam Monday, March 04, 2013 7:07 AM
• Marked as answer by Karthick_Jokirathinam Tuesday, March 05, 2013 2:03 PM
• Unmarked as answer by Karthick_Jokirathinam Tuesday, March 05, 2013 2:04 PM

I have created two separate queries to generate the mac address for Physical & WLAN MAC addresses separately and merged both the reports.

Query:

SELECT
A.Name0,
MAX (B.SerialNumber0) ,
A.Manufacturer0,
A.Model0,
 C.Name0 ,
D.TotalPhysicalMemory0 ,
MAX ( E.Size0 ) ,
MAX (J.MACAddress0) ,
MAX (F.IPAddress0) ,
G.AD_Site_Name0 ,
MAX (A.UserName0) ,
H.Caption0 ,
H.CSDVersion0,
G.Creation_Date0 ,
I.LastHWScan,
BL.DriveLetter0,
BL.ProtectionStatus0,
BL.PersistentVolumeID0,
CASE
WHEN BL.ProtectionStatus0 = 1 THEN 'Encrypted'
WHEN ((BL.ProtectionStatus0 = 0) AND (BL.PersistentVolumeID0 is not NULL)) THEN 'Suspended'
ELSE '*** UNENCRYPTED ***'
END AS 'Encryption_Status'

FROM
v_GS_COMPUTER_SYSTEM A,
v_GS_PC_BIOS B,
v_GS_PROCESSOR C,
v_GS_X86_PC_MEMORY D,
v_GS_DISK E,
v_GS_NETWORK_ADAPTER_CONFIGUR F,
v_GS_NETWORK_ADAPTER J ,
v_GS_OPERATING_SYSTEM H,
v_GS_WORKSTATION_STATUS I,
v_GS_BITLOCKER_VOLUME_ENC BL INNER JOIN
v_R_System G on G.ResourceID=BL.ResourceID

WHERE
A.ResourceID = B.ResourceID AND
A.ResourceID = C.ResourceID AND
A.ResourceID = D.ResourceID AND
A.ResourceID = E.ResourceID AND
A.ResourceID = F.ResourceID AND
A.ResourceID = G.ResourceID AND
A.ResourceID = H.ResourceID AND
A.ResourceID = I.ResourceID AND
A.ResourceID = J.ResourceID AND
A.ResourceID = BL.ResourceID AND
((BL.DriveLetter0 like 'C:') AND
(G.Netbios_Name0 like 'W%')AND
(J.Name0  LIKE '%Gigabit%')) - > For wireless adapter Mac address, i have mentioned WLAN adapter name.

GROUP BY A.Name0, A.Manufacturer0, A.Model0, C.Name0, D.TotalPhysicalMemory0, G.AD_Site_Name0, A.UserName0, H.Caption0, H.CSDVersion0, G.Creation_Date0, I.LastHWScan,BL.DriveLetter0,
BL.ProtectionStatus0,BL.PersistentVolumeID0

• Marked as answer by Karthick_Jokirathinam Tuesday, March 05, 2013 2:18 PM

Can this be done without the Bitlocker data gathering? I do not have a dbo.v_GS_BITLOCKER_VOLUME_ENC and im trying to figure out how to gather that data. So far i've done this:

Insert this at the bottom of
%Program Files%\Microsoft Configuration Manager\inboxes\clifiles.src\hinv\sms_def.mof 

[ SMS_Report (TRUE),

 SMS_Group_Name ("BitLocker Volume Encryption"),
 SMS_Class_ID ("MICROSOFT|BITLOCKER_VOLUME_ENC|1.0"),
 SMS_Namespace (FALSE),
 Namespace ("\\\\\\\\localhost\\\\root\\\\cimv2\\\\security\\\\MicrosoftVolumeEncryption") ]
class Win32_EncryptableVolume : SMS_Class_Template
{
 [SMS_Report (TRUE), key ] string DeviceID;
 [SMS_Report (TRUE) ] string DriveLetter;
 [SMS_Report (FALSE) ] string PersistentVolumeID;
 [SMS_Report (TRUE) ] uint32 ProtectionStatus;
};
[ SMS_Report (TRUE),
 SMS_Group_Name ("Trusted Platform Module"),
 SMS_Class_ID ("MICROSOFT|TRUSTED_PLATFORM_MODULE|1.0"),
 SMS_Namespace (FALSE),
 Namespace ("\\\\\\\\localhost\\\\root\\\\cimv2\\\\security\\\\MicrosoftTPM") ]
class Win32_TPM : SMS_Class_Template
{
 [SMS_Report (TRUE) ] boolean IsActivated_InitialValue;
 [SMS_Report (TRUE) ] boolean IsEnabled_InitialValue;
 [SMS_Report (TRUE) ] boolean IsOwned_InitialValue;
 [SMS_Report (FALSE), key] uint32 ManufacturerId;
 [SMS_Report (TRUE) ] string ManufacturerVersion;
 [SMS_Report (FALSE) ] string ManufacturerVersionInfo;
 [SMS_Report (FALSE) ] string PhysicalPresenceVersionInfo;
 [SMS_Report (TRUE) ] string SpecVersion;
};
And then tried forcing a Hardware Inventory audit, but I'm not having much luck.