Chris Stauffer at myITForum.com

Server Client Report

I need a report that would show all of the server that are located in SMS and it needed to tell me how many server had the client and how many where missing.

This sounds easy but in my environment I am responsible for reporting on 42 agencies. Some of the agencies are under our main Domain and some of the agencies are under a resource domain that they control. So the report needed to list each of 42 agencies.

So how do I do it if there are additional domains?

I create a collection for each agency using the following criteria.

1. Domain name
2. OU location
3. Site code

This allows me to get an accurate count for each agency even if they are in different domains.

I created all of the collections under a head collection called Agencies.

So it looks like this

All Agencies

àDOH

àDPW

àL&I

àetc….

So now I have a structure that I can use in SMS to control my reports.

In all of the reports that I have posted you may have noticed the variable for collid points to a query like this:

SELECT    v_Collection.CollectionID, v_Collection.Name

FROM         v_CollectToSubCollect INNER JOIN

                      v_Collection ON v_CollectToSubCollect.subCollectionID = v_Collection.CollectionID

WHERE     (v_CollectToSubCollect.parentCollectionID = 'PAC0004A')

ORDER BY v_Collection.Name

Basically what I am doing with this query is saying show me all of the collections listed under “All Agencies”

In order to get a count of servers I used this same type of query to give me the list of clients that are servers in each of these collections.

I want to thank Garth Jones for this next part.

He provided the knowledge for the basis of this report

http://smsug.ca/blogs/garth_jones/archive/2007/02/08/149.aspx

He also added a part to get rid of the temp table so it doesn’t view in SMS Web Reports.

http://smsug.ca/blogs/garth_jones/archive/2007/02/09/150.aspx

So now that I have a way to determine what agencies that I want to report on I needed a way to show how many server had a client and how many didn’t. I used a temp table based on what Garth posted above.

Here is the working report.

SET NOCOUNT ON

(SELECT      v_Collection.Name AS 'Department', COUNT(v_FullCollectionMembership.Name) as 'NOTok' into #SMSCliNot1

FROM         v_CollectToSubCollect INNER JOIN

                      v_Collection ON v_CollectToSubCollect.subCollectionID = v_Collection.CollectionID INNER JOIN

                      v_FullCollectionMembership ON v_Collection.CollectionID = v_FullCollectionMembership.CollectionID INNER JOIN

                      v_R_System ON v_FullCollectionMembership.ResourceID = v_R_System.ResourceID

WHERE     (v_CollectToSubCollect.parentCollectionID = 'PAC0004A') AND (v_R_System.Operating_System_Name_and0 LIKE '%Server%') AND

                      (v_R_System.Client0 is null )

GROUP BY v_Collection.CollectionID, v_Collection.Name)

(SELECT      v_Collection.Name AS 'Department', COUNT(v_FullCollectionMembership.Name) as 'CliOK' into #SMSCli1

FROM         v_CollectToSubCollect INNER JOIN

                      v_Collection ON v_CollectToSubCollect.subCollectionID = v_Collection.CollectionID INNER JOIN

                      v_FullCollectionMembership ON v_Collection.CollectionID = v_FullCollectionMembership.CollectionID INNER JOIN

                      v_R_System ON v_FullCollectionMembership.ResourceID = v_R_System.ResourceID

WHERE     (v_CollectToSubCollect.parentCollectionID = 'PAC0004A') AND (v_R_System.Operating_System_Name_and0 LIKE '%Server%') AND

                      (v_R_System.Client0 = '1' )

GROUP BY v_Collection.CollectionID, v_Collection.Name)

Select #SMSCli1.Department as 'Department'

                , CliOk as 'Clients installed'

                , NOTok as 'Clients missing' 

From

#SMSCli1 Full outer join #SMSCliNot1 on #SMSCli1.Department = #SMSCliNot1.Department

Where #SMSCli1.Department is not null

order by #SMSCli1.Department

drop table #SMSCli1

drop table #SMSCliNot1