Need count of model with specific version for chrome

Question

iam using SCCM view/tables for making piechart report. need count of models with specific version of google chrome.

using following query

select distinct v_R_System_Valid_Alias.Netbios_Name0 AS [Computer Name],
      v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedName AS [Product Name],
      [Publisher] = CASE when (ISNULL(v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedPublisher, N'-1') = N'-1') then @UnknownLoc
      Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedPublisher
      End,
      [Version] = CASE when (ISNULL(v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedVersion, N'-1')  = N'-1') then @UnknownLoc
      Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedVersion
      End,
      [Language] = CASE when (ISNULL(v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.Language0, -1) < 0) then @UnknownLoc
      Else CAST(v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.Language0 as nvarchar)
      End,
      ISNULL(v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.InstallType0, 0) as [Installation Type],
      v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.FamilyName AS [Product Family],
      v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.CategoryName AS [Product Category],
      v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.ProductID0 AS [ProductID],
      v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.SoftwareID as [Software ID],
      v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.CM_DSLID0 AS [DSL ID]
     FROM fn_rbac_GS_INSTALLED_SOFTWARE_CATEGORIZED(@UserSIDs) v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias
      INNER JOIN fn_rbac_R_System_Valid(@UserSIDs) v_R_System_Valid_Alias on v_R_System_Valid_Alias.ResourceID = v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.ResourceID   
    where productName0 like '%Google Chrome%'
      order by v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedName, Publisher, Version

it gives me following output

i need distinct version with count of model name output should be

version count(models)
 69.0    2000

i need to achieve this without cursor is this possible?

i need distinct version with count of computername that too distinct — deepti, Dec 30 '18 at 08:51
You can do with something like `select version,count( distinct comp_name) from table group by version`. For aggregation you can get distinct — Himanshu, Dec 30 '18 at 08:55

score 1 · Answer 1 · answered Dec 30 '18 at 08:54

you could the query as a subquery

select [Computer Name],  [Version], count(*)
from (
    select distinct v_R_System_Valid_Alias.Netbios_Name0 AS [Computer Name],
      v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedName AS [Product Name],
      [Publisher] = CASE when (ISNULL(v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedPublisher, N'-1') = N'-1') then @UnknownLoc
      Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedPublisher
      End,
      [Version] = CASE when (ISNULL(v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedVersion, N'-1')  = N'-1') then @UnknownLoc
      Else v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.NormalizedVersion
      End,
      [Language] = CASE when (ISNULL(v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.Language0, -1) < 0) then @UnknownLoc
      Else CAST(v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.Language0 as nvarchar)
      End,
      ISNULL(v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.InstallType0, 0) as [Installation Type],
      v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.FamilyName AS [Product Family],
      v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.CategoryName AS [Product Category],
      v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.ProductID0 AS [ProductID],
      v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.SoftwareID as [Software ID],
      v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.CM_DSLID0 AS [DSL ID]
     FROM fn_rbac_GS_INSTALLED_SOFTWARE_CATEGORIZED(@UserSIDs) v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias
      INNER JOIN fn_rbac_R_System_Valid(@UserSIDs) v_R_System_Valid_Alias on v_R_System_Valid_Alias.ResourceID = v_GS_INSTALLED_SOFTWARE_CATEGORIZED_Alias.ResourceID   
    where productName0 like '%Google Chrome%'
 ) t 
 order by [Computer Name],  [Version]

score -1 · Answer 2 · answered Dec 30 '18 at 12:39

Isn't this just a simple WHERE clause and aggregation?

SELECT isc.NormalizedVersion, COUNT(*)
FROM fn_rbac_GS_INSTALLED_SOFTWARE_CATEGORIZED(@UserSIDs) isc JOIN
     fn_rbac_R_System_Valid(@UserSIDs) sv 
     ON sv.ResourceID = isc.ResourceID   
WHERE isc.productName0 like '%Google Chrome%' AND
      isc.NormalizedVersion = N'69.0'
GROUP BY isc.NormalizedVersion;

Need count of model with specific version for chrome

2 Answers2