Client Management Suite

Objective: To generate a simple report that will generate the following info:

This code I have is very limited:

DECLARE @v1_TrusteeScope nvarchar(max)
   SET @v1_TrusteeScope = N'%TrusteeScope%'

SELECT vc.name, vc.[IP Address], arp.DisplayName, arp.DisplayVersion, arp.Publisher, arp.ParentDisplayName FROM Inv_AddRemoveProgram arp

JOIN vComputer vc ON vc.Guid=arp._ResourceGuid

WHERE DisplayName LIKE '%Office%'

Thanks,

the code sent was actually for a MS Office inventory on the machine.  Need similar for just the Plug-in agents.  Any advice or resource pointer is appreciated.

This should get you started:

I use this, But I do wish I could make it drill down

select [Agent Name], [Product Version], [Count] = count(*) from dbo.Inv_AeX_AC_Client_Agent 
join vItem on _ResourceGuid = Guid 
group by [Agent Name], [Product Version] 
order by [Agent Name], [Product Version] 

Thank You Andrew!  Perfecto!! exactly what I needed.

Mick, I like your idea of drilling down too ... it can be very useful!  Thanks for your reply!

Andrew, can you add the IP address colum after the Name?    Where can I find good resources for learning SQL reporting? if you know any links, please share it with us.  Thanks-

Whoops!  IP is actually available in the query just need to add it to the SELECT statement.  Right after vc.Name, hit Enter, add a comma, and then add this line:

,ip.[IP Address]

You are the best!  Thank You Andrew!!!

Thanks this Idea follower1

did you speak French ?

For those would like the good old Agents/Plugins version reports, was available into NS6, not any more into NS7.1 !!

Here it is join, work well, also missing drill-down in there ! :) Just import

Yep, fun, the NS7 ask for confirming "relocation" an existing - but don't search it, an orphan item ???

Attachment(s)

All Client Agent Version with plugins.xml   5 KB 1 version

Hello Andrew,  thanks for your previews help.  I am still learning SQL reporting. 

Can you help me on adding a column on the report after the (YES, NO) to display the Agent Version number.  I couldn't find the right table to use.  --Thanks.

Wow!  Great idea.  and it is working great!  Y'are the man!

Thanks again,

F1.

NOTE: This is report is a must for those that are maintaining the computers themselves.  If you use this, make sure you remember Andrew for creating it!!!!  And if you have other reports that are helpful, please by all means ... post it and share it with the community. (V7.1)

Taking Andrew's work and a little artistic license, I adpated the query & report to suit my environment.

I noticed his report includes IP address which I assume is because there is a network discovery being performed and that is how the computers are being populated in the CMDB, hence the Yes/No for the "Agent Installed?" column.

Since I am only tracking computers that have already been migrated/installed and am not importing computers via AD imports or network discovery I removed the IP address fields and related SQL code.

I also want to only track computers that are not Retired so I added the IsManaged flag as well setting it to 1.

I also wanted to know when the last time the core agent "checked in" to the console so I added the Client Date from the AC_Ident table.

What this provides is a very comprehensive overview of what your active managed client environment looks like with addtional details for agent health and all agent versions.

The rpeort .XML is attached to the post for easy importing.

Here is the SQL code:

SELECT DISTINCT vc.Name
               ,CASE WHEN core._ResourceGuid IS NOT NULL THEN core.[Product Version] ELSE '' END [Core Agent]
               ,CASE WHEN cldate._ResourceGuid IS NOT NULL THEN cldate.[Client Date] ELSE '' END [Core Agent Date]
               ,CASE WHEN iu.[Collection Time] IS NOT NULL THEN iu.[Collection Time] ELSE '' END [Last Inventory Update]
               ,CASE WHEN inv._ResourceGuid IS NOT NULL THEN inv.[Product Version] ELSE '' END [Inventory]
               ,CASE WHEN am._ResourceGuid IS NOT NULL THEN am.[Product Version] ELSE '' END [Application Metering]
               ,CASE WHEN sms._ResourceGuid IS NOT NULL THEN sms.[Product Version] ELSE '' END [Software Management]
               ,CASE WHEN sua._ResourceGuid IS NOT NULL THEN sua.[Product Version] ELSE '' END [Software Update]
               ,CASE WHEN dep._ResourceGuid IS NOT NULL THEN dep.[Product Version] ELSE '' END [Deployment]
               ,CASE WHEN pca._ResourceGuid IS NOT NULL THEN pca.[Product Version] ELSE '' END [pcAnywhere]

