none
how to create dashboard total Clients & Non clients

    Frage

  • hi,

    how to write dashboard query for total sccm clients & Non clients kindly help. 


    Kiran

    Dienstag, 6. März 2012 06:10

Antworten

  • Hi,

    Try this:

    select count(v_R_System.resourceID) AS 'Total Clients', count(v_R_System.Client0) AS 'Clients',
    count(v_R_System.resourceID) - count(v_R_System.Client0) AS 'Non-Clients'
    from v_R_System

    Regards,
    Jörgen


    -- My System Center blog ccmexec.com -- Twitter @ccmexec

    Dienstag, 6. März 2012 08:51
  • you can use the following query:-

    select sub.AssignedSite,        sum(sub.cnt ) 'Total Count',
            SUM(CASE SUB.ACTIVE0 when 1 then SUB.cnt END) AS 'Total Active Clients',
            SUM(CASE SUB.ACTIVE0 when 0 then SUB.cnt END) AS 'Total Inactive Clients',
            SUM(CASE SUB.obsolete0 when 1 then SUB.cnt END) AS 'Total Obsolete Clients'
            ,ROUND((CAST(SUM(CASE SUB.ACTIVE0 when 0 then SUB.cnt END) AS float) / SUM(sub.Cnt)) * 100,2) AS '% of Inactive Client Count'
            ,ROUND((CAST(SUM(CASE SUB.obsolete0 when 1 then SUB.cnt END) AS float) / SUM(sub.Cnt)) * 100,2) AS '% of Obsolete Client Count'
    FROM
        (select sit.sms_assigned_sites0 AssignedSite,
                sys.active0,sys.obsolete0 ,
                COUNT(*) cnt
      from v_R_System sys
      join v_RA_System_SMSAssignedSites sit  on sys.resourceID=sit.resourceID
        and (sys.Active0 is not null and sys.Obsolete0 is not null)
    group by sit.sms_assigned_sites0,sys.active0,sys.obsolete0
    )sub
    group by sub.AssignedSite

    Dienstag, 13. März 2012 14:21
  • select sub.AssignedSite,        sum(sub.cnt ) 'Total Count',
            SUM(CASE SUB.ACTIVE0 when 1 then SUB.cnt END) AS 'Total Active Clients',
            SUM(CASE SUB.ACTIVE0 when 0 then SUB.cnt END) AS 'Total Inactive Clients',
            ROUND((CAST(SUM(CASE SUB.ACTIVE0 when 0 then SUB.cnt END) AS float) / SUM(sub.Cnt)) * 100,2) AS '% of Inactive Client Count'
    FROM
        (select sit.sms_assigned_sites0 AssignedSite,
                sys.active0,sys.obsolete0 ,
                COUNT(*) cnt
      from v_R_System sys
      join v_RA_System_SMSAssignedSites sit  on sys.resourceID=sit.resourceID
        and (sys.Active0 is not null and sys.Obsolete0 is not null)
    group by sit.sms_assigned_sites0,sys.active0,sys.obsolete0
    )sub
    group by sub.AssignedSite
    Montag, 19. März 2012 15:08

Alle Antworten

  • Hi,

    thanks for replay  above link dashbaord i verified there is no client & non clients status above dash board i used some off .

    can u pls help me how to write Query client & non Client status.


    Kiran

    Dienstag, 6. März 2012 07:26
  • What do you want to query? Just client and non-client numbers? If so you can use the following SQL query:
    select Client0, count(Client0) from v_R_System group by Client0

    Torsten Meringer | http://www.mssccmfaq.de

    Dienstag, 6. März 2012 08:09
  • Hi,

    Try this:

    select count(v_R_System.resourceID) AS 'Total Clients', count(v_R_System.Client0) AS 'Clients',
    count(v_R_System.resourceID) - count(v_R_System.Client0) AS 'Non-Clients'
    from v_R_System

    Regards,
    Jörgen


    -- My System Center blog ccmexec.com -- Twitter @ccmexec

    Dienstag, 6. März 2012 08:51
  • you can use the following query:-

    select sub.AssignedSite,        sum(sub.cnt ) 'Total Count',
            SUM(CASE SUB.ACTIVE0 when 1 then SUB.cnt END) AS 'Total Active Clients',
            SUM(CASE SUB.ACTIVE0 when 0 then SUB.cnt END) AS 'Total Inactive Clients',
            SUM(CASE SUB.obsolete0 when 1 then SUB.cnt END) AS 'Total Obsolete Clients'
            ,ROUND((CAST(SUM(CASE SUB.ACTIVE0 when 0 then SUB.cnt END) AS float) / SUM(sub.Cnt)) * 100,2) AS '% of Inactive Client Count'
            ,ROUND((CAST(SUM(CASE SUB.obsolete0 when 1 then SUB.cnt END) AS float) / SUM(sub.Cnt)) * 100,2) AS '% of Obsolete Client Count'
    FROM
        (select sit.sms_assigned_sites0 AssignedSite,
                sys.active0,sys.obsolete0 ,
                COUNT(*) cnt
      from v_R_System sys
      join v_RA_System_SMSAssignedSites sit  on sys.resourceID=sit.resourceID
        and (sys.Active0 is not null and sys.Obsolete0 is not null)
    group by sit.sms_assigned_sites0,sys.active0,sys.obsolete0
    )sub
    group by sub.AssignedSite

    Dienstag, 13. März 2012 14:21
  • hi,

    only clients and non clients status dashboard required.


    Kiran

    Dienstag, 13. März 2012 17:00
  • Hi,

    Did you try the one I posted above? it will show you all computers in the sccm console and all without a SCCM client.

    Regards,

    Jörgen


    -- My System Center blog ccmexec.com -- Twitter @ccmexec

    Dienstag, 13. März 2012 22:57
  • select sub.AssignedSite,        sum(sub.cnt ) 'Total Count',
            SUM(CASE SUB.ACTIVE0 when 1 then SUB.cnt END) AS 'Total Active Clients',
            SUM(CASE SUB.ACTIVE0 when 0 then SUB.cnt END) AS 'Total Inactive Clients',
            ROUND((CAST(SUM(CASE SUB.ACTIVE0 when 0 then SUB.cnt END) AS float) / SUM(sub.Cnt)) * 100,2) AS '% of Inactive Client Count'
    FROM
        (select sit.sms_assigned_sites0 AssignedSite,
                sys.active0,sys.obsolete0 ,
                COUNT(*) cnt
      from v_R_System sys
      join v_RA_System_SMSAssignedSites sit  on sys.resourceID=sit.resourceID
        and (sys.Active0 is not null and sys.Obsolete0 is not null)
    group by sit.sms_assigned_sites0,sys.active0,sys.obsolete0
    )sub
    group by sub.AssignedSite
    Montag, 19. März 2012 15:08
  • Hi,

    I tried above Query

    total system count & Total client Count & total non client count showing but i try to create Pie chat but pie chat showing y-axis not courrect.

    i need pie chat two diffrent colours clients & non Clients and total systems count number display aslo required Kindly help.


    Kiran

    Dienstag, 27. März 2012 07:27
  • Hi Kiran,

    Please check the below link,Where you can able to change the Chart-information (different typesof chart are available  in your case Pie-Chart)

    http://blog.quirkshop.co.uk/2010/06/30/customising-the-configuration-manager-dashboard-slightly/



    Narahari B


    • Bearbeitet Narahari B Mittwoch, 28. März 2012 13:47
    Mittwoch, 28. März 2012 13:47