• State Verified Answer
  • Locked Locked
  • Replies 8 replies
  • Subscribers 24 subscribers
  • Views 1225 views
  • Users 0 members are here
Options
Related
This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SWQL INNER JOIN

joldosandrei

Greeting everyone,
I am a beginner at SWQL and I'm not very good at SQL neither. I have problems with he bellow sequence.
I am trying to create a widget with the Volume size available of my nodes and I want to add the NodeName which is not in Orion.Volumes collection but in Orion.Nodes collection.
Unfortunately I can't really wrap my head around this. Is there anybody who want to help me, please?

Here is the code as text:

SELECT Orion.Nodes.NodeName
, round((VolumeSpaceAvailable / 1073741824.0),2) as GB_Available
, VolumePercentAvailable
, round((Size/ 1073741824.0),2) as Size_GB
, DetailsUrl AS [_LinkFor_FullName]
FROM Orion.Volumes AS Volume
INNER JOIN Orion.Nodes.NodeID as Nodes
on Orion.Nodes.NodeID = Volume.NodeID
ORDER BY VolumePercentAvailable

I've tried using only Nodes.NodeName instead of also adding the Orion at the beginning but it's the same result.

Also, at the last line (OrderBy) how can I add the "%" symbol in the results near VolumePercentAvailable?

Thank you in advance! 

  • 0 

    SELECT v.Node.Caption
, round((v.VolumeSpaceAvailable / 1073741824.0),2) as GB_Available
, concat (v.VolumePercentAvailable , ' %') as Percent_Available
, round((v.Size/ 1073741824.0),2) as Size_GB
, v.DetailsUrl AS [_LinkFor_FullName]
FROM Orion.Volumes v
ORDER BY VolumePercentAvailable


    You shouldn’t need the join, as if you look further down the columns in the left you will see items that are not columns and these are in fact links to other tables.

    you could do it the way you have but more changes to make Smiley

  • +1

    SELECT v.Node.Caption,
    round((v.VolumeSpaceAvailable / 1073741824.0),2) as GB_Available,
    concat (v.VolumePercentAvailable , ' %') as Percent_Available,
    round((v.Size/ 1073741824.0),2) as Size_GB,
    v.DetailsUrl AS [_LinkFor_FullName]
    FROM Orion.Volumes v
    ORDER BY VolumePercentAvailable
  • 0 in reply to dodo123

    Hi dodo123,

    Incredible, it works and I also learn something new. Thank you very much!

    The code is returning the info regarding size for all the partition. Do you think there is any possibility to select only partition C: ?

    Thank you!

  • 0 in reply to joldosandrei

    SELECT v.Node.Caption,
    round((v.VolumeSpaceAvailable / 1073741824.0),2) as GB_Available,
    concat (v.VolumePercentAvailable , ' %') as Percent_Available,
    round((v.Size/ 1073741824.0),2) as Size_GB,
    v.DetailsUrl AS [_LinkFor_FullName]
    FROM Orion.Volumes v
    where v.Caption like 'c%'
    ORDER BY VolumePercentAvailable
    the % in 'c%' is anything after c but starts with the letter c
  • 0 in reply to dodo123

    dodo123,
    You are the best. I really appreciate your help.

    I wish you a nice week!

  • 0 in reply to dodo123


    After some testing, I removed ORDER BY sequence from SWQL but I couldn't order the nodes correctly. Please check my print screen bellow. I ordered them from the web portal but still, that last Node base on Percent available should not be down there.

    Do you have any suggestions? I would also like to have only 1 digit after . in percent ( like 50.1%), but I couldn't find any post about it in THWACK.

    Thank you again,
    Andrei

  • 0 in reply to joldosandrei

    its because we have concat the column to include the percent field, this changes it tostring then joins what you want up. This is causing the sort to do it by first character.

    adjusted the swql

    SELECT v.Node.Caption,
    round((v.VolumeSpaceAvailable / 1073741824.0),2) as GB_Available,
    v.VolumePercentAvailable,
    concat ((round(v.VolumePercentAvailable,2) , ' %')) as Percent_Available,
    round((v.Size/ 1073741824.0),2) as Size_GB,
    v.DetailsUrl AS [_LinkFor_FullName]
    FROM Orion.Volumes v
    where v.Caption like 'c%' ORDER BY VolumePercentAvailable

    you will see i have brought the original column back in v.VolumePercentAvailable then sort by that on the modern dashboard and hide the column

    aalso add the round command to the ones missing and its the ,2 or ,1 that is saying how many decimal places

  • 0 in reply to dodo123

    Hi dodo123,

    Indeed, the % was messing with my "ORDER BY". I've removed it. Thank you very much for taking your time and explaining this to me, it builds confidence into diving in the SW world.

    Best regards,
    Andrei

SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.

SolarWinds Customer Success Center Certification Media Vault Link Accounts
About THWACK Blogs For Government Edit Settings Free Tools & Trials
Legal Documents Terms of Use Privacy California Privacy Rights Security Information
©2024 SolarWinds Worldwide, LLC. All Rights Reserved.