12 Replies Latest reply on Aug 8, 2014 10:49 AM by Craig Norborg

    Report will not produce the correct output....!!!

    k1gaudineer

      I started out editing this existing report thinking that the task was extremely simple...  Turns out I am going crazy trying to figure out why the report is blank after running it.

       

      Here is my scenario....

       

      I have an exiting report that I use in a custom resource view on one of our web pages.  Its a simple report that shows all nodes that are Unmanaged.  The report only has one elementary condition....  Show all nodes whose status is unmanaged...  We are now in the process of acquiring another partner and they also have and Orion platform.  I am migrating their devices to our Orion installation.  While I am working on importing the nodes I am also setting them to unmanaged until the entire task is complete.  The problem with this is that when I set them to unmanaged these nodes also appear in the report mentioned above.  We want to prevent these nodes from showing up in that report.

       

      Part of the import process is making sure that a custom property field is tagged with each new node that is imported.  The custom Property field is filled in with NewCust.  All I did was add the custom property filed to the report and then added one line to the report.  so now the report lines look like

       

                select records where All of the following apply

                     Records where Status is equal to  Unmanaged (when this was the only line the report worked fine)

                     Records where Cust is not equal to NewCust (This is the line I added.  now when the report runs it is blank nothing shows up)

       

      The way I am thinking this would run is  the first line will grab all of the nodes where the status is Unmanaged and the second line will pick the records from that list where the 'Cusst' field is blank or contains some other value...  What I am finding out is that is not the case the report is just blank.  But if I uncheck the line I added the report runs fine but includes the NewCust nodes we don't want to see.

       

      If anyone has a tip on how my logic has derailed with this comparison it would be greatly appreciated.......

        • Re: Report will not produce the correct output....!!!
          HolyGuacamole

          Logic looks fine. The resultant SQL also should be fine. So, it maybe to do with the data values for this custom property. Use the Report > Show SQL option from the Report Writer designer view, copy the SQL into a new Advanced SQL report  and modify this line in the WHERE clause

           

          Cust <> 'NewCust'

           

          to

           

          Cust NOT LIKE '%NewCust%'

           

          If this returns the data you need, take a closer look at the custom property values.

            • Re: Report will not produce the correct output....!!!
              k1gaudineer

              Thanks for the reply...  I tried making the changes but the report is still blank....  When I convert my report to SQL this is what it looks like......

               

              Select NodeName, IP_Address, Status, Status_Icon,  Cast(Cust As nvarchar(250)) as Cust From ( SELECT

              Nodes.Caption AS NodeName, Nodes.IP_Address AS IP_Address, Nodes.Status AS Status, Nodes.StatusLED AS Status_Icon, Nodes.Cust AS Cust

              FROM

              Nodes

              WHERE  ( 

              (Nodes.Status = '9') AND 

              (Nodes.Cust <> 'NewCust')

              )

              ) As r

               

              I changed the line to Nodes.Cust <> 'NewCust'  to  Nodes.Cust NOT LIKE '%NewCust%'

               

              After running the SQL the report is still blank....

               

              If I change the report back to

               

              Select NodeName, IP_Address, Status, Status_Icon,  Cast(Cust As nvarchar(250)) as Cust From ( SELECT

              Nodes.Caption AS NodeName, Nodes.IP_Address AS IP_Address, Nodes.Status AS Status, Nodes.StatusLED AS Status_Icon, Nodes.Cust AS Cust

              FROM

              Nodes

              WHERE  (  

              (Nodes.Status = '9')

              )

              ) As r 

               

              The report will run and the report will show all nodes that are unmanaged including the nodes that have the custom property 'Cust = NewCust'

               

              Is it possible that the Custom Property field can not be used as a filter in a nodes Report?

                • Re: Report will not produce the correct output....!!!
                  HolyGuacamole

                  There is no restriction on using custom properties in report fields. Can you post a screenshot of your report which currently shows the Cust column along with the NewCust values?

                    • Re: Report will not produce the correct output....!!!
                      HolyGuacamole

                      Wait! Why is there a sub-query in the SQL? Can you attach the .OrionReport file instead?

                      • Re: Report will not produce the correct output....!!!
                        k1gaudineer

                        Here is the report with the NewCust Records showing....

                         

                        Interestingly enough I noticed that the new customer does have an IP range that is unique to them so if I change the second line of the report query to be

                        Select records where IP Address CONTAINS 10.  The report will display correctly....  I still don't understand why I can't do the same thing with the Custom Property

                          • Re: Report will not produce the correct output....!!!
                            k1gaudineer

                            Sorry Posed previous answer before seeing your follow up post....  Here is the .OrionReport file

                             

                            <Report Version="1.0" Group="Current" Title="Unmanaged Nodes" ModuleTitle="" Type="Current" TypeDescription="Current Status of Nodes, Volumes, etc." Icon="Report" Schema="Inventory.Schema" SubTitle="This report will give a list of nodes currently unmanaged." Description="" Footer="" Time_Frame="Named" Named_Time_Frame="Last 30 Days" Relative_Time_Frame="24 Hours" Starting_DateTime="6/30/2014 0:0:0" Ending_DateTime="7/31/2014 0:0:0" Grouping="Date" Group_Position="Beginning" SQL="Select NodeName, IP_Address, Status, Status_Icon,  Cast(Cust As nvarchar(250)) as Cust From ( SELECT

                            Nodes.Caption AS NodeName, Nodes.IP_Address AS IP_Address, Nodes.Status AS Status, Nodes.StatusLED AS Status_Icon, Nodes.Cust AS Cust

                            FROM

                            Nodes

                            WHERE

                            (

                              (Nodes.Status = '9')

                            )

                            ) As r " TopX="All" TopXCount="10" TopXPercent="10" Orientation="Landscape" CookedData="TRUE" Web="TRUE" GroupByDays="TRUE" ShowFolders=""><QueryBuilder><Fields><QUERY><KIND>2</KIND><COMPLEX><TAG></TAG><CONNECTIVE>1</CONNECTIVE><CHECKED>1</CHECKED><SIMPLE><TAG></TAG><ALIAS></ALIAS><ADVANCED>0</ADVANCED><COMPARISON>0</COMPARISON><FUNCTION>0</FUNCTION><SORT>0</SORT><CHECKED>0</CHECKED><LEFTSIDEKIND>2</LEFTSIDEKIND><RIGHTSIDEKIND>1</RIGHTSIDEKIND><COMPARISONATTRIBUTES></COMPARISONATTRIBUTES><FUNCTIONATTRIBUTES></FUNCTIONATTRIBUTES><LEFTFIELDPATH>Network Nodes.Node Details.Node ID</LEFTFIELDPATH><RIGHTFIELDPATH></RIGHTFIELDPATH><LEFTVALUETYPE>0</LEFTVALUETYPE><LEFTVALUE></LEFTVALUE><LEFTCAPTION>Node ID</LEFTCAPTION><RIGHTVALUETYPE>0</RIGHTVALUETYPE><RIGHTVALUE></RIGHTVALUE><RIGHTCAPTION></RIGHTCAPTION></SIMPLE><SIMPLE><TAG></TAG><ALIAS></ALIAS><ADVANCED>0</ADVANCED><COMPARISON>0</COMPARISON><FUNCTION>0</FUNCTION><SORT>0</SORT><CHECKED>1</CHECKED><LEFTSIDEKIND>2</LEFTSIDEKIND><RIGHTSIDEKIND>1</RIGHTSIDEKIND><COMPARISONATTRIBUTES></COMPARISONATTRIBUTES><FUNCTIONATTRIBUTES></FUNCTIONATTRIBUTES><LEFTFIELDPATH>Network Nodes.Node Details.Node Name</LEFTFIELDPATH><RIGHTFIELDPATH></RIGHTFIELDPATH><LEFTVALUETYPE>0</LEFTVALUETYPE><LEFTVALUE></LEFTVALUE><LEFTCAPTION>Node Name</LEFTCAPTION><RIGHTVALUETYPE>0</RIGHTVALUETYPE><RIGHTVALUE></RIGHTVALUE><RIGHTCAPTION></RIGHTCAPTION></SIMPLE><SIMPLE><TAG></TAG><ALIAS></ALIAS><ADVANCED>0</ADVANCED><COMPARISON>0</COMPARISON><FUNCTION>0</FUNCTION><SORT>0</SORT><CHECKED>1</CHECKED><LEFTSIDEKIND>2</LEFTSIDEKIND><RIGHTSIDEKIND>1</RIGHTSIDEKIND><COMPARISONATTRIBUTES></COMPARISONATTRIBUTES><FUNCTIONATTRIBUTES></FUNCTIONATTRIBUTES><LEFTFIELDPATH>Network Nodes.Node Details.IP Address</LEFTFIELDPATH><RIGHTFIELDPATH></RIGHTFIELDPATH><LEFTVALUETYPE>0</LEFTVALUETYPE><LEFTVALUE></LEFTVALUE><LEFTCAPTION>IP Address</LEFTCAPTION><RIGHTVALUETYPE>0</RIGHTVALUETYPE><RIGHTVALUE></RIGHTVALUE><RIGHTCAPTION></RIGHTCAPTION></SIMPLE><SIMPLE><TAG></TAG><ALIAS></ALIAS><ADVANCED>0</ADVANCED><COMPARISON>0</COMPARISON><FUNCTION>0</FUNCTION><SORT>0</SORT><CHECKED>1</CHECKED><LEFTSIDEKIND>2</LEFTSIDEKIND><RIGHTSIDEKIND>1</RIGHTSIDEKIND><COMPARISONATTRIBUTES></COMPARISONATTRIBUTES><FUNCTIONATTRIBUTES></FUNCTIONATTRIBUTES><LEFTFIELDPATH>Network Nodes.Node Status.Status</LEFTFIELDPATH><RIGHTFIELDPATH></RIGHTFIELDPATH><LEFTVALUETYPE>0</LEFTVALUETYPE><LEFTVALUE></LEFTVALUE><LEFTCAPTION>Status</LEFTCAPTION><RIGHTVALUETYPE>0</RIGHTVALUETYPE><RIGHTVALUE></RIGHTVALUE><RIGHTCAPTION></RIGHTCAPTION></SIMPLE><SIMPLE><TAG></TAG><ALIAS></ALIAS><ADVANCED>0</ADVANCED><COMPARISON>0</COMPARISON><FUNCTION>0</FUNCTION><SORT>0</SORT><CHECKED>0</CHECKED><LEFTSIDEKIND>2</LEFTSIDEKIND><RIGHTSIDEKIND>1</RIGHTSIDEKIND><COMPARISONATTRIBUTES></COMPARISONATTRIBUTES><FUNCTIONATTRIBUTES></FUNCTIONATTRIBUTES><LEFTFIELDPATH>Network Nodes.Node Status.Status Description</LEFTFIELDPATH><RIGHTFIELDPATH></RIGHTFIELDPATH><LEFTVALUETYPE>0</LEFTVALUETYPE><LEFTVALUE></LEFTVALUE><LEFTCAPTION>Status Description</LEFTCAPTION><RIGHTVALUETYPE>0</RIGHTVALUETYPE><RIGHTVALUE></RIGHTVALUE><RIGHTCAPTION></RIGHTCAPTION></SIMPLE><SIMPLE><TAG></TAG><ALIAS></ALIAS><ADVANCED>0</ADVANCED><COMPARISON>0</COMPARISON><FUNCTION>0</FUNCTION><SORT>0</SORT><CHECKED>1</CHECKED><LEFTSIDEKIND>2</LEFTSIDEKIND><RIGHTSIDEKIND>1</RIGHTSIDEKIND><COMPARISONATTRIBUTES></COMPARISONATTRIBUTES><FUNCTIONATTRIBUTES></FUNCTIONATTRIBUTES><LEFTFIELDPATH>Network Nodes.Node Status.Status Icon</LEFTFIELDPATH><RIGHTFIELDPATH></RIGHTFIELDPATH><LEFTVALUETYPE>0</LEFTVALUETYPE><LEFTVALUE></LEFTVALUE><LEFTCAPTION>Status Icon</LEFTCAPTION><RIGHTVALUETYPE>0</RIGHTVALUETYPE><RIGHTVALUE></RIGHTVALUE><RIGHTCAPTION></RIGHTCAPTION></SIMPLE><SIMPLE><TAG></TAG><ALIAS></ALIAS><ADVANCED>0</ADVANCED><COMPARISON>0</COMPARISON><FUNCTION>0</FUNCTION><SORT>0</SORT><CHECKED>1</CHECKED><LEFTSIDEKIND>2</LEFTSIDEKIND><RIGHTSIDEKIND>1</RIGHTSIDEKIND><COMPARISONATTRIBUTES></COMPARISONATTRIBUTES><FUNCTIONATTRIBUTES></FUNCTIONATTRIBUTES><LEFTFIELDPATH>Network Nodes.Custom Properties.Cust</LEFTFIELDPATH><RIGHTFIELDPATH></RIGHTFIELDPATH><LEFTVALUETYPE>0</LEFTVALUETYPE><LEFTVALUE></LEFTVALUE><LEFTCAPTION>Cust</LEFTCAPTION><RIGHTVALUETYPE>0</RIGHTVALUETYPE><RIGHTVALUE></RIGHTVALUE><RIGHTCAPTION></RIGHTCAPTION></SIMPLE></COMPLEX></QUERY>

                                      </Fields><Filter><QUERY><KIND>1</KIND><COMPLEX><TAG></TAG><CONNECTIVE>1</CONNECTIVE><CHECKED>1</CHECKED><SIMPLE><TAG></TAG><ALIAS></ALIAS><ADVANCED>0</ADVANCED><COMPARISON>0</COMPARISON><FUNCTION>0</FUNCTION><SORT>0</SORT><CHECKED>1</CHECKED><LEFTSIDEKIND>2</LEFTSIDEKIND><RIGHTSIDEKIND>1</RIGHTSIDEKIND><COMPARISONATTRIBUTES></COMPARISONATTRIBUTES><FUNCTIONATTRIBUTES></FUNCTIONATTRIBUTES><LEFTFIELDPATH>Network Nodes.Node Status.Status</LEFTFIELDPATH><RIGHTFIELDPATH></RIGHTFIELDPATH><LEFTVALUETYPE>0</LEFTVALUETYPE><LEFTVALUE></LEFTVALUE><LEFTCAPTION>Status</LEFTCAPTION><RIGHTVALUETYPE>8</RIGHTVALUETYPE><RIGHTVALUE>9</RIGHTVALUE><RIGHTCAPTION>Unmanaged</RIGHTCAPTION></SIMPLE><SIMPLE><TAG></TAG><ALIAS></ALIAS><ADVANCED>0</ADVANCED><COMPARISON>8</COMPARISON><FUNCTION>0</FUNCTION><SORT>0</SORT><CHECKED>0</CHECKED><LEFTSIDEKIND>2</LEFTSIDEKIND><RIGHTSIDEKIND>1</RIGHTSIDEKIND><COMPARISONATTRIBUTES></COMPARISONATTRIBUTES><FUNCTIONATTRIBUTES></FUNCTIONATTRIBUTES><LEFTFIELDPATH>Network Nodes.Node Details.IP Address</LEFTFIELDPATH><RIGHTFIELDPATH></RIGHTFIELDPATH><LEFTVALUETYPE>0</LEFTVALUETYPE><LEFTVALUE></LEFTVALUE><LEFTCAPTION>IP Address</LEFTCAPTION><RIGHTVALUETYPE>8</RIGHTVALUETYPE><RIGHTVALUE>10</RIGHTVALUE><RIGHTCAPTION>10</RIGHTCAPTION></SIMPLE></COMPLEX></QUERY>

                                      </Filter></QueryBuilder><LayoutGroups></LayoutGroups><Fields><Field Name="NodeName" Header="Node" Range="ALL" Parser="None" Format="" Alignment="Left" Width="0" Units="" Hidden="FALSE" WebURL="/Orion/View.aspx?View=NodeDetails&amp;NetObject=N:${NodeID}" BaseTableName="Nodes"></Field><Field Name="IP_Address" Header="IP Address" Range="ALL" Parser="None" Format="" Alignment="Left" Width="0" Units="" Hidden="FALSE" WebURL="/Orion/View.aspx?View=NodeDetails&amp;NetObject=N:${NodeID}" BaseTableName="Nodes"></Field><Field Name="Status" Header="Status" Range="ALL" Parser="Status" Format="" Alignment="Center" Width="0" Units="" Hidden="FALSE" WebURL="" BaseTableName="Nodes"></Field><Field Name="Status_Icon" Header="Status" Range="ALL" Parser="Icon" Format="Status" Alignment="Center" Width="0" Units="" Hidden="FALSE" WebURL="" BaseTableName="Nodes"></Field><Field Name="Cust" Header="Cust" Range="ALL" Parser="None" Format="" Width="0" Units="" Hidden="FALSE" WebURL="" BaseTableName="Nodes"></Field></Fields></Report>

                              • Re: Report will not produce the correct output....!!!
                                HolyGuacamole

                                In your screenshot, you have scrubbed the original values and super-imposed the text 'NewCust'. So, I still think it is something to do with the values you have used for custom properties. It works perfectly fine here. I have a custom property called Cust, 2 unmanaged nodes, 1 of them with Cust value OldCust and the other one with NewCust

                                Core-Unmanaged-Nodes.png

                                  • Re: Report will not produce the correct output....!!!
                                    k1gaudineer

                                    Thank You for your replies....   I did more testing yesterday and this is what I am concluding...

                                     

                                    I believe you are correct that there is something wrong with that Custom Property 'Cust'.  That Custom Property is setup as text but the values that go into that field are chosen from a drop down value list.  If I create a whole new Custom Property and name it 'Cust-1' and then manually type the values I want into that Custom property the filter will work just as it did in your most recent example above.  I tried it this way because when I tried Mr. Norborg's SWQL example that query was also blank.  When I tried the SWQL example with the new Custom Property that query also ran as expected.

                                     

                                    For my use case here I will need to pick a different field to run the comparison with because we want to keep the 'Cust' field consistent so the drop down value list will need to stay.  I can identify the unique pattern in the IP address between the records I want to display and the records I don't want to display so I will continue to use that filter for now.

                                     

                                    Thanks again for everyone's input.

                            • Re: Report will not produce the correct output....!!!
                              Craig Norborg

                              Just shooting blindly, rather than do it as a report, have you thought about doing it as an SWQL query?   Then you would add the resource to the page as as a "Custom Query" resource instead...   I'm thinking it would look something like below.  I didn't have your custom property, so I substituted one of my own when I tested this, but it should work I think...

                               

                              SELECT NodeName, IPAddress, '/Orion/images/StatusIcons/' + StatusIcon AS [_IconFor_NodeName], Nodes.CustomProperties.Cust

                              FROM Orion.Nodes

                              WHERE (Nodes.Status = 9) AND (Nodes.CustomProperties.Cust <> 'NewCust')

                               

                              Reports take forever to render, this should come up quicker and use less CPU too...   Looks pretty simple, so I think it should work as you think it should...    Since its SWQL and you're just using node properties and custom properties there isn't any reason to do a join like above...

                               

                              Look up the Orion SDK for more info on SWQL, it is a bit different than SQL.