Creating SWQL joins using the links in SWQL studio

Hi guys,

I am currently stuck creating joins using the link icons in SWQL studio. See below example:

I know that some how i need to use 'As' and create and alias in that query and reference it. In the above query i have info from orion.nodes in the query but i want to add info from orion.alertobjects table and 'alertactive' link in the same query and add it to the above.

As mentioned i guess it's by using an alias?.

Thanks for your help

Parents
  • I encourage you to read Kevin's response and follow the post he's linked. Just know, that even after having several very clever folks telling me, it took me a good while to finally see the light - so to speak.

    In your example you could do this:

    FROM Orion.Nodes AS nodes

    Then anywhere you wanted to insert something from Orion.AlertActive you would simply type nodes.AlertActive.name_of_field_you_want

    Another example that I was given, and made a One Note entry about so I can always refer back if I forget or have a dull day is:

    Hope this helps.

  • Hi, 

    Thank you both i will take a look at both and read up

    Yes i met Kevin at SWUG London a few months back. Very knowledgeable guy indeed

  • Ah so i was going wrong because i wasn't selecting a NP from the orion.nodes table.

    So i can't just use NP'S from another table in my query, only those above if there are any of use right?

    Thanks again for all your help

  • So i can't just use NP'S from another table in my query, only those above if there are any of use right?

    To be 100% clear you can use NPs directly associated to the table your are looking at e.g. Orion.Nodes, and those indirectly linked through the directly linked NP's.

  • cool. Thanks

    Also do you happen to know how there's a way of filling in blank gifs and just the file reference and the alertobject id's to mean something?

    Status is fine

    Thanks mate. much appreciated

  • Is this in a Modern Dashboard?  Can you share your original SWQL?  That'll help us understand the specifics.

  • Yes we are thanks. 

    If i input the following, the table in the MD has just the .gif filenames and doesn't display them.

    SELECT [nodes].Caption, 
    [nodes].NodeID, 
    [nodes].ObjectSubType, 
    [nodes].IPAddress, 
    [nodes].IPAddressType, 
    [nodes].DNS, 
    [nodes].Vendor, 
    [nodes].VendorIcon, 
    [nodes].Icon, 
    [nodes].Status
    
    FROM Orion.Nodes AS nodes
    where Caption LIKE'%srveucsc%'
    ORDER BY Caption
    

  • And it won't because that's not how it's designed to work with images.  That's why I wanted to get your original query to see if I can figure out a way to get this information to display in the way you'd like.

  • Ah ok. So you can't get gifs displayed in the table in modern dashes then?.

    Thanks

  • Not in the way that it's done with the previous Custom Query widget.

    What do you want this table to actually show?  Even insert a table and put in field titles and what you'd like to see in each field.

  • If you want, you can import this JSON as a new Modern Dashboard and look at how I configured it to show the vendor icons.

    {
      "version": 1,
      "dashboards": [
        {
          "widgets": [
            {
              "unique_key": "50fdc269-55f2-4cfe-8575-5f856ece0247",
              "location": {
                "x": 0,
                "y": 0,
                "cols": 12,
                "rows": 4
              },
              "reference": false
            }
          ],
          "parent": null,
          "feature": null,
          "unique_key": "b0a2cc3b-74f4-4e4f-a302-5a9f02b406c8",
          "name": "Testing",
          "private": null
        }
      ],
      "widgets": [
        {
          "type": "table",
          "configuration": {
            "header": {
              "properties": {
                "title": "Table Widget",
                "subtitle": "",
                "url": "",
                "description": "",
                "collapsible": false,
                "collapsed": true
              }
            },
            "table": {
              "providers": {
                "dataSource": {
                  "providerId": "TableSwqlDatasourceService",
                  "properties": {
                    "swql": "SELECT [Nodes].Caption, \n[Nodes].NodeID, \n[Nodes].ObjectSubType, \n[Nodes].IPAddress, \n[Nodes].IPAddressType, \n[Nodes].DNS, \n[Nodes].VendorInfo.DisplayName AS [VendorName],\n[Nodes].VendorInfo.Icon AS [VendorIcon],\n[Nodes].Status,\n[Nodes].StatusDescription,\n[Nodes].ChildStatus,\n[Nodes].NodeStatusRootCause,\n[Nodes].EntityType,\n[Nodes].DetailsUrl\n\nFROM Orion.Nodes AS [Nodes]\n--where Caption LIKE'%srveucsc%'\nORDER BY Caption",
                    "dataFields": [
                      {
                        "id": "Caption",
                        "label": "Caption",
                        "dataType": "System.String"
                      },
                      {
                        "id": "NodeID",
                        "label": "NodeID",
                        "dataType": "System.Int32"
                      },
                      {
                        "id": "ObjectSubType",
                        "label": "ObjectSubType",
                        "dataType": "System.String"
                      },
                      {
                        "id": "IPAddress",
                        "label": "IPAddress",
                        "dataType": "System.String"
                      },
                      {
                        "id": "IPAddressType",
                        "label": "IPAddressType",
                        "dataType": "System.String"
                      },
                      {
                        "id": "DNS",
                        "label": "DNS",
                        "dataType": "System.String"
                      },
                      {
                        "id": "VendorName",
                        "label": "VendorName",
                        "dataType": "System.String"
                      },
                      {
                        "id": "VendorIcon",
                        "label": "VendorIcon",
                        "dataType": "System.String"
                      },
                      {
                        "id": "Status",
                        "label": "Status",
                        "dataType": "System.Int32"
                      },
                      {
                        "id": "StatusDescription",
                        "label": "StatusDescription",
                        "dataType": "System.String"
                      },
                      {
                        "id": "ChildStatus",
                        "label": "ChildStatus",
                        "dataType": "System.Int32"
                      },
                      {
                        "id": "NodeStatusRootCause",
                        "label": "NodeStatusRootCause",
                        "dataType": "System.String"
                      },
                      {
                        "id": "EntityType",
                        "label": "EntityType",
                        "dataType": "System.String"
                      },
                      {
                        "id": "DetailsUrl",
                        "label": "DetailsUrl",
                        "dataType": "System.String"
                      }
                    ],
                    "type": "hand-edit"
                  }
                },
                "adapter": {
                  "properties": {
                    "dataSource": {
                      "properties": {
                        "swql": "SELECT [Nodes].Caption, \n[Nodes].NodeID, \n[Nodes].ObjectSubType, \n[Nodes].IPAddress, \n[Nodes].IPAddressType, \n[Nodes].DNS, \n[Nodes].VendorInfo.DisplayName AS [VendorName],\n[Nodes].VendorInfo.Icon AS [VendorIcon],\n[Nodes].Status,\n[Nodes].StatusDescription,\n[Nodes].ChildStatus,\n[Nodes].NodeStatusRootCause,\n[Nodes].EntityType,\n[Nodes].DetailsUrl\n\nFROM Orion.Nodes AS [Nodes]\n--where Caption LIKE'%srveucsc%'\nORDER BY Caption",
                        "dataFields": [
                          {
                            "id": "Caption",
                            "label": "Caption",
                            "dataType": "System.String"
                          },
                          {
                            "id": "NodeID",
                            "label": "NodeID",
                            "dataType": "System.Int32"
                          },
                          {
                            "id": "ObjectSubType",
                            "label": "ObjectSubType",
                            "dataType": "System.String"
                          },
                          {
                            "id": "IPAddress",
                            "label": "IPAddress",
                            "dataType": "System.String"
                          },
                          {
                            "id": "IPAddressType",
                            "label": "IPAddressType",
                            "dataType": "System.String"
                          },
                          {
                            "id": "DNS",
                            "label": "DNS",
                            "dataType": "System.String"
                          },
                          {
                            "id": "VendorName",
                            "label": "VendorName",
                            "dataType": "System.String"
                          },
                          {
                            "id": "VendorIcon",
                            "label": "VendorIcon",
                            "dataType": "System.String"
                          },
                          {
                            "id": "Status",
                            "label": "Status",
                            "dataType": "System.Int32"
                          },
                          {
                            "id": "StatusDescription",
                            "label": "StatusDescription",
                            "dataType": "System.String"
                          },
                          {
                            "id": "ChildStatus",
                            "label": "ChildStatus",
                            "dataType": "System.Int32"
                          },
                          {
                            "id": "NodeStatusRootCause",
                            "label": "NodeStatusRootCause",
                            "dataType": "System.String"
                          },
                          {
                            "id": "EntityType",
                            "label": "EntityType",
                            "dataType": "System.String"
                          },
                          {
                            "id": "DetailsUrl",
                            "label": "DetailsUrl",
                            "dataType": "System.String"
                          }
                        ],
                        "type": "hand-edit"
                      }
                    }
                  }
                }
              },
              "properties": {
                "configuration": {
                  "columns": [
                    {
                      "id": "column_d30e97bc-285b-4c2a-b9ef-f28ac74d925c",
                      "formatter": {
                        "componentType": "EntityLinkFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "status": null,
                            "vendor": "VendorIcon",
                            "link": "DetailsUrl",
                            "value": "Caption"
                          },
                          "iconFormat": "vendor",
                          "entityIcon": "hardware"
                        }
                      },
                      "isActive": true,
                      "label": "Caption"
                    },
                    {
                      "id": "column_cd4dcabe-4b16-4793-886d-78c49be3d795",
                      "formatter": {
                        "componentType": "RawFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "ObjectSubType"
                          }
                        }
                      },
                      "isActive": true,
                      "label": "ObjectSubType"
                    },
                    {
                      "id": "column_c7acc605-76b6-413a-9296-fae2d244b20f",
                      "formatter": {
                        "componentType": "RawFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "IPAddress"
                          }
                        }
                      },
                      "isActive": true,
                      "label": "IPAddress"
                    },
                    {
                      "id": "column_7576c03e-c617-4083-bc43-80d97c0d8cc5",
                      "formatter": {
                        "componentType": "RawFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "IPAddressType"
                          }
                        }
                      },
                      "isActive": true,
                      "label": "IPAddressType"
                    },
                    {
                      "id": "column_aff2a11b-cbd6-4847-9f2c-3b08cced7f49",
                      "formatter": {
                        "componentType": "RawFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "DNS"
                          }
                        }
                      },
                      "isActive": true,
                      "label": "DNS"
                    },
                    {
                      "id": "column_3dd75a00-6dd8-4152-a165-0674cf2103ae",
                      "formatter": {
                        "componentType": "StatusFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "Status"
                          }
                        }
                      },
                      "isActive": true,
                      "label": "Status"
                    },
                    {
                      "id": "column_4b9d792c-6cf4-4b2a-8861-a3633a39fb28",
                      "formatter": {
                        "componentType": "StatusFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "ChildStatus"
                          }
                        }
                      },
                      "isActive": true,
                      "label": "ChildStatus"
                    },
                    {
                      "id": "column_c12d0b8a-3730-47a1-9643-d0ed91dba7e7",
                      "formatter": {
                        "componentType": "RawFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "NodeStatusRootCause"
                          }
                        }
                      },
                      "isActive": true,
                      "label": "NodeStatusRootCause"
                    }
                  ],
                  "sorterConfiguration": {
                    "descendantSorting": ""
                  },
                  "hasVirtualScroll": true
                }
              }
            }
          },
          "subtitle": "",
          "description": "",
          "unique_key": "50fdc269-55f2-4cfe-8575-5f856ece0247",
          "name": "Table Widget",
          "private": false
        }
      ],
      "remove": null
    }
    

    The import and export process is outlined on Importing/Exporting Modern Dashboards - Orion SDK - The Orion Platform - THWACK (solarwinds.com).

