Collect SQL 2019 inventory using Sccm

Question

I want to collect SQL 2019 inventory….I tried Google to get mof file but no luck..can anyone advice or help to get SQL 2019 inventory class for reporting

solved 0
sadik chikode 2020-06-02T13:59:48+05:30 7 Answers 983 views Beginner
0

Answers ( 7 )

  2. karthikeyan
    0
    2020-06-02T19:32:25+05:30
    Reply

    Hi,

    Your requirement is create custom new class to capture the SQL server installed provided information using custom mof file.

    But your post to collect the SQL server 2019 information. That’s why there is a confusion here.

    If you check the existing above link closely. then they have mentioned till 2017 which means. for 2019 custom mof you can use below command.
    —————————————————————————————————————————–
    //———————————————

    // SQL 2019 Properties

    //———————————————

    [Union, ViewSources{“select IsReadOnly,PropertyIndex,PropertyName,PropertyNumValue,PropertyStrValue,PropertyValueType,

    ServiceName,SqlServiceType from sqlServiceAdvancedProperty”},ViewSpaces{“\\\\.\\root\\microsoft\\sqlserver\\computermanagement16”}, dynamic,

    Provider(“MS_VIEW_INSTANCE_PROVIDER”)]

    class cm_sql19

    {

    [PropertySources{“IsReadOnly”} ] boolean IsReadOnly;

    [PropertySources{“PropertyIndex”},key ] uint32 PropertyIndex;

    [PropertySources{“PropertyName”},key ] string PropertyName;

    [PropertySources{“PropertyNumValue”} ] uint32 PropertyNumValue;

    [PropertySources{“PropertyStrValue”} ] string PropertyStrValue;

    [PropertySources{“PropertyValueType”} ] uint32 PropertyValueType;

    [PropertySources{“ServiceName”},key ] string ServiceName;

    [PropertySources{“SqlServiceType”},key] uint32 SqlServiceType;

    };
    ——————————————————————————–
    Save above file as Configuration.Mof

    ———————————————————————————-
    //=================SQL 2019 Information

    [dynamic, provider(“MS_VIEW_INSTANCE_PROVIDER”),

    SMS_Report(TRUE),

    SMS_Group_Name(“SQL19 Property”),

    SMS_Class_ID(“CUSTOM|SQL19_Property|1.0”)]

    class cm_sql19 : SMS_Class_Template

    {

    [SMS_Report(TRUE) ] boolean IsReadOnly;

    [SMS_Report(TRUE),key] uint32 PropertyIndex;

    [SMS_Report(TRUE),key] string PropertyName;

    [SMS_Report(TRUE) ] uint32 PropertyNumValue;

    [SMS_Report(TRUE) ] string PropertyStrValue;

    [SMS_Report(TRUE) ] uint32 PropertyValueType;

    [SMS_Report(TRUE),key] string ServiceName;

    [SMS_Report(TRUE),key] uint32 SqlServiceType;

    };
    —————————————————————————
    save this file as SQL_Property.mof

    First save this above mof files as per the names then try to import.

    Thanks
    karthikeyan

    Best answer
  4. sadik chikode
    0
    2020-06-02T18:36:58+05:30
    Reply

    Information not available in Sccm. We want add custom inventory to collect SQL 2019 as well.
    https://syscenramblings.wordpress.com/2018/01/26/sql-server-inventory-using-configmgr/

    In above link SQL inventory collection provided til SQL 2017
    I am looking for similar classes for SQL 2019.Hope it clear now

  6. karthikeyan
    0
    2020-06-02T18:18:47+05:30
    Reply

    Hi,

    Generally Mof file used to collect the custom inventory information on client machines. If the information is already available in SCCM then why you need to create custom mof to collection same information?

    thanks
    Karthikeyan

  8. karthikeyan
    3
    2020-06-02T16:23:24+05:30
    Reply

    Hi,

    Using existing hardware inventory class. we can collect the SQL installed machines report.

    Given below is the SQL query to get the SQL Server installed machines report
    ————————————————————————————————————————
    Select distinct
    Vrs.ResourceID as ‘ResourceID’,
    Vrs.Name0 as ‘MachineName’,
    –Vrs.DESCRIPTION0 as ‘Description’,
    ChassisType = CASE
    WHEN Vse.ChassisTypes0 in (‘1’) THEN ‘Virtual’
    WHEN Vse.ChassisTypes0 in (‘8′,’9′,’10’,’12’,’14’) THEN ‘Notebook’
    WHEN Vse.ChassisTypes0 in (‘3′,’4′,’5′,’6′,’7′,’13’,’15’,’16’) THEN ‘Desktop’
    WHEN Vse.ChassisTypes0 in (‘2′,’11’,’17’) THEN ‘Tablet’
    when Vse.ChassisTypes0 is Null Then ‘NA’
    Else ‘Others’
    END,
    VPB.MANUFACTURER0 as ‘Manufacturer’,
    Model = CASE
    WHEN VPB.MANUFACTURER0 like ‘%Dell%’ THEN Csd.Model00
    WHEN VPB.MANUFACTURER0 like ‘%Lenovo%’ THEN Vcsp.Version0
    Else
    Csd.Model00
    End,
    VPB.SERIALNUMBER0 as ‘SerialNumber’,
    Vse.SMBIOSAssetTag0 as ‘AssetTag’,
    convert(varchar,VPM.TOTALPHYSICALMEMORY0 / (1024)/1000 ) as ‘Total_RAM_Size_GB’,
    Vgl.Size0 as ‘Total_HDD_Size_GB’,
    FORMAT(Vws.LastHWScan, ‘MM/dd/yyyy HH:mm:ss’) as ‘LastHWScan’,
    DateDiff(D, Convert(VarChar, Vws.LastHWScan,100), GetDate()) as ‘LastHWScanAge’,
    Vos.Caption0 as ‘OperatingSystem’,
    Vos.CSDVersion0 as ‘ServicePack’,
    Osd.Version00 as ‘Version’,
    Csd.SystemType00 as ‘OSType’,
    FORMAT(Vos.InstallDate0, ‘MM/dd/yyyy HH:mm:ss’) as ‘OSInstallDate’,
    DateDiff(D, Vos.InstallDate0, GetDate()) ‘OSInstallDateAge’,
    FORMAT(Vos.LastBootUpTime0, ‘MM/dd/yyyy HH:mm:ss’) as ‘LastBootUpDate’,
    DateDiff(D, Convert(VarChar, Vos.LastBootUpTime0,100), GetDate()) as ‘LastBootUpDateAge’,
    Vpb.Name0 as ‘BiosName’,
    Vpb.BIOSVersion0 as ‘BiosVersion’,
    Vp.Name0 as ‘ProcessorName’,
    Vp.NumberOfCores0 as ‘NumberOfCores’,
    Vp.NumberOfLogicalProcessors0 as ‘NumberOfLogicalProcessors’,
    Vra.SMS_Assigned_Sites0 as ‘AssignedSite’,
    Vrs.AD_Site_Name0 as ‘ADSiteName’,
    Vrs.Full_Domain_Name0 as ‘FullDomainName’,
    (Vrs.Name0 + ‘.’ + Vrs.Full_Domain_Name0) as ‘FQDN’,
    Vrs.USER_DOMAIN0 as ‘UserDomain’,
    Vrs.USER_NAME0 as ‘LastLogonID’,
    Vcud.TOPCONSOLEUSER00 as ‘TopConsoleUserID’,
    ‘PSID’ = CASE
    WHEN Vcud.TOPCONSOLEUSER00 IS NOT NULL THEN SUBSTRING(Vcud.TOPCONSOLEUSER00,CHARINDEX(‘\’,Vcud.TOPCONSOLEUSER00)+1,CHARINDEX(‘\’,Vcud.TOPCONSOLEUSER00)+10)
    ELSE Vrs.USER_NAME0
    END,
    Vru.Mail0 as ‘EMailID’,
    App.ARPDisplayName0 as ‘AppName’,
    App.ProductVersion0 as ‘AppVersion’
    FROM
    V_R_SYSTEM as Vrs
    LEFT JOIN V_GS_PC_BIOS as Vpb ON VPB.ResourceID = Vrs.ResourceID
    LEFT JOIN V_GS_X86_PC_MEMORY as Vpm ON VPM.ResourceID = Vrs.ResourceID
    LEFT JOIN V_GS_COMPUTER_SYSTEM as Vcs ON VCS.ResourceID = Vrs.ResourceID
    LEFT JOIN V_GS_WORKSTATION_STATUS as Vws ON Vws.ResourceID = Vrs.ResourceID
    LEFT JOIN SYSTEM_CONSOLE_USAGE_DATA as Vcud ON Vcud.MachineID = Vrs.ResourceID
    LEFT JOIN V_GS_COMPUTER_SYSTEM_PRODUCT Vcsp ON Vcsp.ResourceID = Vrs.ResourceID
    LEFT JOIN V_GS_SYSTEM_ENCLOSURE as Vse ON Vse.ResourceID = Vrs.ResourceID
    LEFT JOIN v_GS_OPERATING_SYSTEM as Vos ON Vos.ResourceID = Vrs.ResourceID
    LEFT JOIN Operating_System_DATA as Osd ON Osd.MachineID = Vrs.ResourceID
    LEFT JOIN v_GS_LOGICAL_DISK as Vgl ON Vgl.ResourceID = Vrs.ResourceID
    LEFT JOIN v_GS_PROCESSOR as Vp ON Vp.ResourceID = Vrs.ResourceID
    LEFT JOIN Computer_System_DATA as Csd ON Csd.MachineID = VRS.ResourceId
    LEFT JOIN v_RA_System_SMSAssignedSites as Vra ON Vra.ResourceID = VRS.ResourceId
    LEFT JOIN v_R_User as Vru ON Vru.User_Name0 = Vrs.User_Name0
    LEFT JOIN v_GS_INSTALLED_SOFTWARE as App ON App.ResourceID = VRS.ResourceId
    WHERE Vrs.Active0 = 1 and Vrs.Obsolete0 = 0 and Vrs.Decommissioned0 = 0
    –and Vrs.Operating_System_Name_and0 like ‘%Workstation%’
    and (App.ARPDisplayName0 like ‘%SQL%Server%’)
    ORDER BY Vrs.ResourceID
    ———————————————————————————-

    Thanks
    Karthikeyan

Leave an answer

Sorry, you do not have permission to answer to this question .