Page customization question ( sql stuff )

I've done a fair bit of php / mysql scripting a few years ago and want to customize some solarwinds pages. I'm looking through the files in the Orion directory and don't seem to see examples of queries to the database. I'm just trying to start simple and create a simple "select nodes where status = down from db" type thing to get rolling, but that is even a problem. Can someone explain the basics of how to use existing solarwinds code in order to execute my own query in a customized page?

Find more posts tagged with

database_schema

sql_code

sql

custom_reports

Accepted answers

All comments

NTeam

Hey Maladil,

You may want to browse the thwack content exchange for NPM. There are quite a few resources on there where ppl are manipulationg the solarwinds pages.

I haven't created one from scratch yet but i have manipulated a few of the provided resources to benefit my setup.

Michael

darryld

Many of the pages supplied by Solarwinds separate the front end web control from the back end code.

When creating simple resources I prefer to use a single file which contains both elements. There are a number of examples of the this type of file elsewhere in thwack but below is the one I use

++++

<%@ Control Language="C#" ClassName="EnhAlerts" Inherits="SolarWinds.Orion.Web.UI.BaseResourceControl" %>

<%@ Import Namespace="System.Data.SqlClient"%>

<%@ Import Namespace="SolarWinds.Orion.Common"%>

<%@ Import Namespace="SolarWinds.Orion.Web.DAL"%>

<%@ Import Namespace="System.Data"%>

<%@ Register TagPrefix="orion" Namespace="SolarWinds.Orion.Web.Controls" %>

protected void Page_Load(object sender, EventArgs e)

