7 Replies Latest reply on Sep 25, 2013 7:05 PM by njoylif

    what wrong in this statement

    sartous

      SELECT  TOP 10000  CONVERT(DateTime,
      LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
      101) AS SummaryMonth,
      Nodes.NodeID AS NodeID,
      Nodes.VendorIcon AS Vendor_Icon,
      MAX(Nodes.CPULoad) AS MAX_of_CPULoad,
      Volumes.VolumeID AS VolumeID,
      Nodes.Caption AS NodeName,
      Volumes.Caption AS Caption,
      Volumes.VolumeTypeIcon AS VolumeTypeIcon,
      AVG(VolumeUsage.DiskSize) AS AVERAGE_of_Disk_Size,
      AVG(VolumeUsage.AvgDiskUsed) AS AVERAGE_of_AvgDiskUsed,
      MAX(VolumeUsage.MaxDiskUsed) AS MAX_of_MaxDiskUsed,
      Nodes.IP_Address AS IP_Address,
      AVG(CPULoad.MAXLoad) AS AVERAGE_of_CPULoad

      FROM
      (Nodes INNER JOIN Volumes ON (Nodes.NodeID = Volumes.NodeID))  INNER JOIN VolumeUsage ON (Volumes.VolumeID = VolumeUsage.VolumeID)
      INNER JOIN CPULoad ON (Nodes.NodeID = CPULoad.NodeID)

      WHERE

      (
        (Nodes.Team = 'operations') AND
        (Nodes.Status <> '9')
      )

       

       

      GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
      Nodes.NodeID, Nodes.VendorIcon, Volumes.VolumeID, Nodes.Caption, Volumes.Caption, Volumes.VolumeTypeIcon, Nodes.IP_Address


      ORDER BY SummaryMonth ASC, 6 ASC, 7 ASC

        • Re: what wrong in this statement
          njoylif

          what error are you getting and where are you trying to run this?  in Orion DB Manager, SQL mgmt studio, SWQL?

          • Re: what wrong in this statement
            rgward

            Do you have a custom property called 'Nodes.Team'?  That's where it throws the error.

            • Re: what wrong in this statement
              sean.martinez

              Looking at this SQL Statement, I made a few changes and got it to run. Running my SQL on SSD, the data took 3 minutes to return a result that wasn't usable. The issue I am seeing is the these are referencing the CPU Load and the Volume Usage Tables. You can get the Report to display for CPU or Volume, so I took the entire SQL Statement and broke it out into 2 usable Statements and they load in less than 1 second each. This does not include any Custom Property filters.

               

              Last 30 days Average CPU Usage Report. Average based on CPU Load of the day.

               

              Select N.NodeID, N.Caption, N.IP_Address, N.VendorIcon, AVG(CPU.AVGLoad) AS AVERAGE_of_CPULoad, Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate from Nodes N left join CPULoad CPU on N.NodeID=CPU.NodeID

              WHERE (DateTime>DATEADD(month,-1,GETDATE()))

              GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0), N.NodeID, N.Caption, N.IP_Address, N.VendorIcon order by SummaryDate desc, N.NodeID asc

               

               

              Last 30 days Average Volume Usage Report. Reports Average Disk Size, Disk Space Used, Max Disk Space Used.

               

              Select V.VolumeID, V.Caption, V.VolumeTypeIcon, AVG(VU.DiskSize) AS AVERAGE_of_Disk_Size, AVG(VU.AvgDiskUsed) AS AVERAGE_of_AvgDiskUsed, MAX(VU.MaxDiskUsed) AS MAX_of_MaxDiskUsed, Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate from Volumes V join VolumeUsage VU on V.VolumeID=VU.VolumeID

              WHERE (DateTime>DATEADD(month,-1,GETDATE()))

              GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0), V.VolumeID, V.Caption, V.VolumeTypeIcon order by SummaryDate desc, V.VolumeID asc

              • Re: what wrong in this statement
                njoylif

                If you need to have it in the same report, try specifying a table for datetime...like cpu.datetime (i did not review code in detail, but ambiguous column name indicates multiple tables have same column name when doing join).

                Nice work/research Sean!