How to create a report that has a site hierarchy view in Systems Management Server 2003 (886011)

MORE INFORMATION

You can use a customized SQL statement to create a report that has a site hierarchy view that includes the site code, level, site name, and server name. To do this, follow these steps:

1. Open the SMS Administrator console.
2. Expand Site Database (site_code - site_name).
3. Expand Reporting.
4. Right-click Reports, point to New, and then click Report.
5. In the Report Properties dialog box, enter a name in the Name box, select SMS Site - General in the Category list, and then click Edit SQL Statement.
6. Enter the following code into the SQL statement box, and then click OK.
   
   Note When you add the code to the SQL statement box, you must remove the code comments.

   /* Sites in an SMS hierarchy can be listed in hierarchy order with indentation to show */
   /* the site's depth in the hierarchy by using the following code sample in the Web     */
   /* report definition.  If a hierarchy has 7 sites that include AAA as a central site,  */
   /* BBB and CCC as primary sites under the central site, with DDD and EEE as secondary  */
   /* sites of BBB and FFF and GGG as secondary sites of CCC, the report will print the   */
   /* following:                                                                          */
   /*                                                                                     */
   /* SiteCode   Level  SiteName   ServerName                                             */
   /* ---------  -----  --------   ----------                                             */
   /* AAA        1      Site A     SERVER_A                                               */
   /*    BBB     2      Site B     SERVER_B                                               */
   /*       DDD  3      Site D     SERVER_D                                               */
   /*       EEE  3      Site E     SERVER_E                                               */
   /*    CCC     2      Site C     SERVER_C                                               */
   /*       FFF  3      Site F     SERVER_F                                               */
   /*       GGG  3      Site G     SERVER_G                                               */
   /*                                                                                     */
   /* You can see a demonstration of this in the SMS 2003 report                          */
   /*   "Site by hierarchy with time of last site status update."                         */
   
   set nocount on
   
   /* create a temporary table to hold the ordering information */
   
   create table #SiteOrder
   (
     SiteCode char(3),
     Level    int, /* depth in hierarchy, 1 = centeral site */
     PreOrder int identity, /* ordering for hierarchy order */
     PRIMARY KEY (Level,SiteCode)
   )
   
   declare @NextS char(3)
   declare @Level int
   
   set @Level=1
   set @NextS=''
   
   /* do a depth first walk of the hierarchy tree, listing the sites in pre-order */
   
   while @NextS is not null
   begin
      select @NextS=MIN(SiteCode) from v_Site where ReportingSiteCode=@NextS
        and SiteCode not in (select SiteCode from #SiteOrder where Level=@Level)
   
      if @NextS IS NULL
      begin
         set @Level=@Level-1
         select @NextS = MAX(SiteCode) from #SiteOrder where Level=@Level-1
      end
      else 
      begin
         insert into #SiteOrder(SiteCode,Level) values(@NextS,@Level)
         set @Level = @Level + 1
      end  
   end
     
   /* This is a sample query using the #SiteOrder table to list the sites in the v_Site view */
   /* in hierarchy order. Other queries can be listed in hierarchy order by joining to the   */
   /* #SiteOrder table by SiteCode and ordering by PreOrder. Use the Level column with the   */
   /* SPACE() function to provide indentation.                                               */
   
   select SPACE(3*(so.Level-1))+so.SiteCode as SiteCode, so.Level, s.SiteName, s.ServerName
   from #SiteOrder so
   join v_Site s on so.SiteCode=s.SiteCode
   order by so.PreOrder
   
   /* drop the temporary table at the end */
   
   drop table #SiteOrder
   
   set nocount off
   

7. Right-click Dashboards, point to New, and then click Dashboard.
8. On the General tab of the Dashboard Properties dialog box, type a name for the dashboard in the Name box.
9. In the Dashboard Properties dialog box, click the Reports tab, right-click a row in Dashboard reports, and then click Properties.
10. In the Select Report dialog box, click to select the report that you created in step 9, and then click OK two times.
11. Right-click the dashboard that you created in step 7, point to All Tasks, point to Run, and then click the server name. You can open a dashboard that has a site hierarchy view that includes the site code, level, site name, and server name.