nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Changing Dashboard Table Metric from Bytes to GBs/TBs Please

bootneck1111

Hi Guys

I am creating a Dashboard for a team showing File Share and Volume Capacity metrics.
I am puling the SWQL query from Database Manager on the Orion Server, for example;

SELECT TOP 1000 * FROM [dbo].[SRM_Volumes]

and or

SELECT TOP 1000 * FROM [dbo].[SRM_FileShares]

The information in the table is what I'm looking for but the metrics for 'Quota', 'Free', and 'Used' columns is displayed in 'Bytes'.
When I paste this SWQL query into the SWQL dashboard editor I would like to have those Capacity/Space metrics presented in GBs or TBs.

Can you show me what will need to be added to the above SWQL to present GBs/TBs instead of Bytes please

Find more posts tagged with

SWQL

Accepted answers

KMSigma.SWI

Just swap out the field names from my example above.

SELECT TOP 10 [Volumes].CapacityTotal
     , [Volumes].CapacityFileSystem
     , [Volumes].CapacityFree
     , [Volumes].OperStatus
     , [Volumes].OperStatusDescription
     , [Volumes].CapacityFileSystem AS [CapacityFileSystem_Bytes]
     , [Volumes].CapacityFileSystem / 1024.0 AS [CapacityFileSystem_KB]
     , [Volumes].CapacityFileSystem / 1024.0 / 1024.0 AS [CapacityFileSystem_MB]
     -- .. etc.
     -- You'll probably also want to add a ROUND() function here to clip it to X decimal places
     , ROUND([Volumes].CapacityFileSystem / 1024.0 / 1024.0 / 1024.0, 2) AS [CapacityFileSystem_GB]
FROM Orion.SRM.Volumes AS [Volumes]

[embed:6b06e1a7-0ec1-4e43-9421-8caaf95469b6:b969dc65-4907-444f-870f-9199312a1b7f:message=I%20added%20aliases%2C%20because%20that%20helps%20me%20as%20these%20types%20of%20queries%20grow%20larger%20and%C2%A0more%20complex.&type=information]

Please excuse any typos - I don't have a system where I can currently test SRM queries.

All comments

KMSigma.SWI

Within the API, almost everything is stored as bytes. So if you want to go to KB, you divide by 1024. To Mega, divide by another 1024. Etc.

SELECT [FreeSpace] AS [FreeSpace_Bytes]
      ,[FreeSpace] / 1024.0 AS [FreeSpace_KB]
      ,[FreeSpace] / 1024.0  / 1024.0 AS [FreeSpace_MB]
      -- .. etc.
      -- You'll probably also want to add a ROUND() function here to clip it to X decimal places
      ,ROUND([FreeSpace] / 1024.0 / 1024.0 / 1024.0, 2) AS [FreeSpace_GB]
FROM [Volumes]

But WHY am I dividing by 1024.0 and not 1024? Because Bytes will always be integers, but higher notations can be decimals. According to the way SWQL works, if I divide an integer by and integer, it'll return an integer. But if I divide an integer by a decimal, it'll return a decimal. You'll probably also need to add some rounding so the decimal numbers aren't overly long.

bootneck1111

Hi, and thank you for your response.

Where does the SELECT TOP 1000 * FROM [dbo].[SRM_Volumes]

line fit into the below please, is the below query correct to convert Bytes to GBs on the SRM_Volumes table?

(GBs)

SELECT [FreeSpace] AS [FreeSpace_Bytes]

,[FreeSpace] / 1024.0 / 1024.0 / 1024.0 AS [FreeSpace_GB]

,ROUND([FreeSpace] / 1024.0 / 1024.0 / 1024.0, 2) AS [FreeSpace_GB]

==========================================================

SELECT [FreeSpace] AS [FreeSpace_Bytes]
,[FreeSpace] / 1024.0 AS [FreeSpace_KB]
,[FreeSpace] / 1024.0 / 1024.0 AS [FreeSpace_MB]
-- .. etc.
-- You'll probably also want to add a ROUND() function here to clip it to X decimal places
,ROUND([FreeSpace] / 1024.0 / 1024.0 / 1024.0, 2) AS [FreeSpace_GB]
FROM [Volumes]

