Computers with Chrome or Firefox

Necessary code for linked report “Computers with Firefox or Google Chrome” –> “Computers with a particular installed Product Name”

I’ve updated my method of doing this now, and the report is much simpler.

“Computers with Firefox or Chrome”

Select top 100
CASE WHEN CAT.NormalizedName like 'Mozilla Firefox%'
THEN SUBSTRING(CAT.NormalizedName, 0, 16)
ELSE CAT.NormalizedName
END as [Product Name],
CAT.NormalizedPublisher as [Publisher],
count(CAT.ResourceID) as [Instance Count],
'CCP00437' as [Collection ID]
from v_GS_INSTALLED_SOFTWARE_CATEGORIZED as CAT with (NOLOCK)
Where
(CAT.NormalizedName like 'Mozilla Firefox%')
OR
(CAT.NormalizedName like '%Google Chrome%') 
group by
CASE WHEN CAT.NormalizedName like 'Mozilla Firefox%'
THEN SUBSTRING(CAT.NormalizedName, 0, 16)
ELSE CAT.NormalizedName
END,
CAT.NormalizedPublisher

order by [Instance Count] desc

“Computers with a particular application”

select SCCM.Name0
,CAT.[ProductName0]
,CAT.[ProductVersion0]
,CAT.[Publisher0]
,CAT.[NormalizedName]</code>

FROM v_GS_INSTALLED_SOFTWARE_CATEGORIZED as CAT
join v_R_System as SCCM on CAT.ResourceID = SCCM.ResourceID
where CAT.[ProductName0] like '%' + @ProductName0 + '%'
order by CAT.[ProductVersion0] desc

///////////////////////////////////////////////———–OLD METHOD BELOW THIS POINT———///////////////////////////////

“Computers with Firefox or Google Chrome”

Select TOP (convert(bigint,@NumberOfRows))
CAT.NormalizedName as  [Product Name],
CAT.NormalizedPublisher as [Publisher],
count(CAT.ResourceID) as [Instance Count],
@CollectionID as [Collection ID]</code>

from v_GS_INSTALLED_SOFTWARE_CATEGORIZED as CAT with (NOLOCK)
Where  CAT.ResourceID IN
(
select distinct v_FullCollectionMembership.ResourceID
FROM v_FullCollectionMembership inner join v_R_System_Valid
ON v_R_System_Valid.ResourceID = v_FullCollectionMembership.ResourceID
where CollectionID = @CollectionID
)
AND
(CAT.NormalizedName like 'Mozilla Firefox%')
OR
(CAT.NormalizedName like '%Google Chrome%')
group by CAT.NormalizedName, CAT.NormalizedPublisher

PROMPTS FOR SEARCH BY BROWSERS – Collections

NAME - @CollectionID
Prompt Text - Collection (defaults to 'All Win7 Workstations')
Default Value- CCP00437</code>

SQL - begin
if (@__filterwildcard = '')
select CollectionID, Name from v_Collection order by Name
else
select CollectionID, Name from v_Collection
WHERE CollectionID like @__filterwildcard
order by Name
end

PROMPTS FOR SEARCH BY BROWSERS – Number of Rows

NAME - @NumberOfRows
Prompt text - Specify the number of rows
Default Value - 100</code>

Finalized Search by Product Report

select CAT.[ResourceID],SCCM.Name0,CAT.[ProductName0],CAT.[ProductVersion0],CAT.[Publisher0],CAT.[Language0],CAT.[SoftwarePropertiesHash0],CAT.[NormalizedName]
FROM v_GS_INSTALLED_SOFTWARE_CATEGORIZED as CAT
join   v_R_System as SCCM on CAT.ResourceID = SCCM.ResourceID
where CAT.[ProductName0] = @ProductName0
order by CAT.[ProductVersion0] desc

PROMPTS for SEARCH BY PRODUCT NAME

Name - @ProductName0
PromptText - Specify Product Name
Advertisements

One thought on “Computers with Chrome or Firefox

Have a code issue? Share your code by going to Gist.github.com and pasting your code there, then post the link here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s