Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Possible to embed SWQL inside of custom HTML

Is it possible to embed a SWQL query against the orion db inside of a custom html page. I want to be able to pull the data out of the DB directly but have the customization options for displaying the data that i can play with in the html blocks. Does anyone know if this is possible?

Find more posts tagged with

Accepted answers

All comments

mesverrum

I have been playing with a custom html resource to do some javascript charts and this is the relevant part I use to pull data in from the information service, you can basically point any swql at it and it will give you a JSON response.

var params = JSON.stringify({query:'select n.caption, n.CPULoadHistory.AvgLoad from orion.nodes n where n.CPULoadHistory.DateTime > AddMinute(-60, GETUTCDATE()) order by n.caption, n.CPULoadHistory.DateTime desc'});

jQuery.ajax({

type: 'POST', url: '/Orion/Services/Information.asmx/Query', data: params, contentType: "application/json; charset=utf-8", dataType: "json", success: function (response) {

console.log(response);

Another thread with a full example of using the info service here:

Custom Sparkline Charts

tszilagyi

thanks for the info I am looking through this and have some experience with basic HTML and building SQL queries but have never worked with json, jquery or any thing else do you by chance have a basic example that pulls data from a node that's already in the database, looks like your query is already there and fully displays the html.

I'm kinda a self taught coder and if i have a full example that will display data i can usually start from that and then work from there to learn more and accomplish what i need.

I looked at the link for the sparkline charts you provided me but was trying to figure out how to get it to work for either your query or the query that i am using.

tszilagyi

so a little more info

I have a SQL query that returns several text responses i am trying to find a way to insert return these and display them within the html page.

kinda an example would be that it would return the name of a monitored component, a status text, detailed status description and then a date time stamp.

I am trying to figure out a way to get these display on the page in a formatted cell that can eventually be expanded and collapsed etc.

just to have a working query that anyone could use lets assume this was the data i wanted to display on the page

SELECT top 5

AncestorDisplayNames,

AvgResponseTime,

Caption,

Description,

DisplayName,

IPAddress

FROM Orion.Nodes

in my idea i could put the results of this into some type of variable and then push these out to dispaly on the web page.

i_like_eggs

HI mesverrum,

is it possible to output of the query below not with a chart or anything but just show the Select and the results, in a Custom HTML resource? i want to see if this would work rather then SWQL resource..

thanks in advance

tszilagyi

So i have been playing with this and was able to output the results to a table with some inventive html formatting. let me clean up my code a bit so its not referencing anything internal and ill post it. After i get a page setup the way i like it entirely i plan to make a clean post as a how to with everything i have done.

A few things i am trying to add to my code if anyone has any examples. In the stringify portion is it possible to add a variable. I have a where clause that i select specific sets of servers based on custom properties. I would like to add the ability to set that via a variable so its not hard coded in each query.

Second in my first run at this i have about 10 different queries that are pulling back data and each one has its own worker setup. I am not sure if its possible to combine these into fewer worker processes to save on resources. I built this entirely by stealing code online so i can't honestly say that what i am doing is the most efficient method to build this and am looking for ways to improve.

tszilagyi

So here is a code i used to get servers that have a alert based on memory utilization and to display it in a table, with the css formatting i have in it. Its not the cleanest way and once thats organized i will post a full code dump.

Basically what i am doing is running my query to get the servers with high memory utilization, were using any over 97% used since we are virtual and rightsize everything so they typically will run hot by most standards. Then in the worker code i am setting a function at first lines begin the html code for a table. then in the loop it keeps adding on lines to the table based on the results. finally after the loops over i tag on the rest of the data i need to close out the code. This is then set to a variable that im able to reference as a span in the html code. finally i have some css code that i added on to clean up the display. Not sure if this can just be dropped in with the other examples but you should be able to look through and get an idea.

As i said before in the query code i am looking for a way to variablize the server group sections so that i can change it out easier and possibly set it to update based on a drop down on the page.

*** QUERY CODE ***

var params_Memory = JSON.stringify({query:"SELECT N.DisplayName AS [Sys Name], N.DetailsUrl AS [_LinkFor_Sys Name], '/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Sys Name], N.PercentMemoryAvailable AS [Mem Avail %], Round((N.TotalMemory-N.MemoryUsed)/ 1073741824,2) AS [Avail Mem GB], Round(N.TotalMemory / 1073741824,2) AS [Total Mem GB] FROM Orion.Nodes as N INNER JOIN Orion.NodesCustomProperties as NCP ON N.NodeID = NCP.NodeID WHERE N.MachineType like '%Windows%' AND (NCP.Installed_Applications = '%SERVER GROUP 1%' OR NCP.Installed_Applications = '%SERVER GROUP 2%' OR NCP.Installed_Applications = '%SERVER GROUP 3%') AND N.Status !=2 AND ( (N.PercentMemoryUsed >= 97 AND NCP.m_ovrd_prcnt IS NULL) OR (NCP.m_ovrd_prcnt IS NOT NULL AND N.PercentMemoryUsed >= NCP.m_ovrd_prcnt) ) "});

*** END QUERY CODE ***

*** WORKER CODE ***

(function worker_Memory()

{

//Create the AJAX POST request

$.ajax({

type: 'POST',

url: '/Orion/Services/Information.asmx/Query',

data: params_Memory,

contentType: "application/json; charset=utf-8",

dataType: "json",

success: function (response) {

//On a successfull request, parse the data.

//Uncommnet the statement below if you want to see the response

//document.write(JSON.stringify(response));

//Create some arrays to hold the data. -- The response data appears to be oddly formatted compared to other JSON examples, but I'm no expert.

var Mem_HTML = '<table id="Sys_Table"><thead><tr><th>Server Name</th><th>Mem avail</th><th>Avail Mem</th><th>Total Mem</th></tr></thead><tbody>';

for(var i in response.d.Rows)

{

Mem_HTML += '<tr><td> <img border="0" src="https://YourOrionServerHere' + (response.d.Rows[i][2]) + '">' + '<a href="https://YourOrionServerHere" + (response.d.Rows[i][1]) + '">' + (response.d.Rows[i][0]) + '</a></td>' + '<td>' + (response.d.Rows[i][3]) + ' %' + '</td>' + '<td>' + (response.d.Rows[i][4]) + ' (MB)' + '</td>' + '<td>' + (response.d.Rows[i][4]) + ' (GB)' + '</td>' + '</tr>';

};

Mem_HTML += '</tbody></table>';

$('#Svr_Mem').html(Mem_HTML);

// document.write(Mem_HTML);

//Uncomment this if you want to log data to the browser development tools console.

//console.log(a.constructor === Array);

complete: function() {

// Schedule the next request when the current one's complete. Not positive this works yet.

setTimeout(worker_Memory, 60000);

error: function(err) {

// Tell me if something is broke

document.write("Memory Worker broke");

}

});

})();

*** END WORKER CODE ***

*** CSS CODE ***

table#Sys_Table

{

background: #ADD8E6;

border-collapse: collapse;

table-layout: fixed;

margin: auto;

max-width: 600px;

}

table#Sys_Table th

{

border:solid #ADD8E6 1px;

border-radius:10px;

-moz-border-radius:10px;

background: white;

}

table#Sys_Table td

{

border:solid #ADD8E6 1px;

border-radius:10px;

-moz-border-radius:10px;

background: white;

text-align: center;

}

table#Sys_Table td:first-child

{

text-align: left;

}

table#Sys_Table thead

{

display: table;

width: 100%;

text-align: center;

table-layout: fixed;

}

table#Sys_Table thead tr

{

background: #ADD8E6;

}

table#Sys_Table tbody

{

height: 150px;

display: block;

overflow: auto;

overflow-x: hidden;

width: 100%;

}

table#Sys_Table tbody tr

{

display: table;

width: 100%;

table-layout: fixed;

}

table#Sys_Table tr:nth-child(odd) td

{

background: silver;

}

div#Mem_List

{

border-radius: 25px;

background:#ADD8E6;

padding: 10px;

width: 600px;

height: 200px;

position: absolute;

left: 550px;

top: 305px;

}

div#Svr_Mem

{

border-radius: 25px;

background:#ADD8E6;

padding: 10px;

width: 580px;

height: 170px;

}

*** END CSS CODE ***

*** HTML CODE TO DISPLAY ***

<div id ="Mem_List">

Memory Alerts

<div id="alignRight" class="tooltip"> Memory Overrides

<span id="Svr_Mem_Over"></span>

</div>

<div id ="Svr_Mem"> </div>

</div>

*** END HTML CODE ***