Who’s the Man Blog

 As part of a implementation at work I have been trying to integrate applications (mainly SQL backend) applications into Windows Sharepoint Services 3.0 and MOSS 2007. While What’s Up uses SQL it comes with either MSDE 2000 or SQL 2005 Express in order to access it via the network you will need t enable TCP/IP access. I used Sharepoint Designer 2007 and created a data view on a test page and then exported the web part and imported into our layout based main page(a good way to get a custom web part into a ghosted/attached layout page). The problem was finding the correct SQL query to generate a table of down devices from Whats Up Gold here is the query I used at first which just gives the device and how long it has been down which is just the default query from What’sUp Dynamic Groups.

SELECT DISTINCT Device.nDeviceID FROM Device JOIN MonitorState ON Device.nWorstStateID = MonitorState.nMonitorStateID WHERE Device.bRemoved = 0 AND MonitorState.nInternalMonitorState = 1

Here is the webpart it displays the time down is different colors yellow for currently down, orange for 2 mins, and red for 5 mins or more to customize to your enviroment replace whatsupserver with your server name and username and password with yours:

<webParts>
  <webPart xmlns=”http://schemas.microsoft.com/WebPart/v3“>
    <metaData>
      <type name=”Microsoft.SharePoint.WebPartPages.DataFormWebPart, Microsoft.SharePoint,

Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” />
      <importErrorMessage>Cannot import this Web Part.</importErrorMessage>
    </metaData>
    <data>
      <properties>
        <property name=”PageSize” type=”int”>-1</property>
        <property name=”HelpMode” type=”helpmode”>Modeless</property>
        <property name=”Height” type=”string” />
        <property name=”SampleData” type=”string” null=”true” />
        <property name=”AllowConnect” type=”bool”>True</property>
        <property name=”CatalogIconImageUrl” type=”string” />
        <property name=”XslLink” type=”string” null=”true” />
        <property name=”AllowClose” type=”bool”>True</property>
        <property name=”Hidden” type=”bool”>False</property>
        <property name=”MissingAssembly” type=”string”>Cannot import this Web Part.</property>
        <property name=”TitleIconImageUrl” type=”string” />
        <property name=”ChromeState” type=”chromestate”>Normal</property>
        <property name=”FireInitialRow” type=”bool”>True</property>
        <property name=”Description” type=”string” />
        <property name=”AllowMinimize” type=”bool”>True</property>
        <property name=”Xsl” type=”string”>
<xsl:stylesheet xmlns:xs=”http://www.w3.org/2001/XMLSchema”

xmlns:msdata=”urn:schemas-microsoft-com:xml-msdata” version=”1.0″ exclude-result-prefixes=”xsl

msxsl ddwrt” xmlns:ddwrt=”http://schemas.microsoft.com/WebParts/v2/DataView/runtime”

xmlns:asp=”http://schemas.microsoft.com/ASPNET/20”

xmlns:__designer=”http://schemas.microsoft.com/WebParts/v2/DataView/designer”

xmlns:xsl=”http://www.w3.org/1999/XSL/Transform” xmlns:msxsl=”urn:schemas-microsoft-com:xslt”

xmlns:SharePoint=”Microsoft.SharePoint.WebControls” xmlns:ddwrt2=”urn:frontpage:internal”>
 <xsl:output method=”html” indent=”no”/>
 <xsl:decimal-format NaN=”"/>
 <xsl:param name=”dvt_apos”>’</xsl:param>
 <xsl:variable name=”dvt_1_automode”>0</xsl:variable>
 
 <xsl:template match=”/” xmlns:xs=”http://www.w3.org/2001/XMLSchema”

xmlns:msdata=”urn:schemas-microsoft-com:xml-msdata”

xmlns:asp=”http://schemas.microsoft.com/ASPNET/20”

xmlns:__designer=”http://schemas.microsoft.com/WebParts/v2/DataView/designer”

xmlns:SharePoint=”Microsoft.SharePoint.WebControls”>
  <xsl:call-template name=”dvt_1″/>
 </xsl:template>
 
 <xsl:template name=”dvt_1″>
  <xsl:variable name=”dvt_StyleName”>RepForm1</xsl:variable>
  <xsl:variable name=”Rows” select=”/dsQueryResponse/NewDataSet/Row” />
  <xsl:variable name=”dvt_RowCount” select=”count($Rows)” />
  <xsl:variable name=”IsEmpty” select=”$dvt_RowCount = 0″ />
  <table border=”0″ width=”100%”>
   <xsl:call-template name=”dvt_1.body”>
    <xsl:with-param name=”Rows” select=”$Rows” />
    
    </xsl:call-template>
  </table>
  </xsl:template>
 <xsl:template name=”dvt_1.body”>
  <xsl:param name=”Rows” />
  <xsl:for-each select=”$Rows”>
     <xsl:call-template name=”dvt_1.rowview”

/></xsl:for-each>
 </xsl:template>
 <xsl:template name=”dvt_1.rowview”>
  <tr>
   <td width=”100%” colspan=”2″ class=”ms-vb”>
    
   </td>
  </tr>
  <tr>
   <td width=”100%” colspan=”2″ class=”ms-vb”>
    <a

