How to Show Bundle Usage

[mark75081]

Is there a setting to see which bundles are actually use and not used? Would like to clean up some if possible!

[CRAIGDWILSON]

mark7508;2348023 wrote:
Is there a setting to see which bundles are actually use and not used? Would like to clean up some if possible!

The ZENworks Reporting Server can show the last time a bundle was used on each device.

[spencapl]

mark7508;2348023 wrote:
Is there a setting to see which bundles are actually use and not used? Would like to clean up some if possible!

Hi,

This may or maynot help depending on your DB but I have this SQL query for looking for bundles with no device relationship. You still need to have a manual look through and see what you get.

[INDENT]IF OBJECT_ID('tempdb..#zrs_assignment') IS NOT NULL
BEGIN
drop table #zrs_assignment
END
GO

BEGIN
WITH parentobjects (Zuid,parentuid)
AS
(
select z.ZUID,z.ParentUID from zZENObject z
WHERE z.PrimaryType='Device'
UNION ALL
select p.ZUID,z.ParentUID from zZENObject z
INNER JOIN parentobjects p
ON p.ParentUID=z.zuid
and z.ParentUID is not null
)
SELECT allcontents.contentuid,
allconsumers.deviceuid AS consumeruid,
zAssignment.type into #zrs_assignment
FROM
(SELECT zb1.zuid AS asnblcontntuid , zb1.zuid AS contentuid FROM zBundle zb1
UNION
SELECT zp1.zuid AS asnblcontntuid , zp1.zuid AS contentuid FROM zPolicy zp1
UNION
SELECT zg1.groupuid AS asnblcontntuid ,
zg1.memberuid AS contentuid
FROM zgroupings zg1
)allcontents
INNER JOIN zAssignment
ON zAssignment.contentuid=allcontents.asnblcontntuid
INNER JOIN
(SELECT Zuid as deviceuid,parentuid as assignableconsumer FROM parentobjects
UNION
SELECT zgroupings.memberuid AS deviceuid,
zgroupings.groupuid AS assignableconsumer
FROM zgroupings
WHERE zgroupings.type IN ('Device Group','Query Group')
UNION
SELECT zd1.zuid AS deviceuid, zd1.zuid AS assignableconsumer FROM zDevice zd1
) allconsumers ON allconsumers.assignableconsumer = zAssignment.consumeruid
UNION
SELECT contentuid,
consumeruid,
type
FROM zAssignment zasst
WHERE zasst.type='Bundles/Users'
OR zasst.type ='Policies/Users'
UNION
SELECT objectUID AS contentuid ,
targetDeviceUID AS consumeruid,
NULL AS type
FROM zObjectInfo zof1
WHERE source='U'

SELECT
zAssignableContentObject3.Version,
contentobject.*
FROM
zZENObject contentobject INNER JOIN zAssignableContentObject zAssignableContentObject3
ON (contentobject.ZUID=zAssignableContentObject3.ZUID
AND (zAssignableContentObject3.Version!= -1)
AND zAssignableContentObject3.releaseuid is null)
WHERE
contentobject.PrimaryType='Bundle' and contentobject.Path not like '/System%' and zAssignableContentObject3.Version!= -1
and contentobject.zuid not in
(SELECT contentobject.ZUID
FROM zReference
RIGHT OUTER JOIN #zrs_assignment zrs_assignment
ON (zReference.ZUID=zrs_assignment.consumeruid)
RIGHT OUTER JOIN zZENObject contentobject
ON (zrs_assignment.contentuid=contentobject.ZUID)
INNER JOIN zAssignableContentObject zAssignableContentObject3
ON (contentobject.ZUID =zAssignableContentObject3.ZUID
AND (zAssignableContentObject3.Version! = -1)
AND zAssignableContentObject3.releaseuid IS NULL)
INNER JOIN zZENObject consumertab1
ON (consumertab1.ZUID =zrs_assignment.consumeruid
AND (consumertab1.PrimaryType ='Device'
OR consumertab1.PrimaryType ='External Reference' ))
WHERE contentobject.PrimaryType='Bundle'
AND contentobject.Path NOT LIKE '/System%'
AND zAssignableContentObject3.Version!= -1
)
END
GO
[/INDENT]

Thanks

Spencer