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

Formatting SWQL output.

I'm working on a query to display in my IPAM page the DHCP scopes that are empty or near empty. Close to getting it done, but I'd like my output to be a bit friendlier if possible, wondering if there is any way to do it and I have a couple questions. Here is my full query as I have it right now, my questions will be based on this a bit.

SELECT FriendlyName AS Scope, (ROUND(PercentUsed, 2) * 1.0) AS [Percent IP Space Used], AvailableCount AS [IPS Available], UsedCount AS [IPS Used], ReservedCount AS [IPS Reserved]

FROM IPAM.GroupNode

WHERE (PercentUsed < 5) AND (TotalCount > 253)

ORDER BY PercentUsed, UsedCount, AvailableCount, FriendlyName

The first question being, where I have [Percent IP Space Used] as an alias for the column, I would actually love to have it more like the column names in the standard "Top XX Subnets by % IP Address Used" that is on the main IPAM page, or specifically I'd like the column named "% IP Address Used". I've tried everything I can think of from escaping the '%' with a backslash to trying to represent it as a hex digit, quoting it, etc. etc... Nothing has worked. Any ideas how to get it to look nicer?

The second question also deals with that same column, but the values produced in the table. Ideally I'd like them to be percentages to two decimal points, also like in the standard resource noted above. If I used the column itself (ie: PercentUsed), I get 4 decimal places after the decimal point. Using ROUND() on its own seems to have no effect on the number of places after the decimal point, only on the significant digits past the decimal point. ie: If the column value is 0.12345 and I used ROUNT(PercentUsed, 2) it changes it to 0.12000, where I'd really like it to strip the 0's after the '12'. Thinking this is a bug, but not sure how to fix it.

Now, if I multiply the value given to me by the ROUND() function by something like 1.0 (not an integer like '1'), I get what I want somewhat. From the example above I would end up getting '0.12'. It's not quite right in the case of an initial value of something like '0.90000' would show up as '0.9' at this point, rather than 0.90. Or, if its just a straight '0', I get that instead of 0.00. (ie: ideally I'd like 2 places no matter what, or zero padded)... But it's close, so now you're saying that I figured it out already and what the heck am I asking?

Ok, the problem then arises in that I want to put a '%' after it, once again so it better matches the built in resource shown above. So in my first example I would like to end up seeing '0.12%' in the column. No problem you say, just convert it to a string with ToString() and add a '%' at the end. The minute I wrap a ToString() around my results, it goes back to the 5 places after the decimal point. ie: if I leave it at "ROUND(PercentUsed, 2) * 1.0" I get 0.12. If I change that to "ToString(ROUND(PercentUsed, 2) * 1.0) + '%'" I get "0.12000%"!!

I'm just finding it very frustrating trying to get things formatted as I'd like them to be. It seems as if things aren't working quite as they should be, I'd call it a bug.

Anyone have any insight on this on how to get it to do what I want? Even getting closer would be nice...

Find more posts tagged with

Accepted answers

All comments

tdanner

Number formatting is not SWQL's strong suit. I don't see any way to do what you want directly. You might be able to hack something together using the floor function to separate the whole and fractional parts of the number and recombine them as strings.

It's better to handle formatting at the presentation layer. You didn't say what Orion resource you are using, but my guess is that this is the "Custom Query" resource. The "Custom Table" resource gives you much more control over formatting. Like this:

2015-04-09 15_41_03-Edit Resource_ Custom Table.png

bluefunelemental

tdanner wrote:
Number formatting is not SWQL's strong suit.

YUP.

More functions would help and as the OP mentioned being able to have more control over CAST/ Convert in addition to ToString() functions.

cnorborg

Hmm... Never really played with that before. Definitely close to what I want in some ways, but in some ways not so nice of results.

Definitely don't like that you get the "for Datasource 1" and the "Ordered by" before your table headings. No way to get rid of those AFAIK. I tried sorting in my query first, but it seems to have re-ordered everything on me when it displays. At least I'm seeing records I normally wouldn't...

It does solve the ability to truly customize my headers, but the format it puts it out in uses more space, the table is I'd guess about 50% taller than it was just using a "Custom Query" resource...

The biggest thing I miss is the pagination, with the custom query I could display the "first 25" and at the bottom I'd have Page 1 of X and the ability to page through all the pages or "Display All". With this one I get the ability to display the "Top X" or "Top X %", but it displays them all right on the page I have it on, gets quite large!!

Wish we could have the same features in both of them!!

In order to get that "Custom Format" for the PercentUsed field, I did have to multiply it by something, which I'm pretty sure forced it to be changed from a "System.Decimal" format to an "Int" format or something like that. Otherwise it only gave me the "Generic Icon" setting in the "display settings"..