Hello,

I need some help customizing a report.

I want a report that lists any computers with "Dell Command | Monitor - Severity" NOT equal to "OK" within the last 7 days based on the "Dell Command | Monitor - Timestamp".
I also want the report to list the errors under each computer name, using the computer name as a sub-headder.
Lastly I only want it to show the 5 most recent errors logged.

I've illustrated what I currently have (KACE.png) and what I'm looking for (KACE-tobe.png) using Photoshop.

If anyone could help me write the report (using the wizard or even straight SQL) I'd appreciate it.

Thanks.

KACE.png:


KACE-tobe.png
OylBk2.png
2 Comments   [ + ] Show Comments

Comments

  • Can you paste the SQL of your current query? Also, the forum compresses images pretty small so you may want to link the images externally or write out the details.
    • https://image.ibb.co/fAMYov/KACE.png
      https://image.ibb.co/iFmNgF/KACE_tobe.png


      SELECT MACHINE.NAME AS SYSTEM_NAME, MACHINE_DCM_LOG_ENTRY.SEVERITY, MACHINE.USER_NAME, CHASSIS_TYPE, CSP_ID_NUMBER, CS_MODEL, MACHINE_DCM_LOG_ENTRY.CREATED, MACHINE_DCM_LOG_ENTRY.CATEGORY, MACHINE_DCM_LOG_ENTRY.DESCRIPTION FROM MACHINE LEFT JOIN MACHINE_DCM_LOG_ENTRY ON (MACHINE_DCM_LOG_ENTRY.ID = MACHINE.ID) WHERE (((TIMESTAMP(MACHINE_DCM_LOG_ENTRY.CREATED) <= NOW() AND TIMESTAMP(MACHINE_DCM_LOG_ENTRY.CREATED) > DATE_SUB(NOW(),INTERVAL 7 DAY))) AND (MACHINE_DCM_LOG_ENTRY.SEVERITY != 'OK')) ORDER BY SYSTEM_NAME
  • https://image.ibb.co/fAMYov/KACE.png
    https://image.ibb.co/iFmNgF/KACE_tobe.png


    SELECT MACHINE.NAME AS SYSTEM_NAME, MACHINE_DCM_LOG_ENTRY.SEVERITY, MACHINE.USER_NAME, CHASSIS_TYPE, CSP_ID_NUMBER, CS_MODEL, MACHINE_DCM_LOG_ENTRY.CREATED, MACHINE_DCM_LOG_ENTRY.CATEGORY, MACHINE_DCM_LOG_ENTRY.DESCRIPTION FROM MACHINE LEFT JOIN MACHINE_DCM_LOG_ENTRY ON (MACHINE_DCM_LOG_ENTRY.ID = MACHINE.ID) WHERE (((TIMESTAMP(MACHINE_DCM_LOG_ENTRY.CREATED) <= NOW() AND TIMESTAMP(MACHINE_DCM_LOG_ENTRY.CREATED) > DATE_SUB(NOW(),INTERVAL 7 DAY))) AND (MACHINE_DCM_LOG_ENTRY.SEVERITY != 'OK')) ORDER BY SYSTEM_NAME
Please log in to comment

Answers

0
Try the query below. I don't have any information in my "MACHINE_DCM_LOG_ENTRY" table, so I wasn't able to test this against my database (though a similar query worked with a different table).The whole "top 5" thing was very difficult to figure out but I think I have it. This query is in no way optimized so it could take awhile to run (took about 15 seconds when I was testing with "top 3" in a table with 27,000 entries).
SELECT MACHINE.NAME AS SYSTEM_NAME, TOP_5.SEVERITY, MACHINE.USER_NAME, CHASSIS_TYPE, CSP_ID_NUMBER, CS_MODEL, TOP_5.CREATED, TOP_5.CATEGORY, TOP_5.DESCRIPTION
FROM   (SELECT   a.*
        FROM     MACHINE_DCM_LOG_ENTRY AS a 
                  LEFT JOIN MACHINE_DCM_LOG_ENTRY AS a2 ON a.ID = a2.ID 
                   AND a.CREATED <= a2.CREATED
        WHERE  TIMESTAMP(a.CREATED) <= NOW() 
               AND TIMESTAMP(a.CREATED) > DATE_SUB(NOW(), INTERVAL 7 DAY)
               AND a.SEVERITY != 'OK'
               AND TIMESTAMP(a2.CREATED) <= NOW() 
               AND TIMESTAMP(a2.CREATED) > DATE_SUB(NOW(), INTERVAL 7 DAY)
               AND a2.SEVERITY != 'OK'
        GROUP BY a.ID
        HAVING   COUNT(*) <= 5
        ORDER BY a.ID, a.CREATED DESC) TOP_5
JOIN MACHINE on TOP_5.ID = MACHINE.ID

Answered 03/20/2017 by: JasonEgg
Tenth Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity