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.

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

  • You can only "implicit" JOIN direct to any listed navigation property [NPs] that is listed under the table you are starting from. You can also connect to any other NP that the directly listed NP's have access to.

    An example... 

    Orion.Node has AccessPoints (or Orion.Packages.Wireless.AccessPoints) as its first NP. If you were to separately navigate to that table, you could also connect to 2 other NPs that Orion.Nodes doesn't have direct access to.

    The easiest way to understand is to open SWQL Studio and expand the field in question. So in your example of Orion.Nodes I'd expand it and look at the bracketed names to find the NP I want. In your case, the most obvious one you want is: ActiveAlerts and not AlertActive:

    However that doesn't include the field AlertObject, so your query still wouldn't work. 

    That said, if you link to AlertObjects.AlertObjectID then your query works - if that is what you need then the query would be:

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

  • 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.

Reply Children
  • 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).