locked
All Desktop and Server Clients RRS feed

  • Question

  • Hello,

    The collection "All Desktop and Server Clients" has 26,508 members.

    When I run a query provided by the supplier:

    SELECT v_GS_COMPUTER_SYSTEM.Name0 as name, v_GS_COMPUTER_SYSTEM.domain0 as domain, v_GS_COMPUTER_SYSTEM.Model0 as model, v_GS_COMPUTER_SYSTEM.Manufacturer0 as manufacturer, v_GS_COMPUTER_SYSTEM.ResourceID, v_GS_COMPUTER_SYSTEM.UserName0 as username, v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 as numberofprocessors, v_GS_SYSTEM.SystemRole0 as SystemRole, v_GS_OPERATING_SYSTEM.Caption0 as caption, v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 as chassistype, v_GS_WORKSTATION_STATUS.LastHWScan, v_GS_PC_BIOS.SerialNumber0 as BIOSSerialNumber, v_GS_COMPUTER_SYSTEM_PRODUCT.IdentifyingNumber0 as SystemSerialNumber, v_GS_COMPUTER_SYSTEM_PRODUCT.UUID0 as UUIDSerialNumber, v_GS_SYSTEM_ENCLOSURE.SerialNumber0 as ChassisSerialNumber, v_GS_BASEBOARD.SerialNumber0 as BaseboardSerialNumber, v_GS_Registry_Values0.Value0 as CheckPointEncryptionStatus FROM v_GS_COMPUTER_SYSTEM LEFT JOIN v_GS_WORKSTATION_STATUS ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID LEFT JOIN v_GS_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM.ResourceID LEFT JOIN v_GS_PC_BIOS ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_PC_BIOS.ResourceID LEFT JOIN v_GS_OPERATING_SYSTEM ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID LEFT JOIN v_GS_COMPUTER_SYSTEM_PRODUCT ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_COMPUTER_SYSTEM_PRODUCT.ResourceID LEFT JOIN v_GS_SYSTEM_ENCLOSURE ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID LEFT JOIN v_GS_BASEBOARD ON v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_BASEBOARD.ResourceID LEFT JOIN v_GS_Registry_Values0 on v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_Registry_Values0.ResourceID WHERE v_GS_Registry_Values0.KeyPath0 = N'HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\CheckPoint\EndPoint Security\Full Disk Encryption\Status\Current Boot\ClientStatus' or v_GS_Registry_Values0.KeyPath0 is null Order by v_GS_COMPUTER_SYSTEM.Name0

    I am getting in return 26,047 members which are 461 machines less than the collection so my questions are:

    is the test on the registry "_GS_Registry_Values0.KeyPath0 is null" correct to get the machine whih does not have the registry key "

    HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\CheckPoint\EndPoint Security\Full Disk Encryption\Status\Current Boot\ClientStatus

    or will it gives me all machine having any registry "Null"?

    If not how to get the machines missing the specific key?

    How to explain the difference between the collection "All Desktop and Server Clients" and the content of the table v_GS_COMPUTER_SYSTEM?

    I noticed also that this query is giving a duplicated entries for the clusters, how to prevent this?

    name	model	manufacturer	ResourceID	username	numberofprocessors	SystemRole	caption	chassistype	LastHWScan	BIOSSerialNumber	SystemSerialNumber	UUIDSerialNumber	ChassisSerialNumber	BaseboardSerialNumber	CheckPointEncryptionStatus
    SOTEDWDB002	x3950 X6 -[3837AC2]-	IBM	16777243	NULL	8	Server	Microsoft Windows Server 2012 R2 Standard	23	2018-11-27 15:14:47.000	06CF474	NULL	NULL	06CF474	NULL	NULL
    SOTEDWDB002	x3950 X6 -[3837AC2]-	IBM	16777243	NULL	8	Server	Microsoft Windows Server 2012 R2 Standard	23	2018-11-27 15:14:47.000	06CF474	NULL	NULL	06CF474	NULL	NULL

    Thanks,
    Dom


    Security / System Center Configuration Manager Current Branch / SQL






    • Edited by Felyjos Thursday, November 29, 2018 8:07 PM
    Thursday, November 29, 2018 7:40 PM

