/build/static/layout/Breadcrumb_cap_w.png
11/26/2018 107 views

Hello,


I have an alert for Servers that have a disk with disk usage over 95%.  However it does not show which disk is the culprit.  I can get i t to show all the drives on that Server but how do I get it to show only the offending disk?  The SQL Query is below:



select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,

                       UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS

                  from ORG1.MACHINE 

                  LEFT JOIN KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1

                 where (((  OS_NAME like '%Server%') AND (1  in (select 1 from ORG1.MACHINE_DISKS where MACHINE.ID = MACHINE_DISKS.ID and MACHINE_DISKS.PERCENT_USED > '95')) ))


2 Comments   [ + ] Show comments

Comments

  • select MACHINE.NAME, D.NAME AS DISK_NAME from ORG1.MACHINE
    LEFT JOIN KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1 LEFT JOIN ORG1.MACHINE_DISKS D ON MACHINE.ID=D.ID
    where (( OS_NAME like '%Server%') AND D.PERCENT_USED > '95')
    • That works perfectly in a report but when I paste that into the alert I don't get the same result. The computer Name gets populated but all the other fields are blank and the Disk ID does not show up at all. I changed the threshold to 90% to get more servers and disks in the report but all the other text is the same. There must be some difference in the way it handles the query for a report and an alert. Sorry but my SQL is pitiful. The original Alert outputs this:

      select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
      UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS
      from ORG1.MACHINE
      LEFT JOIN KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1
      where ((( OS_NAME like '%Server%') AND (1 in (select 1 from ORG1.MACHINE_DISKS where MACHINE.ID = MACHINE_DISKS.ID and MACHINE_DISKS.PERCENT_USED > '95')) ))


      Computer Name System Description MAC Address IP Address
      CVMAPROD1 empty xx:xx:xx:xx:xx:xx4 nn.nnn.n.nnn



      select MACHINE.NAME, D.NAME AS DISK_NAME from ORG1.MACHINE
      LEFT JOIN KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1 LEFT JOIN ORG1.MACHINE_DISKS D ON MACHINE.ID=D.ID
      where (( OS_NAME like '%Server%') AND D.PERCENT_USED > '90')


      Computer Name System Description MAC Address IP Address
      FSISRV

      WSDEV1

      LISLESRV1

      VCMSRV

      WSTEST1

      CVMAPROD1

      CVMAPROD1
  • This content is currently hidden from public view.
    Reason: Removed by member request For more information, visit our FAQ's.
  • OK I made some progress but still don't show the offending Disk ID's in the table. Code used for alert is:

    select *, UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(LAST_SYNC) as LAST_SYNC_TIME,
    UNIX_TIMESTAMP(MACHINE.LAST_SYNC) as LAST_SYNC_SECONDS,
    MACHINE.NAME, D.NAME as DISK_NAME from ORG1.MACHINE
    LEFT JOIN KBSYS.KUID_ORGANIZATION ON KUID_ORGANIZATION.KUID=MACHINE.KUID LEFT JOIN KBSYS.SMMP_CONNECTION ON SMMP_CONNECTION.KUID = MACHINE.KUID AND KUID_ORGANIZATION.ORGANIZATION_ID = 1 LEFT JOIN ORG1.MACHINE_DISKS D ON MACHINE.ID=D.ID
    where (( OS_NAME like '%Server%') AND D.PERCENT_USED > '90')

    Result is:
    Computer Name System Description MAC Address IP Address
    FSISRV empty xx:xx:xx:xx:xx:xx nn.nnn.n.nnn
    WSDEV1 empty xx:xx:xx:xx:xx:xx nn.nnn.n.nnn
    LISLESRV1 empty xx:xx:xx:xx:xx:xx nn.nnn.n.nnn
    VCMSRV empty xx:xx:xx:xx:xx:xx nn.nnn.n.nnn
    WSTEST1 empty xx:xx:xx:xx:xx:xx nn.nnn.n.nnn
    CVMAPROD1 empty xx:xx:xx:xx:xx:xx nn.nnn.n.nnn
    CVMAPROD1 empty xx:xx:xx:xx:xx:xx nn.nnn.n.nnn

There are no answers at this time