Reply
  • If you want, you can import this JSON as a new Modern Dashboard and look at how I configured it to show the vendor icons.

    {
      "version": 1,
      "dashboards": [
        {
          "widgets": [
            {
              "unique_key": "50fdc269-55f2-4cfe-8575-5f856ece0247",
              "location": {
                "x": 0,
                "y": 0,
                "cols": 12,
                "rows": 4
              },
              "reference": false
            }
          ],
          "parent": null,
          "feature": null,
          "unique_key": "b0a2cc3b-74f4-4e4f-a302-5a9f02b406c8",
          "name": "Testing",
          "private": null
        }
      ],
      "widgets": [
        {
          "type": "table",
          "configuration": {
            "header": {
              "properties": {
                "title": "Table Widget",
                "subtitle": "",
                "url": "",
                "description": "",
                "collapsible": false,
                "collapsed": true
              }
            },
            "table": {
              "providers": {
                "dataSource": {
                  "providerId": "TableSwqlDatasourceService",
                  "properties": {
                    "swql": "SELECT [Nodes].Caption, \n[Nodes].NodeID, \n[Nodes].ObjectSubType, \n[Nodes].IPAddress, \n[Nodes].IPAddressType, \n[Nodes].DNS, \n[Nodes].VendorInfo.DisplayName AS [VendorName],\n[Nodes].VendorInfo.Icon AS [VendorIcon],\n[Nodes].Status,\n[Nodes].StatusDescription,\n[Nodes].ChildStatus,\n[Nodes].NodeStatusRootCause,\n[Nodes].EntityType,\n[Nodes].DetailsUrl\n\nFROM Orion.Nodes AS [Nodes]\n--where Caption LIKE'%srveucsc%'\nORDER BY Caption",
                    "dataFields": [
                      {
                        "id": "Caption",
                        "label": "Caption",
                        "dataType": "System.String"
                      },
                      {
                        "id": "NodeID",
                        "label": "NodeID",
                        "dataType": "System.Int32"
                      },
                      {
                        "id": "ObjectSubType",
                        "label": "ObjectSubType",
                        "dataType": "System.String"
                      },
                      {
                        "id": "IPAddress",
                        "label": "IPAddress",
                        "dataType": "System.String"
                      },
                      {
                        "id": "IPAddressType",
                        "label": "IPAddressType",
                        "dataType": "System.String"
                      },
                      {
                        "id": "DNS",
                        "label": "DNS",
                        "dataType": "System.String"
                      },
                      {
                        "id": "VendorName",
                        "label": "VendorName",
                        "dataType": "System.String"
                      },
                      {
                        "id": "VendorIcon",
                        "label": "VendorIcon",
                        "dataType": "System.String"
                      },
                      {
                        "id": "Status",
                        "label": "Status",
                        "dataType": "System.Int32"
                      },
                      {
                        "id": "StatusDescription",
                        "label": "StatusDescription",
                        "dataType": "System.String"
                      },
                      {
                        "id": "ChildStatus",
                        "label": "ChildStatus",
                        "dataType": "System.Int32"
                      },
                      {
                        "id": "NodeStatusRootCause",
                        "label": "NodeStatusRootCause",
                        "dataType": "System.String"
                      },
                      {
                        "id": "EntityType",
                        "label": "EntityType",
                        "dataType": "System.String"
                      },
                      {
                        "id": "DetailsUrl",
                        "label": "DetailsUrl",
                        "dataType": "System.String"
                      }
                    ],
                    "type": "hand-edit"
                  }
                },
                "adapter": {
                  "properties": {
                    "dataSource": {
                      "properties": {
                        "swql": "SELECT [Nodes].Caption, \n[Nodes].NodeID, \n[Nodes].ObjectSubType, \n[Nodes].IPAddress, \n[Nodes].IPAddressType, \n[Nodes].DNS, \n[Nodes].VendorInfo.DisplayName AS [VendorName],\n[Nodes].VendorInfo.Icon AS [VendorIcon],\n[Nodes].Status,\n[Nodes].StatusDescription,\n[Nodes].ChildStatus,\n[Nodes].NodeStatusRootCause,\n[Nodes].EntityType,\n[Nodes].DetailsUrl\n\nFROM Orion.Nodes AS [Nodes]\n--where Caption LIKE'%srveucsc%'\nORDER BY Caption",
                        "dataFields": [
                          {
                            "id": "Caption",
                            "label": "Caption",
                            "dataType": "System.String"
                          },
                          {
                            "id": "NodeID",
                            "label": "NodeID",
                            "dataType": "System.Int32"
                          },
                          {
                            "id": "ObjectSubType",
                            "label": "ObjectSubType",
                            "dataType": "System.String"
                          },
                          {
                            "id": "IPAddress",
                            "label": "IPAddress",
                            "dataType": "System.String"
                          },
                          {
                            "id": "IPAddressType",
                            "label": "IPAddressType",
                            "dataType": "System.String"
                          },
                          {
                            "id": "DNS",
                            "label": "DNS",
                            "dataType": "System.String"
                          },
                          {
                            "id": "VendorName",
                            "label": "VendorName",
                            "dataType": "System.String"
                          },
                          {
                            "id": "VendorIcon",
                            "label": "VendorIcon",
                            "dataType": "System.String"
                          },
                          {
                            "id": "Status",
                            "label": "Status",
                            "dataType": "System.Int32"
                          },
                          {
                            "id": "StatusDescription",
                            "label": "StatusDescription",
                            "dataType": "System.String"
                          },
                          {
                            "id": "ChildStatus",
                            "label": "ChildStatus",
                            "dataType": "System.Int32"
                          },
                          {
                            "id": "NodeStatusRootCause",
                            "label": "NodeStatusRootCause",
                            "dataType": "System.String"
                          },
                          {
                            "id": "EntityType",
                            "label": "EntityType",
                            "dataType": "System.String"
                          },
                          {
                            "id": "DetailsUrl",
                            "label": "DetailsUrl",
                            "dataType": "System.String"
                          }
                        ],
                        "type": "hand-edit"
                      }
                    }
                  }
                }
              },
              "properties": {
                "configuration": {
                  "columns": [
                    {
                      "id": "column_d30e97bc-285b-4c2a-b9ef-f28ac74d925c",
                      "formatter": {
                        "componentType": "EntityLinkFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "status": null,
                            "vendor": "VendorIcon",
                            "link": "DetailsUrl",
                            "value": "Caption"
                          },
                          "iconFormat": "vendor",
                          "entityIcon": "hardware"
                        }
                      },
                      "isActive": true,
                      "label": "Caption"
                    },
                    {
                      "id": "column_cd4dcabe-4b16-4793-886d-78c49be3d795",
                      "formatter": {
                        "componentType": "RawFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "ObjectSubType"
                          }
                        }
                      },
                      "isActive": true,
                      "label": "ObjectSubType"
                    },
                    {
                      "id": "column_c7acc605-76b6-413a-9296-fae2d244b20f",
                      "formatter": {
                        "componentType": "RawFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "IPAddress"
                          }
                        }
                      },
                      "isActive": true,
                      "label": "IPAddress"
                    },
                    {
                      "id": "column_7576c03e-c617-4083-bc43-80d97c0d8cc5",
                      "formatter": {
                        "componentType": "RawFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "IPAddressType"
                          }
                        }
                      },
                      "isActive": true,
                      "label": "IPAddressType"
                    },
                    {
                      "id": "column_aff2a11b-cbd6-4847-9f2c-3b08cced7f49",
                      "formatter": {
                        "componentType": "RawFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "DNS"
                          }
                        }
                      },
                      "isActive": true,
                      "label": "DNS"
                    },
                    {
                      "id": "column_3dd75a00-6dd8-4152-a165-0674cf2103ae",
                      "formatter": {
                        "componentType": "StatusFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "Status"
                          }
                        }
                      },
                      "isActive": true,
                      "label": "Status"
                    },
                    {
                      "id": "column_4b9d792c-6cf4-4b2a-8861-a3633a39fb28",
                      "formatter": {
                        "componentType": "StatusFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "ChildStatus"
                          }
                        }
                      },
                      "isActive": true,
                      "label": "ChildStatus"
                    },
                    {
                      "id": "column_c12d0b8a-3730-47a1-9643-d0ed91dba7e7",
                      "formatter": {
                        "componentType": "RawFormatterComponent",
                        "properties": {
                          "dataFieldIds": {
                            "value": "NodeStatusRootCause"
                          }
                        }
                      },
                      "isActive": true,
                      "label": "NodeStatusRootCause"
                    }
                  ],
                  "sorterConfiguration": {
                    "descendantSorting": ""
                  },
                  "hasVirtualScroll": true
                }
              }
            }
          },
          "subtitle": "",
          "description": "",
          "unique_key": "50fdc269-55f2-4cfe-8575-5f856ece0247",
          "name": "Table Widget",
          "private": false
        }
      ],
      "remove": null
    }
    

    The import and export process is outlined on Importing/Exporting Modern Dashboards - Orion SDK - The Orion Platform - THWACK (solarwinds.com).

Children
No Data