Answers

  • Please try:

    Declare @AllDScollID as NVARCHAR(8)
    Select @AllDScollID = col.CollectionID from v_Collection col where col.Name = 'All Desktop and Server Clients'
    
    SELECT Distinct
    v_R_System.ResourceID,
    v_R_System.Name0 as name,
    v_GS_COMPUTER_SYSTEM.domain0 as domain,
    v_GS_COMPUTER_SYSTEM.Model0 as model,
    v_GS_COMPUTER_SYSTEM.Manufacturer0 as manufacturer,
    v_GS_COMPUTER_SYSTEM.UserName0 as username,
    v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 as numberofprocessors,
    v_GS_SYSTEM.SystemRole0 as SystemRole,
    v_GS_OPERATING_SYSTEM.Caption0 as caption,
    v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 as chassistype,
    v_GS_WORKSTATION_STATUS.LastHWScan, 
    v_GS_PC_BIOS.SerialNumber0 as BIOSSerialNumber,
    v_GS_COMPUTER_SYSTEM_PRODUCT.IdentifyingNumber0 as SystemSerialNumber,
    v_GS_COMPUTER_SYSTEM_PRODUCT.UUID0 as UUIDSerialNumber,
    v_GS_SYSTEM_ENCLOSURE.SerialNumber0 as ChassisSerialNumber,
    v_GS_BASEBOARD.SerialNumber0 as BaseboardSerialNumber,
    tmp.Value0 as CheckPointEncryptionStatus
    
    FROM v_R_System
    join v_FullCollectionMembership on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID
     and v_FullCollectionMembership.CollectionID = @AllDScollID 
    left join v_GS_COMPUTER_SYSTEM on v_R_System.ResourceID= v_GS_COMPUTER_SYSTEM.ResourceID
    LEFT JOIN v_GS_WORKSTATION_STATUS ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID 
    LEFT JOIN v_GS_SYSTEM ON v_R_System.ResourceID = v_GS_SYSTEM.ResourceID 
    LEFT JOIN v_GS_PC_BIOS ON v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID
    LEFT JOIN v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
    LEFT JOIN v_GS_COMPUTER_SYSTEM_PRODUCT ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM_PRODUCT.ResourceID
    LEFT JOIN v_GS_SYSTEM_ENCLOSURE ON v_R_System.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID
    LEFT JOIN v_GS_BASEBOARD ON v_R_System.ResourceID = v_GS_BASEBOARD.ResourceID
    LEFT JOIN (Select ResourceID, Value0 From v_GS_Registry_Values0 where
    v_GS_Registry_Values0.KeyPath0 = N'HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\CheckPoint\EndPoint Security\Full Disk Encryption\Status\Current Boot\ClientStatus'
    ) tmp on v_R_System.ResourceID = tmp.ResourceID
    
    
    Order by v_R_System.Name0.Name0



    • Edited by NikNicholas Monday, December 3, 2018 7:16 PM
    • Marked as answer by Felyjos Monday, December 3, 2018 7:37 PM
    Monday, December 3, 2018 7:15 PM

