Client Management Suite

Hi there, we use managed software deliveries for alot of installation tasks like rolling out adobe reader and flash to all clients. The compliance reports generated from these policies indicate those that are in compliance and not, which is great!

The problem i have is i need to resolve the clients that are not in compliance, to do this i will assign the clients the relevant support teams across our multiple sites. what would be really handy is if i could add the location of every client machine in the compliance 

I was thinking of using the site server the client is attached to as we assign the clients to each ss via ip but i really haven't got a clue on how to add this into a custom report.

Any help would be great, thanks

Tom

I'd be interested to hear this as well.  I went digging a little bit, but not being a SQL guy, I couldn't figure this out.  I know you'll start by cloning the Software Compliance Status report, and then you'll modify the Right Click menu so that the cloned Software Compliance Status report appears as an option when you're right-clicking a Managed Software Delivery, but as for how the database associates a site server with a computer, I'm a little lost on what table that's in.

Site Server is an interesting notion, since a Site Server can be an OOB server, Package Server, Task Server, Deployment Server...

So here's the query for Task Server that we use for a custom token:

select c.name from TaskTargetDeviceCache vc left outer join Inv_Client_Task_Resources ctr on ctr._ResourceGuid = vc.Guid And vc.Guid = '%COMPUTERID%' left outer join Inv_Client_Task_Servers cts on cts.ClientTaskServerGuid = ctr.ClientTaskServerGuid Join vcomputer c on cts._ResourceGuid = c.guid

That will obviously only get you started, but it points in the right direction.  Let me know if you need something different, more, or whatever.  :-)

Hi Thomas.

From the talk of site servers and your script not working on my v6 install, I gather that this is specific to Altiris v7?

I'm assuming that in v6 you'll want a package server?  Sites in v6 only included package servers.  So would that be a safe assumption?

Hi Thomas, my specific request was for Version 7

The report i would like to add the site server to is the 'Software Compliant Detailed Summary'

the query for this is so far 

 

DECLARE @v1_TrusteeScope    nvarchar(max)

SELECT  @v1_TrusteeScope  = N'%TrusteeScope%'

 

DECLARE @MaxRow             int,

        @Policies           nvarchar(128),

        @Computers          nvarchar(128),

        @CheckLicense       int,

        @ComplianceStatus   int

 

SELECT  @MaxRow = CASE WHEN '%flag%' = '-1' THEN %Maximum Number of Results to Include%

                                            ELSE 100000 END  -- ROWCOUNT default value

SELECT  @Policies      = LOWER(N'%Policies to Include%'),

        @Computers     = LOWER(N'%Computers to Include%'),

        @CheckLicense  = CASE LOWER('%Only Include Licensed Computers%') WHEN 'yes' THEN 1 ELSE 0 END

 

IF ( N'%Compliance Status%' = N'%' )

    SELECT @ComplianceStatus = 8   -- Any

ELSE IF ( N'%Compliance Status%' = ISNULL(dbo.fnLocalizeString('item.name.in compliance',     'E9C0F270-D983-4385-8C36-DF0559FC9DE6','%_culture%'), 'In Compliance') )

    SELECT @ComplianceStatus = 3   -- In Compliance

ELSE IF ( N'%Compliance Status%' = ISNULL(dbo.fnLocalizeString('item.name.not in compliance', 'E9C0F270-D983-4385-8C36-DF0559FC9DE6','%_culture%'), 'Not In Compliance') )

    SELECT @ComplianceStatus = 0   -- Not In Compliance

ELSE 

    SELECT @ComplianceStatus = 1   -- Unknown

 

SELECT      DISTINCT TOP (@MaxRow)

            vc.Name         AS [Computer Name],

            s.String        AS [Policy Name],

            pcs.PolicyGuid  AS [PolicyGuid],

            CASE pcs.Compliance

                WHEN 3 THEN ISNULL(dbo.fnLocalizeString('item.name.in compliance',     'E9C0F270-D983-4385-8C36-DF0559FC9DE6','%_culture%'), 'In Compliance')

                WHEN 0 THEN ISNULL(dbo.fnLocalizeString('item.name.not in compliance', 'E9C0F270-D983-4385-8C36-DF0559FC9DE6','%_culture%'), 'Not In Compliance')

                       ELSE ISNULL(dbo.fnLocalizeString('item.name.unknown',           'E9C0F270-D983-4385-8C36-DF0559FC9DE6','%_culture%'), 'Unknown')

            END             AS [Status]

    FROM    Inv_Policy_Compliance_Status pcs

    JOIN    dbo.fnGetTrusteeScopedResources(@v1_TrusteeScope) tsr ON tsr.ResourceGuid = pcs._ResourceGuid

    JOIN  ( SELECT      MAX(CreatedDate) [Time],

                        _ResourceGuid,

                        PolicyGuid,

                        Compliance

                FROM    InvHist_Policy_Compliance_Status

                WHERE ( @ComplianceStatus = 8 OR Compliance = @ComplianceStatus )

                AND     CreatedDate >= '%Start Date%'

                AND     CreatedDate <= '%End Date%'

               GROUP BY _ResourceGuid, PolicyGuid, Compliance

          ) AS                           pch ON pch._ResourceGuid = tsr.ResourceGuid AND pch.PolicyGuid = pcs.PolicyGuid AND pch.Compliance = pcs.Compliance

    JOIN    vComputer                           vc   ON pcs._ResourceGuid = vc.Guid

    JOIN    ItemClass                           ic   ON ic.Guid = pcs.PolicyGuid AND ic.ClassGuid ='2D3A170E-5028-4570-BA0C-3DB775CB8BDE'

    JOIN    String                              s    ON s.BaseGuid = ic.Guid AND s.StringRef = 'item.name'

    WHERE   LOWER(s.String) LIKE @Policies

    AND     LOWER(vc.Name)  LIKE @Computers

    AND   ( @CheckLicense = 0 OR

            vc.Guid IN (

                SELECT      lu.ResourceGuid

                    FROM    LicenseInUse lu

                    JOIN    LicenseStatus ls ON lu.LicensingPolicyGuid = ls.LicensingPolicyGuid

                    WHERE   ls.Status LIKE 'Ok' AND ls.ProductGuid='AD3F5980-D9E9-11D3-A318-0008C7A09198' )

          )

    ORDER BY [Status], [Policy Name], [Computer Name]

