/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


How to add offending disk to existing alert

11/26/2018 444 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

Be the first to answer this question

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