{

string filter = this.Resource.Properties["Filter"];

DataTable table;

try

{

string sql = @"

SELECT AlertDefinitions.AlertName, Nodes.Caption, AlertStatus.TriggerTimeStamp

FROM AlertDefinitions INNER JOIN

AlertStatus ON AlertDefinitions.AlertDefID = AlertStatus.AlertDefID INNER JOIN

Nodes ON AlertStatus.ActiveObject = Nodes.NodeID

WHERE AlertDefinitions.AlertName = 'NodeDown'

ORDER BY AlertStatus.TriggerTimeStamp DESC

if (!String.IsNullOrEmpty(filter))

sql = string.Format(sql, "AND " + filter.Replace('*', '%').Replace('?', '_'));

else

sql = string.Format(sql, "");

using (SqlCommand cmd = SqlHelper.GetTextCommand(Limitation.LimitSQL(sql)))

{

table = SqlHelper.ExecuteDataTable(cmd);

}

catch (SqlException)

{

this.SQLErrorPanel.Visible = true;

return;

}

this.enhAlertsTable.DataSource = table;

this.enhAlertsTable.DataBind();

}

protected override string DefaultTitle

{

get { return "Down Node (Time Ordered)"; }

}

public override string HelpLinkFragment

{

get { return "OrionPHResourceDownNodes"; }

}

public override string EditURL

{

get

{

string url = String.Format("/Orion/NetPerfMon/Resources/FilterEdit.aspx?ResourceID={0}&ViewID={1}&HideInterfaceFilter=True",

this.Resource.ID, this.Resource.View.ViewID);

if (!String.IsNullOrEmpty(this.Request.QueryString["NetObject"]))

{

url += "&NetObject=" + this.Request.QueryString["NetObject"];

}

return url;

}

public override string SubTitle

{

get

{

string subTitle = base.SubTitle;

if (String.IsNullOrEmpty(subTitle))

{

return "The Following Nodes Are Down (Latest ones listed first)";

}

return subTitle;

}

</script>

<orion:resourceWrapper runat="server" ID="wrapper">

<asp:Panel ID="SQLErrorPanel" runat="server" Visible="false">

<tr>

Custom SQL filter is incorrectly formated.

</td>

</tr>

</table>

</asp:Panel>

<asp:Repeater runat="server" ID="enhAlertsTable">

<tr>

<th class="ReportHeader">AlertName</th>

</tr >

</HeaderTemplate>

<tr >

</tr>

</ItemTemplate>

</table>

</FooterTemplate>

</asp:Repeater>

</Content>

</orion:resourceWrapper>

+++++

The sql statement is in lines 18 to 25

try

{

string sql = @"

SELECT AlertDefinitions.AlertName, Nodes.Caption, AlertStatus.TriggerTimeStamp

FROM AlertDefinitions INNER JOIN

AlertStatus ON AlertDefinitions.AlertDefID = AlertStatus.AlertDefID INNER JOIN

Nodes ON AlertStatus.ActiveObject = Nodes.NodeID

WHERE AlertDefinitions.AlertName = 'NodeDown'

ORDER BY AlertStatus.TriggerTimeStamp DESC

My query returns 3 variables which need to be displayed.

Lines 101 to 115 deal with this, first setting up column headers in the resource then accessing the results

</tr>

Hope this helps

maladil

Thanks everyone. I got my page working as a stand alone. It was a lot harder than I thought it would be. Now I'm trying to figure out how to get it to work with a CustomHTML field. If that is too difficult, then can someone point me to a way to add the Solarwinds Header and Menu bar to the top of my custom page? Here's my code if anyone is curious.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<Script Runat="Server">
Sub Page_Load
dim dbconn AS SqlConnection
dim dbcomm AS New SqlCommand
dim dbread AS SqlDataReader
dim cmdSelectNodes AS String
dbconn=New SqlConnection("Server=165.X.x.x;uid=x;pwd=x;database=NetPerfMon" )
dbconn.Open()
cmdSelectNodes= "Select Nodes.NodeID, Nodes.Caption, Nodes.IP_Address, Nodes.Site_ID, Nodes.Lattitude, Nodes.Longitude, Nodes.Location_Type, Nodes.Alert_Importance, Nodes.MachineType From Nodes Where ( Nodes.Status = 2 ) AND ( Nodes.Lattitude IS NOT Null)"
dbcomm=New SqlCommand(cmdSelectNodes,dbconn)
dbread=dbcomm.ExecuteReader()
Nodes.DataSource=dbread
Nodes.DataBind()
dbread.Close()
dbconn.Close()
End Sub
</Script>

<html>
<head>
<title>SolarWinds / GoogleMaps Node Assignment</title>
<link href="">code.google.com/.../default.css" rel="stylesheet" type="text/css" />
<script type="text/javascript" src="">maps.google.com/.../js
<form runat="server">
   <asp:Repeater id="Nodes" runat="server">
<HeaderTemplate>
<script type="text/javascript">
var map = null;

function initialize() {
var myOptions = {
    zoom: 5,
   mapTypeControl: true,
    mapTypeControlOptions: {style: google.maps.MapTypeControlStyle.DROPDOWN_MENU},
    navigationControl: true,
    center: new google.maps.LatLng(38, -95),
    mapTypeId: google.maps.MapTypeId.ROADMAP
}
map = new google.maps.Map(document.getElementById("map_canvas"), myOptions);
google.maps.event.addListener(map, 'click', function() {
        infowindow.close();
        });

</HeaderTemplate>
    <ItemTemplate>
   var caption = "<%#Container.DataItem("Caption")%>";
   var point = new google.maps.LatLng(<%#Container.DataItem("Lattitude")%>, <%#Container.DataItem("Longitude")%>);
   var node_url = '<b>Node : </b> <a href=""NodeID")%>"+'">'+caption+"<a/>">thwack.solarwinds.com/.../NodeDetails.aspx is down.";
   var contentString = '<div id="content">'+node_url+"<br />IP = <%#Container.DataItem("IP_Address")%><br />Location type = <%#Container.DataItem("Location_Type")%>"+
                        "<br />Device Type = <%#Container.DataItem("MachineType")%></div>";
   var marker = createMarker(point, caption , contentString);

   </ItemTemplate>

<FooterTemplate>

}

var infowindow = new google.maps.InfoWindow(
{
});

function createMarker(latlng, mark_title, html) {
    var contentString_marker = html;
    var marker = new google.maps.Marker({
        position: latlng,
        map: map,
       title: mark_title
         });

    google.maps.event.addListener(marker, 'click', function() {
        infowindow.setContent(contentString_marker);
        infowindow.open(map,marker);
        });
}

</script>
</FooterTemplate>
</asp:Repeater>
</form>

</head>
<body onload="initialize()">
<div id="map_canvas" style="width: 1400px; height: 800px;"></div>
</body>
</html>