href=”http://whatsupserver/NmConsole/Workspace/DeviceStatus/DeviceStatus.asp?nDeviceID={@nDeviceID

}”><xsl:value-of select=”@sDisplayName” /></a>
    <xsl:text

xmlns:ddwrt=”http://schemas.microsoft.com/WebParts/v2/DataView/runtime”

ddwrt:nbsp-preserve=”yes” disable-output-escaping=”yes”>&amp;nbsp;
    
    </xsl:text>- <span>
    <xsl:attribute name=”style”>
     <xsl:if test=”normalize-space(@sStateName) =

‘Down’”>color: #FFFF00;</xsl:if>
     <xsl:if test=”normalize-space(@sStateName) = ‘Down at

least 2 min’”>color: #FF9900;</xsl:if>
     <xsl:if test=”normalize-space(@sStateName) = ‘Down at

least 5 min’ or normalize-space(@sStateName) = ‘Down at least 20 min’”>color: #FF0000;

font-size: inherit;</xsl:if>
    </xsl:attribute>
    <xsl:value-of select=”@sStateName”

disable-output-escaping=”yes” />
    </span><br /></td>
  </tr>
  <xsl:if test=”$dvt_1_automode = ‘1′” ddwrt:cf_ignore=”1″>
   <tr>
    <td width=”100%” colspan=”2″ class=”ms-vb”>
     <span ddwrt:amkeyfield=”nDeviceID,nMonitorStateID”

ddwrt:amkeyvalue=”concat(ddwrt:EscapeDelims(string(@nDeviceID)),’,',ddwrt:EscapeDelims(string(@n

MonitorStateID)))” ddwrt:ammode=”view” />
    </td>
   </tr>
  </xsl:if>
 </xsl:template>
 </xsl:stylesheet> </property>
        <property name=”ListName” type=”string” null=”true” />
        <property name=”ChromeType” type=”chrometype”>Default</property>
        <property name=”AllowEdit” type=”bool”>True</property>
        <property name=”ShowWithSampleData” type=”bool”>False</property>
        <property name=”Direction” type=”direction”>NotSet</property>
        <property name=”DataFields”

type=”string”>@nDeviceID,nDeviceID;@sDisplayName,sDisplayName;@nMonitorStateID,nMonitorStateID;@

sStateName,sStateName;@nInternalMonitorState,nInternalMonitorState;@nInternalStateTime,nInternal

StateTime;@nStateFillColor,nStateFillColor;@nStateLineColor,nStateLineColor;@nShapePoints,nShape

Points;@nShapeInnerRadius,nShapeInnerRadius;@nShapeOuterRadius,nShapeOuterRadius;@nShapeDirectio

n,nShapeDirection;@bSystemType,bSystemType;</property>
        <property name=”ViewContentTypeId” type=”string” />
        <property name=”Width” type=”string” />
        <property name=”CacheXslTimeOut” type=”int”>86400</property>
        <property name=”TitleUrl” type=”string” />
        <property name=”DataSourceID” type=”string” />
        <property name=”HelpUrl” type=”string” />
        <property name=”CacheXslStorage” type=”bool”>True</property>
        <property name=”DataSourcesString” type=”string”><%@ Register TagPrefix=”sharepoint”

Namespace=”Microsoft.SharePoint.WebControls” Assembly=”Microsoft.SharePoint, Version=12.0.0.0,

Culture=neutral, PublicKeyToken=71e9bce111e9429c” %><sharepoint:SPSqlDataSource

runat=”server” AllowIntegratedSecurity=”False” ConnectionString=”Data

Source=whatsupserver\whatsup;User ID=username;Password=password;Initial Catalog=WhatsUp;”

ProviderName=”System.Data.SqlClient” SelectCommand=”SELECT DISTINCT Device.nDeviceID,

Device.sDisplayName, MonitorState.*  FROM         Device INNER JOIN                       

MonitorState ON Device.nWorstStateID = MonitorState.nMonitorStateID  WHERE     (Device.bRemoved

= 0) AND (MonitorState.nInternalMonitorState = 1)”

ID=”WhatsUp_x0020_Server_x0020_Down_x0020_Devices1″></sharepoint:SPSqlDataSource>
</property>
        <property name=”ExportMode” type=”exportmode”>All</property>
        <property name=”AllowZoneChange” type=”bool”>True</property>
        <property name=”Default” type=”string” />
        <property name=”UseSQLDataSourcePaging” type=”bool”>True</property>
        <property name=”ParameterBindings” type=”string”>
       <ParameterBinding Name=”dvt_apos”

Location=”Postback;Connection”/>
       <ParameterBinding Name=”UserID”

Location=”CAMLVariable” DefaultValue=”CurrentUserName”/>
       <ParameterBinding Name=”Today”

Location=”CAMLVariable” DefaultValue=”CurrentDate”/>
      </property>
        <property name=”DisplayName” type=”string” />
        <property name=”NoDefaultStyle” type=”string”>TRUE</property>
        <property name=”Title” type=”string”>WhatsUp Server Down Devices</property>
        <property name=”AllowHide” type=”bool”>True</property>
        <property name=”ViewFlag” type=”string”>0</property>
      </properties>
    </data>
  </webPart>
</webParts>

And you end up with something that looks like this:

 I hope this helps someone besides myself.