Software Management Group

Attached a report i modified from one of the canned reports, I need help on adding a filter picker for it, so that I can run it against filter. Thanks

Hello,

See article: http://www.symantec.com/business/support/index?page=content&id=HOWTO21759&profileURL=https%3A%2F%2Fsymaccount-profile.symantec.com%2FSSO%2Findex.jsp%3FssoID%3D1395662346579E6du6Q66AoU54f2FW4RmD04pTnIvAaijr2AcN

Best Regards

still need help?

can you elaborate as im not fully following..

Tele,

Yes I do still need help, this is the original post https://www-secure.symantec.com/connect/forums/need-help-adding-filter-picker-modified-canned-software-report  it includes the reports I need help on.

I have yet to figure out how to target a filter when you use a count statement. I believe that cant be done but your drilldown report should.

I need to know where you want the filter... so if you were to target a filter.. what is the means so I know where to put it into the report.

so the drill down report shows machine name and user information.... which filter to target?

for Office I have an office report that does a count and cant target a filter as your counting all however you can create a new report that targets a filter... but what would you want it to gather? Machine Name, office version?

let me know and we will get something going... 

Thanks Tele,

A filter picker would work out great for the drilldown.  We can start out with Visio

tried adding it for you but my limited sql knowlege hurts.. hah..

so maybe u can figure out where to add it. it is somewhere in getting the right joins.

so if you do

Select t2.[ResourceGUID]

FROM [vCollection] t1 inner join

[CollectionMembership] t2 on t1.GUID = t2.CollectionGUID inner join

[Inv_Aex_AC_Identification] t3 on t2.ResourceGUID = t3._resourceGUID

WHERE t1.[Name] = 'All computers'

it will show you the resourceguid of all computers in the All Computers filter...

so then if you can get the joins to work right then you can create a new variable and add it. If you can get the joins I can help ya with the other part if you need...

your report returned results for me but when i tried adding in mine the results were none at that point. here is what i had... i also cleaned up some of the lines as I like indenting and putting the joins on a single line... makes it easier to follow.

DECLARE @v1_TrusteeScope nvarchar(max)
SET @v1_TrusteeScope = N'%TrusteeScope%'
DECLARE @temp TABLE (ResourceGuid UNIQUEIDENTIFIER)
INSERT INTO @temp
SELECT ResourceGuid FROM ScopeMembership sm
    JOIN fnGetTrusteeScopeCollections (@v1_TrusteeScope) tsc ON sm.ScopeCollectionGuid = tsc.ScopeCollectionGuid

SELECT DISTINCT i.Guid, i.Name [Computer], ISNULL (pu.[User],'') [User], vu.[Display Name] as Employee, vu.[Office Location], GU.[Department] as [Business Unit], Gu.[company] as [Division] 
FROM  vSoftwareComponent sc 
    LEFT JOIN ResourceAssociation ra ON sc.Guid = ra.ChildResourceGuid
    JOIN Inv_InstalledSoftware iis ON iis._SoftwareComponentGuid = sc.Guid
        AND iis.InstallFlag = 1
    LEFT JOIN RM_ResourceSoftware_Product sp ON sp.Guid = ra.ParentResourceGuid
        AND ra.ResourceAssociationTypeGuid = '9D67B0C6-BEFF-4FCD-86C1-4A40028FE483'
    JOIN vComputer i ON i.Guid = iis._ResourceGuid
    left JOIN Inv_AeX_AC_Primary_User pu ON pu._ResourceGuid = i.Guid
    JOIN @temp sm1 ON sm1.[ResourceGuid] = i.Guid 
    JOIN @temp sm2 ON sm2.[ResourceGuid] = sc.Guid
    LEFT JOIN vUser vu ON vu.[Name] = pu.[User] 
    Left Join Inv_Global_User_General_Details GU on GU.[Display name] = vu.[Display name]
    LEFT JOIN @temp sm3 ON sm3.[ResourceGuid] = sp.Guid
-- Added to place filter into joins
    join [CollectionMembership] t2 on i.GUID = t2.ResourceGuid
    join [vCollection] t1 on t2.CollectionGuid = t1.Guid
WHERE (('%Guid%' = '%') OR ('%Guid%' <> '%' AND (sp.Guid = '%Guid%' OR sc.Guid = '%Guid%')))
    AND LOWER (i.Name) LIKE LOWER ('%Computer%')
    AND LOWER (i.[Domain]) LIKE LOWER ('%Domain%') 
    AND i.Name LIKE '%Computer Name%'
    AND sc.Name LIKE '%Software Name%'
    AND t1.[Name] like '%FName%'

WOOOT!! think i got it!!!

try this... worked for me...

Attachment(s)

_SW - Drilldown to Installed Software (detailed User Info...xml   25 KB 1 version