19 Replies Latest reply on May 2, 2016 10:26 AM by mikelowery

    Reporting Error

    mikelowery

      Hey Guys Recently we were editing some of our custom SQL Reports and we were greeted with this lovely error message;

       

      I'm not sure what the cause could be? Any suggestions? a few searches on Google pointed to a temp cache corruption.

       

       

      System.InvalidCastException: Invalid cast from 'Int32' to 'DateTime'. at

      System.Int32.System.IConvertible.ToDateTime(IFormatProvider provider) at System.Convert.ToDateTime(Object value) at

      SolarWinds.Orion.Web.Reporting.OrionReportField.b__5(Object o) at

      SolarWinds.Orion.Web.Reporting.OrionReportField.ParseValue(Object value) at

      SolarWinds.Orion.Web.Reporting.OrionReportHtmlFormatter.GetReport() at

      SolarWinds.Orion.Web.Reporting.ReportRunner.WorkerProc(Object state)

       

      Our SQL Code

       

      SELECT
      StartTime.EventTime,
      (SELECT TOP 1
      EventTime
      FROM Events AS Endtime
      WHERE EndTime.EventTime > StartTime.EventTime
      AND EndTime.EventType = 5
      AND EndTime.NetObjectType = 'N'
      AND EndTime.NetworkNode = StartTime.NetworkNode
      ORDER BY EndTime.EventTime) AS UpEventTime,
      Nodes.Caption, StartTime.Message, DATEDIFF(Mi, StartTime.EventTime,(SELECT TOP 1 EventTime FROM Events AS Endtime
      WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5 AND EndTime.NetObjectType = 'N'
      AND EndTime.NetworkNode = StartTime.NetworkNode  ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes
      FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
      WHERE (StartTime.EventType = 1)
      ORDER BY eventtime desc

       

       

      Thanks,

       

      Mike

        • Re: Reporting Error
          Steven Klassen

          The query runs fine here and I went through it line by line comparing data types to what I have in my database and I don't see any type mismatches. If you run the same query in SQL Studio do you get data back?

           

          --

          Steven W. Klassen

          Programmer Analyst @ Loop1 Systems

          http://www.loop1systems.com/

          http://www.linkedin.com/in/mrxinu

            • Re: Reporting Error
              mikelowery

              We are setting up a SQL Studio test server now to see what we get back. One of our programmers in devops seems to think that the error occured when we changed the outageduration in minutes variable. Not coming from a SQL background, it's been a learning experience haha.

                • Re: Reporting Error
                  Steven Klassen

                  mikelowery wrote:

                   

                  We are setting up a SQL Studio test server now to see what we get back. One of our programmers in devops seems to think that the error occured when we changed the outageduration in minutes variable. Not coming from a SQL background, it's been a learning experience haha.

                  Anything's possible I guess (since I can't reproduce it here) but you're not using the OutageDurationInMinutes field in any comparisons so the failure to cast (or convert) its data type from Int32 to DateTime doesn't make any sense. Typically you'll see that when you're trying to compare fields (WHERE x > y) or evaluate them (WHERE x + y = z) and you need both sides of the equation to be the same type so it can complete the work.

                   

                  --

                  Steven W. Klassen

                  Programmer Analyst @ Loop1 Systems

                  http://www.loop1systems.com/

                  http://www.linkedin.com/in/mrxinu

                  1 of 1 people found this helpful
                • Re: Reporting Error
                  mikelowery

                  Msg 208, Level 16, State 1, Line 1

                  Invalid object name 'Events'.

                   

                  Here is the error message that appeared in SQL Studio.

                    • Re: Reporting Error
                      Steven Klassen

                      mikelowery wrote:

                       

                      Msg 208, Level 16, State 1, Line 1

                      Invalid object name 'Events'.

                       

                      Here is the error message that appeared in SQL Studio.

                      In this case I think you're probably not targeting the right database. After you open a new query window if you look up and to the left, what does it say in the dropdown? If it still says "master" change that to your database and re-run your query. Alternatively, you can put the following above your query to make sure your query window has the right context:

                       

                      USE SolarWindsOrion
                      GO
                      

                       

                      ** Of course change from SolarWindsOrion to the name of your database if you changed it from the default.

                       

                      --

                      Steven W. Klassen

                      Programmer Analyst @ Loop1 Systems

                      http://www.loop1systems.com/

                      http://www.linkedin.com/in/mrxinu

                      1 of 1 people found this helpful
                        • Re: Reporting Error
                          oludads

                          Hello Steven,

                          Thanks for the reply.

                          I'm still having the same issue. But I have narrowed it down to this: Now this is SQL Statement I'm working with::

                          SELECT MAX(VendorIcon) AS VendorIcon,  MAX(Caption) AS Caption,  MAX(StatusLED) AS StatusLED, NodeID, SUM(OutageDurationInMinutes) AS SumOutageDurationInMinutes  FROM (

                            SELECT

                            Nodes.VendorIcon,

                            Nodes.Caption,

                            Nodes.StatusLED,

                            Nodes.NodeID,

                            StartTime.Message,

                            StartTime.EventTime AS DownEventTime,

                            (

                            SELECT TOP 1 EventTime

                            FROM Events AS EndTimeTable

                            where EndTimeTable.EventTime >= StartTime.EventTime

                            AND EndTimeTable.EventType = 5

                            AND EndTimeTable.NetObjectType = 'N'

                            AND EndTimeTable.NetworkNode = StartTime.NetworkNode

                            AND EventTime IS NOT NULL

                            ORDER BY EndTimeTable.EventTime

                            ) AS UpEventTime,

                            DATEDIFF(Mi, StartTime.EventTime,(

                            SELECT TOP 1 EventTime FROM Events AS Endtime

                            where EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5 AND EndTime.NetObjectType = 'N'

                            AND EndTime.NetworkNode = StartTime.NetworkNode  ORDER BY EndTime.EventTime)

                            ) AS OutageDurationInMinutes

                            FROM Events StartTime

                            INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID

                            WHERE (StartTime.EventType = 1)

                          ) AS UpTimeTable

                          where outageDurationInMinutes IS NOT NULL

                          GROUP BY NodeID

                          ORDER BY Caption ASC

                           

                          I think the Italicized syntax is what needs to change. Basically, I want to change the  SUM(OutageDurationInMinutes) to reflect this "HH24:MI:SS". I have tried numerous MYSQL Date Format but not working.

                           

                          This is the screenshot below. The SUM of Outage Duration In Minutes is what needs to change to "HH24:MI:SS"..  Thanks for your help.

                           

                           

                            • Re: Reporting Error
                              Steven Klassen

                              Hi oludads - you mention an outage duration in minutes and then a time format (HH24:MI:SS). Can you tell me what you'd envision that last column looking like? What would an outage duration in HH24:MI:SS look like?

                               

                              --

                              Steven W. Klassen

                              Programmer Analyst @ Loop1 Systems

                              http://www.loop1systems.com/

                              http://www.linkedin.com/in/mrxinu

                                • Re: Reporting Error
                                  oludads

                                  Hello Steven,

                                   

                                  Just wondering if you got my reply to the question you asked last week?

                                   

                                  Thanks

                                    • Re: Reporting Error
                                      Steven Klassen

                                      I did and I asked a follow up question about what that'd look like. Did you see that? I think we're close to a solution but I need info. ☺

                                      • Re: Reporting Error
                                        Steven Klassen

                                        I've followed up with a PM.

                                          • Re: Reporting Error
                                            oludads

                                            Hey Steven,

                                            I'm not sure I received your follow up question. Was this the follow up question? " Hi oludads - you mention an outage duration in minutes and then a time format (HH24:MI:SS). Can you tell me what you'd envision that last column looking like? What would an outage duration in HH24:MI:SS look like?"

                                             

                                            And who is PM?

                                             

                                            Thanks

                                              • Re: Reporting Error
                                                Steven Klassen

                                                Yes it was! What's the answer? And a PM is a private message.

                                                  • Re: Reporting Error
                                                    oludads

                                                    Hey Steven,

                                                    This is what I replied to the question above.

                                                     

                                                    Hello Steven,

                                                    Thanks for the reply.

                                                    I'm still having the same issue. But I have narrowed it down to this: Now this is SQL Statement I'm working with::

                                                    SELECT MAX(VendorIcon) AS VendorIcon,  MAX(Caption) AS Caption,  MAX(StatusLED) AS StatusLED, NodeID, SUM(OutageDurationInMinutes) AS SumOutageDurationInMinutes  FROM (

                                                      SELECT

                                                      Nodes.VendorIcon,

                                                      Nodes.Caption,

                                                      Nodes.StatusLED,

                                                      Nodes.NodeID,

                                                      StartTime.Message,

                                                      StartTime.EventTime AS DownEventTime,

                                                      (

                                                      SELECT TOP 1 EventTime

                                                      FROM Events AS EndTimeTable

                                                      where EndTimeTable.EventTime >= StartTime.EventTime

                                                      AND EndTimeTable.EventType = 5

                                                      AND EndTimeTable.NetObjectType = 'N'

                                                      AND EndTimeTable.NetworkNode = StartTime.NetworkNode

                                                      AND EventTime IS NOT NULL

                                                      ORDER BY EndTimeTable.EventTime

                                                      ) AS UpEventTime,

                                                      DATEDIFF(Mi, StartTime.EventTime,(

                                                      SELECT TOP 1 EventTime FROM Events AS Endtime

                                                      where EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5 AND EndTime.NetObjectType = 'N'

                                                      AND EndTime.NetworkNode = StartTime.NetworkNode  ORDER BY EndTime.EventTime)

                                                      ) AS OutageDurationInMinutes

                                                      FROM Events StartTime

                                                      INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID

                                                      WHERE (StartTime.EventType = 1)

                                                    ) AS UpTimeTable

                                                    where outageDurationInMinutes IS NOT NULL

                                                    GROUP BY NodeID

                                                    ORDER BY Caption ASC

                                                     

                                                    I think the Italicized syntax is what needs to change. Basically, I want to change the  SUM(OutageDurationInMinutes) to reflect this "HH24:MI:SS". I have tried numerous MYSQL Date Format but not working.

                                                     

                                                    This is the screenshot below. The SUM of Outage Duration In Minutes is what needs to change to "HH24:MI:SS"..  Thanks for your help.

                                                     

                                                     

                                                     

                                                    Let me know if this makes sense.

                                                    Thanks

                                                      • Re: Reporting Error
                                                        Steven Klassen

                                                        Okay, taking another stab at this. If this doesn't help or move us forward I propose we hop on a GoToMeeting session over the weekend and discuss it real-time. I'm determined to help you fix this!

                                                         

                                                        2016-04-26_16-59-41.png

                                                        1 of 1 people found this helpful
                                                          • Re: Reporting Error
                                                            oludads

                                                            That's exactly it Steven. The '01:30:02' to represent 1 hour, 30 minutes, and 2 seconds is what we are looking to have. Thank you Steven, you narrowed it down to the point.

                                                              • Re: Reporting Error
                                                                Steven Klassen

                                                                Here's my first swing at it - since the outage is already being presented in minutes I went to hours & minutes with it:

                                                                2016-04-27_23-16-09-filtered.png

                                                                Here's the adjusted SQL:

                                                                 

                                                                SELECT tbl.VendorIcon, tbl.Caption, tbl.StatusLED, tbl.NodeID,
                                                                       (tbl.SumOutageDurationInMinutes / 60) AS Hours,
                                                                       (tbl.SumOutageDurationInMinutes - ((tbl.SumOutageDurationInMinutes / 60) * 60)) AS Minutes
                                                                FROM
                                                                (
                                                                SELECT MAX(VendorIcon) AS VendorIcon,  MAX(Caption) AS Caption,  MAX(StatusLED) AS StatusLED, NodeID, SUM(OutageDurationInMinutes) AS SumOutageDurationInMinutes
                                                                FROM (
                                                                  SELECT
                                                                  Nodes.VendorIcon,
                                                                  Nodes.Caption,
                                                                  Nodes.StatusLED,
                                                                  Nodes.NodeID,
                                                                  StartTime.Message,
                                                                  StartTime.EventTime AS DownEventTime,
                                                                  (
                                                                  SELECT TOP 1 EventTime
                                                                  FROM Events AS EndTimeTable
                                                                  where EndTimeTable.EventTime >= StartTime.EventTime
                                                                  AND EndTimeTable.EventType = 5
                                                                  AND EndTimeTable.NetObjectType = 'N'
                                                                  AND EndTimeTable.NetworkNode = StartTime.NetworkNode
                                                                  AND EventTime IS NOT NULL
                                                                  ORDER BY EndTimeTable.EventTime
                                                                  ) AS UpEventTime,
                                                                  DATEDIFF(Mi, StartTime.EventTime,(
                                                                  SELECT TOP 1 EventTime FROM Events AS Endtime
                                                                  where EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5 AND EndTime.NetObjectType = 'N'
                                                                  AND EndTime.NetworkNode = StartTime.NetworkNode  ORDER BY EndTime.EventTime)
                                                                  ) AS OutageDurationInMinutes
                                                                  FROM Events StartTime
                                                                  INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
                                                                  WHERE (StartTime.EventType = 1)
                                                                ) AS UpTimeTable
                                                                where outageDurationInMinutes IS NOT NULL
                                                                GROUP BY NodeID
                                                                ) tbl
                                                                --ORDER BY Caption ASC
                                                                

                                                                 

                                                                The next step is going to be changing the DATEDIFF to provides seconds instead of minutes to get the hour/minute/second precision. I'll take another look at it as soon as I've slept - it's been a long day.