5 Replies Latest reply on Jul 28, 2010 9:39 AM by mezdem

    Report Writer - Limitations / Duplicate Nodes Discovered

    mezdem

      Is there a way or any plans to include a 'show sql code' function in the report writer as it has numerous limitations, If users could see the code behind the report, then adapt it slightly the report writer would give everyone what they needed..fully customisable!!

      One problem I have found is that we have duplicate nodes (names not IP addresses), depending on where they were scanned from.. example:

      I know they exist as I can see them in sql, and when I add the node id in report writer is also shows me.. but you cannot properly do a dupe check, as when you take the node id out, and list Node name/ System Name and perform a count on this field it seems to 'de-dupe' the returned list for you, but what it should do is return the full list with a count of duplicate names in column 2.

      Now this is issue one, and could easily be fixed by adding the 'view sql code' for this report function. As users could then modify the actual code - which probably has a DISTINCT select in it for some reason!?

      I would then also add that these nodes should not of been added into the orion database, it should of surely picked up all of these as duplicate Node names? then added the IP address to the existing node as an alternate interface type /poll status?

        • Re: Report Writer - Limitations / Duplicate Nodes Discovered

          I think the functionality you are looking for is already there.  When you have the report open in report writer, select Report from the menu bar and look for Show SQL on the menu.  Selecting this option will add a tab to the report that displays the SQL.

            • Re: Report Writer - Limitations / Duplicate Nodes Discovered
              sean.martinez

              You are correct. You can View the SQL code in Report Writer under Reports> Show SQL.

              You however cannot modify the SQL in the Report directly.

              The Report Writer also has an option to create a Report titled: "Advanced SQL"

                • Re: Report Writer - Limitations / Duplicate Nodes Discovered
                  mezdem

                  arhhh excellent - I just missed that option in the menu bar! great stuff that helps anyway o:)

                  Dont suppose there is any feedback on the duplicate node issue??? Now the sql shown is..

                  SELECT
                  Nodes.Caption AS NodeName, COUNT(DISTINCT Nodes.Caption) AS COUNT_of_NodeName
                   FROM
                  Nodes

                   GROUP BY Nodes.Caption

                   

                  It does contain the Distinct clause.. even though you are listing the node name, it is then performing a Distinct Count on the name, rather than just a count!

                    • Re: Report Writer - Limitations / Duplicate Nodes Discovered

                      I use an advanced SQL report with the code below for my duplicate node name report.  However, It doesn't give a count of the number of duplicates per name.

                      SELECT Nodes.Caption, Nodes.NodeID, Nodes.IP_Address
                      FROM Nodes
                      WHERE (((Nodes.Caption) In (SELECT [Caption] FROM [Nodes] As Tmp GROUP BY [Caption] HAVING Count(*)>1 )))
                      ORDER BY Nodes.Caption

                      You could use code like this to get a report that lists the number of duplicates for each node name.

                      SELECT Nodes.Caption, Count(Nodes.Caption) as Number_of_Duplicates
                      FROM Nodes
                      WHERE (((Nodes.Caption) In (SELECT [Caption] FROM [Nodes] As Tmp GROUP BY [Caption] HAVING Count(*)>1 )))
                      Group BY Nodes.Caption

                      1 of 1 people found this helpful