like as a far-right column?  The report is already pretty slow... I'm not sure that'd be a good idea.

the good news is you already have vComputer there (vc).  The bad news is you'd have to add 2 more join statements probably, maybe a 3rd, and slow the report significantly.  Oh, and join to VC again as a diff alias.  <sigh>  hmmm...  Can it be done?  Yes.  Should it be done... questionable.

Are you sure you want Site Server in this report? What about IP addy instead (a lot easier)?

IP Addy is a step in the right direction yes as i can tell what site they are from,  then assign to the relevant team to troubleshoot the installation.

Only reason i suggested site server is purely cosmetic and nice for management to see i suppose:)

thanks

Tom

Another option would be if i export a list of computer names from the above report. I could then create a list of computer names.

It would be good if i could run these against a report to give me their IP address or even better which site server they are attached?

Not sure if this is any easier?

Thanks

If you had to pare-down the report to the bare minimum, what is management requiring?  I understand you want site servers, and we can work on that.  However, do they need everything else already in the canned report?  Maybe there's a few things we can take out that would help speed up the reporting once we add things in to slow it down?

Oh, and in it's current state, how long does it take to get results from the report?

Okay, at the moment i Select "Software Compliance by managed Delivery" and this takes around 30 seconds to load. I then choose the policy i want more info on say some Adobe Reader one and select "Software compliance detail report" this report loads within 10 seconds normally quicker.

Here you get the policy name, computer name, policy status and date/time.

The only one i really dont need is date/time as this only reports when the policy was changed. All i really need is the ip or site server.

If i take the Adobe report for an example, i have 200 or so computers that are non-compliant. I want to basically divide the computers into specific sites and assign to the relevant "site teams" to diagnose.

Thanks for sticking with me on this.

Tom

Hi Thomas, did you have any luck with this report? thax in advance

Sorry to bring this up again but i could really do with this report so if anyone has any ideas on how to get the report im after that would be great. 

How does everyone else deal with this scenario?

thanks

that you can edit directly, sort of.

Here's what you do.  Select the Software Compliance by Managed Delivery report, right-click in the left pane on it, choose clone.  Do the same thing for the drill-down report which is Software Compliance Details by Managed Delivery Policy.  This should leave you with 2 cloned reports.

Now, make the first report use the second one by editing it and pointing it to the second cloned report.  Now you can play with the drill-down.

Finally, add in

               ,
               vc.[ip address]

Basically, you have to put the comma at the end of the vi.ModifiedDate and then add the new field.  We're already joined on VComputer, which has IP Address.  You'll have to put this in 2 places though, and be sure to catch both.

Getting the site server will require a couple more join statements and slow the query down.  If you can live with IP address, you should be good to go and there should be literally no speed impact at all, that is measurable by any tool you and I have anyway...
 

...to report on the Site that a particular computer was located in.  It was a big headache, but I finally got it.  Assuming you have NS sites setup (which probably have friendlier names than the PS name/IP address), this should be doable.  I'll try to find it when I'm back at work tomorrow, but it seems that I had to use Inv_AeX_AC_TCPIP, vSite, and vPackageServerServingSite tables/views.

Thanks Thomas, ill give this ago. And if you can Kyle that would be great aswell, would be alot easier using the site server option.

Cheers

The IP option have proven to be the best option for me, this way i can now export the detials and confirm location and network via the ip details (much more granular than the site server option)

Only other thing i would like to add to the report if at all possible is primary user?

Thanks for your help, this has proven very useful.

Hi Tom,

I posted the report here (it may not be approved yet, so you may have to try back later on today).  Hopefully it gives you what you need, or at least a foundation!

You can find it here:

https://www-secure.symantec.com/connect/downloads/package-servers-site-name-subnet-report

Thanks!

Cheryl