All replies

  • If you are getting this from a supplier then you should ask them why the results are incorrect. However they are clearly not following best practices as they should using v_r_system or v_r_system_valid.

    Garth Jones

    Blog: https://www.enhansoft.com/blog Old Blog: https://sccmug.ca/

    Twitter: @GarthMJ Book: System Center Configuration Manager Reporting Unleashed

    Thursday, November 29, 2018 8:27 PM
  • Garth is absolutely correct, you should ask those who wrote the query to fix it.

    To try and address some of the issues...

    Using the IS NULL criteria is correct to get those that don't have the reg key. However, because you have another criteria to look for the specific reg key that turns the LEFT JOIN into an INNER JOIN so if a machine hasn't reported anything for that class (assuming it's a custom WMI class picked up via HWINV) then you won't get those machines. To get machines missing the specific key you'd have to add the key to the join condition and use the is null criteria by itself.

    v_GS_COMPUTER_SYSTEM will only contain the machines that have reported information from the hardware inventory class. The collection is based on actual machine records.

    You're getting duplicate records because one of those views has duplicate records for a given ResourceId...


    Benjamin

    Thursday, November 29, 2018 8:42 PM
  • Hi, Please try this to see is any improvement:

    Declare @AllDScollID as NVARCHAR(8)
    Select @AllDScollID = col.CollectionID from v_Collection col where col.Name = 'All Desktop and Server Clients'
    
    SELECT Distinct
    v_R_System.ResourceID,
    v_GS_COMPUTER_SYSTEM.Name0 as name,
    v_GS_COMPUTER_SYSTEM.domain0 as domain,
    v_GS_COMPUTER_SYSTEM.Model0 as model,
    v_GS_COMPUTER_SYSTEM.Manufacturer0 as manufacturer,
    v_GS_COMPUTER_SYSTEM.UserName0 as username,
    v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 as numberofprocessors,
    v_GS_SYSTEM.SystemRole0 as SystemRole,
    v_GS_OPERATING_SYSTEM.Caption0 as caption,
    v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 as chassistype,
    v_GS_WORKSTATION_STATUS.LastHWScan, 
    v_GS_PC_BIOS.SerialNumber0 as BIOSSerialNumber,
    v_GS_COMPUTER_SYSTEM_PRODUCT.IdentifyingNumber0 as SystemSerialNumber,
    v_GS_COMPUTER_SYSTEM_PRODUCT.UUID0 as UUIDSerialNumber,
    v_GS_SYSTEM_ENCLOSURE.SerialNumber0 as ChassisSerialNumber,
    v_GS_BASEBOARD.SerialNumber0 as BaseboardSerialNumber,
    v_GS_Registry_Values0.Value0 as CheckPointEncryptionStatus
    
    FROM v_R_System
    join v_FullCollectionMembership on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID
     and v_FullCollectionMembership.CollectionID = @AllDScollID 
    left join v_GS_COMPUTER_SYSTEM on v_R_System.ResourceID= v_GS_COMPUTER_SYSTEM.ResourceID
    LEFT JOIN v_GS_WORKSTATION_STATUS ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID 
    LEFT JOIN v_GS_SYSTEM ON v_R_System.ResourceID = v_GS_SYSTEM.ResourceID 
    LEFT JOIN v_GS_PC_BIOS ON v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID
    LEFT JOIN v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
    LEFT JOIN v_GS_COMPUTER_SYSTEM_PRODUCT ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM_PRODUCT.ResourceID
    LEFT JOIN v_GS_SYSTEM_ENCLOSURE ON v_R_System.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID
    LEFT JOIN v_GS_BASEBOARD ON v_R_System.ResourceID = v_GS_BASEBOARD.ResourceID
    LEFT JOIN v_GS_Registry_Values0 on v_R_System.ResourceID = v_GS_Registry_Values0.ResourceID
    
    WHERE 
    v_GS_Registry_Values0.KeyPath0 = N'HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\CheckPoint\EndPoint Security\Full Disk Encryption\Status\Current Boot\ClientStatus'
    or v_GS_Registry_Values0.KeyPath0 is null or v_GS_Registry_Values0.KeyPath0 = ''
    Order by v_GS_COMPUTER_SYSTEM.Name0
    
    I don't have v_GS_Registry_Values0 to do full validation and am afraid that the where may limit output in which case we may be able to use a case statement instead of a where.
    • Edited by NikNicholas Thursday, November 29, 2018 10:15 PM
    Thursday, November 29, 2018 8:59 PM
  • Hello NikNicholas,

    Yes it is looking better as this query is picking 26413 items so I am checking now if there are duplicates also as I have a lot of NULL values...


    So about 370 already as "NULL" !!! in the v_R_System so what are this machine names "NULL" in v_R_System? which explain 75% of the gap between the HW table/view and the collection... I miss only  113

    Thanks,
    Dom


    Security / System Center Configuration Manager Current Branch / SQL


    • Edited by Felyjos Thursday, November 29, 2018 10:52 PM
    Thursday, November 29, 2018 10:47 PM
  • Try:

    SELECT Distinct
    v_R_System
    .ResourceID,
    v_R_System.Name0 as name,

    instead of:

    SELECT Distinct
    v_R_System
    .ResourceID,
    v_GS_COMPUTER_SYSTEM
    .Name0 as name,

    Sunday, December 2, 2018 6:34 PM
  • v_R_System.Name0 as name, is giving me 26,435

    v_GS_COMPUTER_SYSTEM.Name0 as name is giving me 26,436

    so it is almost the same

    All Desktop and Server Clients is at 46,545 still up to 110... how to identify these machines...

    Thanks,

    Dom


    Security / System Center Configuration Manager Current Branch / SQL

    Monday, December 3, 2018 5:37 PM
  • Please try:

    Declare @AllDScollID as NVARCHAR(8)
    Select @AllDScollID = col.CollectionID from v_Collection col where col.Name = 'All Desktop and Server Clients'
    
    SELECT Distinct
    v_R_System.ResourceID,
    v_R_System.Name0 as name,
    v_GS_COMPUTER_SYSTEM.domain0 as domain,
    v_GS_COMPUTER_SYSTEM.Model0 as model,
    v_GS_COMPUTER_SYSTEM.Manufacturer0 as manufacturer,
    v_GS_COMPUTER_SYSTEM.UserName0 as username,
    v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 as numberofprocessors,
    v_GS_SYSTEM.SystemRole0 as SystemRole,
    v_GS_OPERATING_SYSTEM.Caption0 as caption,
    v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 as chassistype,
    v_GS_WORKSTATION_STATUS.LastHWScan, 
    v_GS_PC_BIOS.SerialNumber0 as BIOSSerialNumber,
    v_GS_COMPUTER_SYSTEM_PRODUCT.IdentifyingNumber0 as SystemSerialNumber,
    v_GS_COMPUTER_SYSTEM_PRODUCT.UUID0 as UUIDSerialNumber,
    v_GS_SYSTEM_ENCLOSURE.SerialNumber0 as ChassisSerialNumber,
    v_GS_BASEBOARD.SerialNumber0 as BaseboardSerialNumber,
    tmp.Value0 as CheckPointEncryptionStatus
    
    FROM v_R_System
    join v_FullCollectionMembership on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID
     and v_FullCollectionMembership.CollectionID = @AllDScollID 
    left join v_GS_COMPUTER_SYSTEM on v_R_System.ResourceID= v_GS_COMPUTER_SYSTEM.ResourceID
    LEFT JOIN v_GS_WORKSTATION_STATUS ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID 
    LEFT JOIN v_GS_SYSTEM ON v_R_System.ResourceID = v_GS_SYSTEM.ResourceID 
    LEFT JOIN v_GS_PC_BIOS ON v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID
    LEFT JOIN v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
    LEFT JOIN v_GS_COMPUTER_SYSTEM_PRODUCT ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM_PRODUCT.ResourceID
    LEFT JOIN v_GS_SYSTEM_ENCLOSURE ON v_R_System.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID
    LEFT JOIN v_GS_BASEBOARD ON v_R_System.ResourceID = v_GS_BASEBOARD.ResourceID
    LEFT JOIN (Select ResourceID, Value0 From v_GS_Registry_Values0 where
    v_GS_Registry_Values0.KeyPath0 = N'HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\CheckPoint\EndPoint Security\Full Disk Encryption\Status\Current Boot\ClientStatus'
    ) tmp on v_R_System.ResourceID = tmp.ResourceID
    
    
    Order by v_R_System.Name0.Name0



    • Edited by NikNicholas Monday, December 3, 2018 7:16 PM
    • Marked as answer by Felyjos Monday, December 3, 2018 7:37 PM
    Monday, December 3, 2018 7:15 PM
  • Please try:

    Declare @AllDScollID as NVARCHAR(8)
    Select @AllDScollID = col.CollectionID from v_Collection col where col.Name = 'All Desktop and Server Clients'
    
    SELECT Distinct
    v_R_System.ResourceID,
    v_R_System.Name0 as name,
    v_GS_COMPUTER_SYSTEM.domain0 as domain,
    v_GS_COMPUTER_SYSTEM.Model0 as model,
    v_GS_COMPUTER_SYSTEM.Manufacturer0 as manufacturer,
    v_GS_COMPUTER_SYSTEM.UserName0 as username,
    v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 as numberofprocessors,
    v_GS_SYSTEM.SystemRole0 as SystemRole,
    v_GS_OPERATING_SYSTEM.Caption0 as caption,
    v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 as chassistype,
    v_GS_WORKSTATION_STATUS.LastHWScan, 
    v_GS_PC_BIOS.SerialNumber0 as BIOSSerialNumber,
    v_GS_COMPUTER_SYSTEM_PRODUCT.IdentifyingNumber0 as SystemSerialNumber,
    v_GS_COMPUTER_SYSTEM_PRODUCT.UUID0 as UUIDSerialNumber,
    v_GS_SYSTEM_ENCLOSURE.SerialNumber0 as ChassisSerialNumber,
    v_GS_BASEBOARD.SerialNumber0 as BaseboardSerialNumber,
    tmp.Value0 as CheckPointEncryptionStatus
    
    FROM v_R_System
    join v_FullCollectionMembership on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID
     and v_FullCollectionMembership.CollectionID = @AllDScollID 
    left join v_GS_COMPUTER_SYSTEM on v_R_System.ResourceID= v_GS_COMPUTER_SYSTEM.ResourceID
    LEFT JOIN v_GS_WORKSTATION_STATUS ON v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID 
    LEFT JOIN v_GS_SYSTEM ON v_R_System.ResourceID = v_GS_SYSTEM.ResourceID 
    LEFT JOIN v_GS_PC_BIOS ON v_R_System.ResourceID = v_GS_PC_BIOS.ResourceID
    LEFT JOIN v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
    LEFT JOIN v_GS_COMPUTER_SYSTEM_PRODUCT ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM_PRODUCT.ResourceID
    LEFT JOIN v_GS_SYSTEM_ENCLOSURE ON v_R_System.ResourceID = v_GS_SYSTEM_ENCLOSURE.ResourceID
    LEFT JOIN v_GS_BASEBOARD ON v_R_System.ResourceID = v_GS_BASEBOARD.ResourceID
    LEFT JOIN (Select ResourceID, Value0 From v_GS_Registry_Values0 where
    v_GS_Registry_Values0.KeyPath0 = N'HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\CheckPoint\EndPoint Security\Full Disk Encryption\Status\Current Boot\ClientStatus'
    ) tmp on v_R_System.ResourceID = tmp.ResourceID
    
    
    Order by v_R_System.Name0.Name0



    Msg 258, Level 15, State 1, Line 39
    Cannot call methods on nvarchar.
    Msg 145, Level 15, State 1, Line 4
    ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

    a double Name0 I find out...

    Then I am getting 26598 which is really good now...

    Thanks,
    Dom



    Security / System Center Configuration Manager Current Branch / SQL



    • Edited by Felyjos Monday, December 3, 2018 7:32 PM
    Monday, December 3, 2018 7:28 PM