FROM vComputer vc

LEFT JOIN(SELECT _ResourceGuid FROM Inv_AeX_AC_Client_Agent WHERE LOWER([Agent Name]) = 'altiris agent') ai
   ON ai._ResourceGuid = vc.Guid
LEFT JOIN(SELECT _ResourceGuid, [Product Version] FROM Inv_AeX_AC_Client_Agent WHERE LOWER([Agent Name]) = 'altiris agent') core
   ON core._ResourceGuid = vc.Guid
LEFT JOIN(SELECT _ResourceGuid, [Client Date] FROM Inv_AeX_AC_Identification) cldate
   ON cldate._ResourceGuid = vc.Guid
LEFT JOIN(SELECT _ResourceGuid, MAX([Collection Time]) AS [Collection Time] FROM Inv_Inventory_Results GROUP BY _ResourceGuid) iu
   ON iu._ResourceGuid = vc.Guid
LEFT JOIN(SELECT _ResourceGuid, [Product Version] FROM Inv_AeX_AC_Client_Agent WHERE LOWER([Agent Name]) = 'altiris inventory agent') inv
   ON inv._ResourceGuid = vc.Guid
LEFT JOIN(SELECT _ResourceGuid, [Product Version] FROM Inv_AeX_AC_Client_Agent WHERE LOWER([Agent Name]) = 'deployment solution plug-in') dep
   ON dep._ResourceGuid = vc.Guid
LEFT JOIN(SELECT _ResourceGuid, [Product Version] FROM Inv_AeX_AC_Client_Agent WHERE LOWER([Agent Name]) = 'symantec pcanywhere agent') pca
   ON pca._ResourceGuid = vc.Guid
LEFT JOIN(SELECT _ResourceGuid, [Product Version] FROM Inv_AeX_AC_Client_Agent WHERE LOWER([Agent Name]) = 'altiris software update agent') sua
   ON sua._ResourceGuid = vc.Guid
LEFT JOIN(SELECT _ResourceGuid, [Product Version] FROM Inv_AeX_AC_Client_Agent WHERE LOWER([Agent Name]) = 'altiris application metering agent') am
   ON am._ResourceGuid = vc.Guid
LEFT JOIN(SELECT _ResourceGuid, [Product Version] FROM Inv_AeX_AC_Client_Agent WHERE LOWER([Agent Name]) = 'software management solution agent') sms
   ON sms._ResourceGuid = vc.Guid

WHERE vc.[IsManaged] = 1

ORDER BY vc.Name ASC

Attachment(s)

Symantec Management Agent & Agent Plug-in Versions.zip   3 KB 1 version

It works beautifully!   First time I import  report on *.XML format, and it imported and created the report easily.  Thanks!!

This is the report we are using.

Attachment(s)

All Machine Agent Versions.xml   12 KB 1 version

Andrew or any of the gurus here, any ideas on why the report shows date like "01/01/1900" on the 'Last Inventory Update" ?  the report XML in use is the code aboved date 29.AUG.2011 from Andrew ...  only a few shows '01/01/1900", the rest are accurate.

Also, on 'SilenCastle" XML above the column "Last Configuration Request" has many blanks, but most have the accurate date.  Does this has anything to do with NULL values?

Thanks,

{F1}

Hi, Follower1:

I would bet my money to a default value in a column which wouldn't allow NULLS.

And at the end, it makes sense to have an "impossible" value, so you can "know" that these machines have an issue, but at the same time, it simplifies making calculations with this column when launching queries, as you are sure that not NULLs are possible in this column.

Thanks Falquian. NULLS seems the problem.