3 Replies Latest reply on Jul 14, 2016 5:08 PM by tdanner

    SWQL Studio Error (Unknown dataprovider type 'ntext')

    xtraspecialj

      EDIT: My question in bold underline below still stands, even though I know some of the answer now (see my Edits further below), but if you look at my final edit (Edit 3:) you can see my workaround that will hopefully help anyone else that wants to make an events report or needs a SWQL query for a script they may be working on for Events.

       

      When joining the Orion.Events table (or entity, or whatever it's called in SWQL.) to the Orion.NetObjectTypes table ON Events.NetObjectType = NetObjectTypes.Prefix I get the error Unknown dataprovider type 'ntext'.  Both objects are of type "System.String" in their table.  When I query the Orion.NetObjectTypes table on its own the Prefix field comes back just fine, but joining on it seems to cause this problem.  The only thing I can think of is that the NetObjectTypes table isn't data that is in the SQL database and (I assume) must be generated by the application and fed into SWIS as an entiy.  Maybe for some reason this causes a problem. I don't know.

       

      Anyone (tdanner especially) know why I can't join Event NetObjectTypes to NetObjectTypes Prefix?

       

      Here is the actual code:

      SELECT TOP 1000 
      e.EventID,
      et.Name AS EventTypeName,
      e.Message AS EventMessage,
      e.NetObjectType,
      nt.Name AS ObjectType,
      e.NetObjectValue,
      e.NetworkNode,
      e.InstanceType,
      e.EventTime
      FROM Orion.Events e
      JOIN Orion.EventTypes et ON e.EventType = et.EventType
      JOIN Orion.NetObjectTypes nt ON e.NetObjectType = nt.Prefix
      

       

      I've tried putting the NetObjectTypes table in a subselect in the FROM statement and joining to the results of that but that doesn't seem to trick the query plan and I get the same error.  Besides doing a billion CASE statements for the Orion.Events "NetObjectType" field, what can I do here?

       

      EDIT 1: Also noticed that the NetObjectTypes table has some blank Prefixes that still have a type associated with them.  What's this all about:

       

       

      EDIT 2:  Also just noticed that there are many duplicate prefixes with different Name matches.  I'm sure that will cause a problem, but I wouldn't think it would be an "Unknown dataprovider type" error.  Why are there either duplicate prefix/name combos, or duplicate prefixes mapping to different names?  How does the program use these items and know how to map duplicate prefixes to the different Name fields?

       

      Orion.NetObjectTypes

      PREFIXNAME
      AAApplication
      AAAPM: Application
      FCPFibre Channel Port
      FCPFibre Channel Port
      FCRFibre Channel Revision
      FCRFibre Channel Revision
      FCSFibre Channel Sensor
      FCSFibre Channel Sensor
      GMGroup Member
      GMGroup Member
      GMGroup Member
      IPAMNIPAM Nodes
      IPAMNIPAM IPAddress Conflict
      NVLANVLAN
      NVLANVLAN
      UE-DNSRogue DNSName
      UE-DNSUDT: Hostname
      UE-IPRogue EmptyDNSName
      UE-IPRogue IPAddress
      UE-MACNew MAC Vendor
      UE-MACMoved MAC
      UE-MACNew MACAddress
      UE-MACRogue MACAddress
      UNDPTCustom Node Table Poller
      UNDPTCustom Node Table Poller

       

       

      EDIT 3: My question above still stands, but I went ahead and used Excel to create all of the CASE statements necessary to map the object type codes (Orion.Events.NetObjectType) to the object type names (based on the Orion.NetObjectTypes.Name field, but modified a bit to fix the duplicates and NULL values):

       

      SELECT TOP 10000 
        e.EventID,
        et.Name AS EventTypeName,
        e.Message AS EventMessage,
        CASE
        WHEN e.NetObjectType = 'AA' THEN 'Application'
        WHEN e.NetObjectType = 'ABIA' THEN 'AppInsight for IIS: Application'
        WHEN e.NetObjectType = 'ABIP' THEN 'AppInsight for IIS: Application Pool'
        WHEN e.NetObjectType = 'ABIR' THEN 'AppInsight for IIS: Request'
        WHEN e.NetObjectType = 'ABIRD' THEN 'AppInsight for IIS: Request Details'
        WHEN e.NetObjectType = 'ABIS' THEN 'AppInsight for IIS: Site'
        WHEN e.NetObjectType = 'ABISB' THEN 'AppInsight for IIS: Site Binding'
        WHEN e.NetObjectType = 'ABSA' THEN 'AppInsight for SQL: Application'
        WHEN e.NetObjectType = 'ABSD' THEN 'AppInsight for SQL: Database'
        WHEN e.NetObjectType = 'ABSF' THEN 'AppInsight for SQL: Database File'
        WHEN e.NetObjectType = 'ABSJ' THEN 'AppInsight for SQL: Job Info'
        WHEN e.NetObjectType = 'ABSQ' THEN 'AppInsight for SQL: Expensive Queries Info'
        WHEN e.NetObjectType = 'ABTT' THEN 'APM: Windows Scheduled Tasks'
        WHEN e.NetObjectType = 'ABXA' THEN 'AppInsight for Exchange: Application'
        WHEN e.NetObjectType = 'ABXD' THEN 'AppInsight for Exchange: Database'
        WHEN e.NetObjectType = 'ABXDC' THEN 'AppInsight for Exchange: Database Copy'
        WHEN e.NetObjectType = 'ABXF' THEN 'AppInsight for Exchange: Database File'
        WHEN e.NetObjectType = 'ABXMB' THEN 'AppInsight for Exchange: Mailboxes'
        WHEN e.NetObjectType = 'ABXR' THEN 'AppInsight for Exchange: Replication Status'
        WHEN e.NetObjectType = 'AM' THEN 'APM: Component'
        WHEN e.NetObjectType = 'C' THEN 'Group'
        WHEN e.NetObjectType = 'CCM' THEN 'NTA: CBQoS Class Map'
        WHEN e.NetObjectType = 'DBI' THEN 'Database Instance'
        WHEN e.NetObjectType = 'EWE' THEN 'EnergyWise Entity'
        WHEN e.NetObjectType = 'F5' THEN 'F5 Devices'
        WHEN e.NetObjectType = 'FCP' THEN 'Fibre Channel Port'
        WHEN e.NetObjectType = 'FCR' THEN 'Fibre Channel Revision'
        WHEN e.NetObjectType = 'FCS' THEN 'Fibre Channel Sensor'
        WHEN e.NetObjectType = 'FCU' THEN 'Fibre Channel Unit'
        WHEN e.NetObjectType = 'FN' THEN 'F5 Nodes'
        WHEN e.NetObjectType = 'FP' THEN 'F5 Pools'
        WHEN e.NetObjectType = 'FVS' THEN 'F5 Virtual Servers'
        WHEN e.NetObjectType = 'GM' THEN 'Group Member'
        WHEN e.NetObjectType = 'HWH' THEN 'Hardware'
        WHEN e.NetObjectType = 'HWHS' THEN 'Hardware Sensor'
        WHEN e.NetObjectType = 'HWHT' THEN 'Hardware Type'
        WHEN e.NetObjectType = 'I' THEN 'Interface'
        WHEN e.NetObjectType = 'IPAM-DSO' THEN 'IPAM DHCPScopes Overlapping'
        WHEN e.NetObjectType = 'IPAMG' THEN 'IPAM Networks'
        WHEN e.NetObjectType = 'IPAMN' THEN 'IPAM Node or IPAddress Conflict'
        WHEN e.NetObjectType = 'ISOP' THEN 'IP SLA QoS'
        WHEN e.NetObjectType = 'L' THEN 'Location'
        WHEN e.NetObjectType = 'MCG' THEN 'Multicast Routing Group'
        WHEN e.NetObjectType = 'MCGN' THEN 'Multicast Routing'
        WHEN e.NetObjectType = 'N' THEN 'Node'
        WHEN e.NetObjectType = 'NBR' THEN 'Routing Neighbors'
        WHEN e.NetObjectType = 'NCH' THEN 'UCS Chassis'
        WHEN e.NetObjectType = 'NVLAN' THEN 'VLAN'
        WHEN e.NetObjectType = 'NVS' THEN 'VSAN'
        WHEN e.NetObjectType = 'NWA' THEN 'Node Warranty'
        WHEN e.NetObjectType = 'P' THEN 'VoIP Infrastructure'
        WHEN e.NetObjectType = 'T' THEN 'Transaction'
        WHEN e.NetObjectType = 'TS' THEN 'Step'
        WHEN e.NetObjectType = 'TSR' THEN 'Step Request'
        WHEN e.NetObjectType = 'UCSB' THEN 'UCS Blade'
        WHEN e.NetObjectType = 'UCSF' THEN 'UCS Fabric'
        WHEN e.NetObjectType = 'UCSFAN' THEN 'UCS Fan'
        WHEN e.NetObjectType = 'UCSM' THEN 'UCS Manager'
        WHEN e.NetObjectType = 'UCSPSU' THEN 'UCS Psu'
        WHEN e.NetObjectType = 'UE-DNS' THEN 'UDT: Rogue DNSName or Hostname'
        WHEN e.NetObjectType = 'UE-IP' THEN 'UDT: Rogue EmptyDNSName or IPAddress'
        WHEN e.NetObjectType = 'UE-MAC' THEN 'New MAC Vendor or Moved, New, Rogue MAC Address'
        WHEN e.NetObjectType = 'UNDPI' THEN 'Custom Interface Poller'
        WHEN e.NetObjectType = 'UNDPN' THEN 'Custom Node Poller'
        WHEN e.NetObjectType = 'UNDPT' THEN 'Custom Node Table Poller'
        WHEN e.NetObjectType = 'UP' THEN 'AccessPort'
        WHEN e.NetObjectType = 'UW' THEN 'Watch List'
        WHEN e.NetObjectType = 'V' THEN 'Volume'
        WHEN e.NetObjectType = 'VCCM' THEN 'VoIP CallManager'
        WHEN e.NetObjectType = 'VCCMP' THEN 'VoIP Phone'
        WHEN e.NetObjectType = 'VCDS' THEN 'VoIP Call Details'
        WHEN e.NetObjectType = 'VG' THEN 'VoIP Gateway'
        WHEN e.NetObjectType = 'VH' THEN 'Virtual Host'
        WHEN e.NetObjectType = 'VMC' THEN 'Virtual Cluster'
        WHEN e.NetObjectType = 'VMD' THEN 'Virtual DataCenter'
        WHEN e.NetObjectType = 'VMS' THEN 'Virtual Datastore'
        WHEN e.NetObjectType = 'VR' THEN 'VoIP Region'
        WHEN e.NetObjectType = 'VRF' THEN 'VRF'
        WHEN e.NetObjectType = 'VVC' THEN 'Virtual Center'
        WHEN e.NetObjectType = 'VVG' THEN 'VoIP PRI Gateway'
        WHEN e.NetObjectType = 'VVGT' THEN 'VoIP PRI Trunk'
        WHEN e.NetObjectType = 'VVM' THEN 'Virtual Machine'
        WHEN e.NetObjectType = 'WLAP' THEN 'Wireless Access Point'
        WHEN e.NetObjectType = 'WLC' THEN 'Wireless Controller'
        WHEN e.NetObjectType = 'WLHM' THEN 'Wireless Heatmap'
        WHEN e.NetObjectType IS NULL AND e.EventMessage LIKE 'IPAM Service%' THEN 'Orion Engine'
        WHEN e.NetObjectType IS NULL AND e.EventMessage LIKE '%trap packet%' THEN 'SNMP Trap'
        WHEN e.NetObjectType IS NULL AND (e.EventMessage LIKE '%Nightly Maintenance%' OR e.EventMessage LIKE '%database maintenance%') THEN 'Orion Engine'
        ELSE 'N/A'
        END AS ObjectType,
        e.NetObjectValue,
        e.NetworkNode,
        e.InstanceType,
        e.EventTime
      FROM 
        Orion.Events e
      JOIN 
        Orion.EventTypes et ON e.EventType = et.EventType
      
        • Re: SWQL Studio Error (Unknown dataprovider type 'ntext')
          tdanner

          There's a lot here, so I'll try to provide some info and you can let me know if there are still unresolved questions.

           

          1. The Orion.NetObjectTypes entity is used for a few purposes internally that justify including entries that don't actually have a defined net object prefix.

          2. I couldn't reproduce the "Unknown dataprovider type 'ntext'" error, but then I am probably not running the same combination of products versions as you. If it is still an issue for you let me know what set of versions you are running and I'll try again to reproduce the error.

          3. Some net object types have more than one key property. In this case, there will be one row in Orion.NetObjectTypes for each key property. If you aren't looking at the KeyProperty and KeyPropertyIndex columns, this will look like duplicate rows. If you join to this table on the Prefix column without including a "KeyPropertyIndex=0" filter, you will wind up with duplicated data, as you observed.

          4. When joining to Orion.NetObjectTypes on the Prefix column, you would also want to filter out rows with Prefix='' (the empty string) since there are several of those.

          5. Since some Orion.Events are not related to any particular object, you should use LEFT JOIN Orion.NetObjectTypes (as opposed to INNER) if you don't intend to filter out those events with no associated object.

            • Re: SWQL Studio Error (Unknown dataprovider type 'ntext')
              xtraspecialj

              Amazing response as always tdanner , thanks.

               

               

              2.)   SWQL Studio 2.1.13.0

               

              Orion modules:

              3.  Ok, I thought I understood about including the "KeyPropertyIndex = 0" line to remove duplicates until I started looking at the table more and noticed that a lot of the duplicate prefixes havve a KeyPropertyIndex of 0...  See further below. 

               

              Also, how do I use the "KeyProperty" field.  So I get that the KeyProperty is the column of the entity table that contains the ID field for that entity type, (like Prefix AA has KeyProperty of ApplicationID for example) but what can I do with that information?  I don't see how it's useful for joining.

               

               

              Here are the only fields that have a KeyPropertyIndex other than zero.  There are a bunch more duplicates than just the few that have a KeyPropertyIndex greater than zero.

               

               

              Thanks again for your help.

                • Re: SWQL Studio Error (Unknown dataprovider type 'ntext')
                  tdanner

                  One of the things this mapping table is used for is to guide constructing the net object id string (like "N:42") from a SWIS entity instance (like an instance of Orion.Nodes with a bunch of properties, including NodeID=42). This net object id string is used in a few places, most notably website navigation. If you have a net object id string, you can go to "/Orion/View.aspx?NetObject=N:42" and it will redirect you to the appropriate details page for that object regardless of its type.

                   

                  It looks like UDT has gone a bit off script in defining multiple SWIS entity types with the same net object prefix. This doesn't interfere with the SWIS EntityType -> Net object id conversion I just described, but it is definitely an issue for going the other way or looking up a object type name given a net object prefix. So what can you do in this case? The options I see are:

                   

                  1. the giant CASE statement workaround that you have already implemented
                  2. a smaller CASE statement that handles only net object prefixes that need special handling and delegates the majority non-duplicate ones to selecting from Orion.NetObjectTypes
                  3. "SELECT TOP 1 Name FROM Orion.NetObjectTypes WHERE Prefix={your prefix here}" and hope for the best

                   

                  If I were in your shoes, I would probably go with #2.