stuartd

The FROM is already there

[quote userid="60209" url="~/product-forums/the-orion-platform/f/forum/100589/changing-dashboard-table-metric-from-bytes-to-gbs-tbs-please/315632"]FROM [Volumes][/quote]

The SELECT bit is up to you, but all @KMSigma.SWI has done is replace your * (=all records) with looking at just the FreeSpace record instead.

Also note you are asking about SQL whereas Kevin is talking about SWQL. There are subtle differences and for example the SQL concept of * does not work. Instead you have to list each record you want.

Easiest way, I find, to do that is to download and install SWQL Studio. You can then double click a table (or right-click) and query it, which produces output like:

SELECT TOP 1000 ConfigId, Name, Hash, Complexity, Interfaces
FROM NCM.AccessList

If you want ALL results then just delete the TOP 1000 bit.

Coming full circle - if you want other records then just add them in so it would look something like:

SELECT
record1
,record2
, record3,
,etc

N.B. no comma before the first record or at end of the last.

Apologies if this has been a granny suck eggs reply, but hope it helps.

bootneck1111

Apologies, I meant SWQL via the SDK. I'm new to SWQL so still picking things up.

I have the Storage Team asking for a dashboard to display some Volumes Capacity metrics in a Table Widget so I have created the Table Widget and run the SELECT TOP 1000 * FROM [dbo].[SRM_Volumes] which is fine as I will/can filter out the Rows using the WHERE query to pinpoint the target volume names. I just needed to convert the Bytes to GBs in the query.

So if I used the suggested query below to convert Bytes to GBs I'm struggling with the first line: SELECT TOP 1000 * FROM [dbo].[SRM_Volumes] , where does this line fit into the query below.

SELECT [FreeSpace] AS [FreeSpace_Bytes]

,[FreeSpace] / 1024.0 / 1024.0 / 1024.0 AS [FreeSpace_GB]

,ROUND([FreeSpace] / 1024.0 / 1024.0 / 1024.0, 2) AS [FreeSpace_GB]

bootneck1111

Hi Guys

I'm logged into the SolarWinds SWQL Studio SDK.

I run the below query which presents a table with all of the headings requested in the query, and the Capacity Metrics are all in Bytes.

SELECT TOP 10 CapacityTotal, CapacityFileSystem, CapacityFree, OperStatus, OperStatusDescription
FROM Orion.SRM.Volumes

Capacity Total	CapacityFileSystem	CapacityFree	OperStatus	OperStatusDescription
355612258304	75564441600	280047816704	1	online
355612258304	64925503488	290686754816	1	online

How do I change the below query that changes the Capacity Bytes to GBs please. I can then paste the amended SWQL query into the Dashboard Editor in 'Manage Dashboards'.

KMSigma.SWI

Just swap out the field names from my example above.

SELECT TOP 10 [Volumes].CapacityTotal
     , [Volumes].CapacityFileSystem
     , [Volumes].CapacityFree
     , [Volumes].OperStatus
     , [Volumes].OperStatusDescription
     , [Volumes].CapacityFileSystem AS [CapacityFileSystem_Bytes]
     , [Volumes].CapacityFileSystem / 1024.0 AS [CapacityFileSystem_KB]
     , [Volumes].CapacityFileSystem / 1024.0 / 1024.0 AS [CapacityFileSystem_MB]
     -- .. etc.
     -- You'll probably also want to add a ROUND() function here to clip it to X decimal places
     , ROUND([Volumes].CapacityFileSystem / 1024.0 / 1024.0 / 1024.0, 2) AS [CapacityFileSystem_GB]
FROM Orion.SRM.Volumes AS [Volumes]

Please excuse any typos - I don't have a system where I can currently test SRM queries.

bootneck1111

KMSigma, this is perfect, I've validated your query in SWQL which is fine so I'll make adjustments based on the output I need.

Much Appreciated for your help and support.