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

Second try, using a different method:

set @pk1 ='';
set @rn1 =1;
set @val ='';
SELECT  MACHINE.NAME AS SYSTEM_NAME, RANKED.SEVERITY, MACHINE.USER_NAME, CHASSIS_TYPE, CSP_ID_NUMBER, CS_MODEL, RANKED.CREATED, RANKED.CATEGORY, RANKED.DESCRIPTION
FROM
(
  SELECT  SEVERITY,ID,CREATED,CATEGORY,DESCRIPTION,
          @rn1 := if(@pk1=ID, if(@val=CREATED, @rn1, @rn1+1),1) as myRank,
          @pk1 := ID,
          @val := CREATED   
  FROM
  (
    SELECT  SEVERITY,ID,CREATED,CATEGORY,DESCRIPTION
    FROM    MACHINE_DCM_LOG_ENTRY
    WHERE CREATED <= NOW() 
               AND CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)
    ORDER BY ID, CREATED desc
) A
) RANKED
JOIN MACHINE on RANKED.ID = MACHINE.ID
WHERE myRank <= 5

I'm using my HD_TICKET_CHANGE table to test this by retrieving the last 5 updates to a ticket. So the general form of the query is working for me at least. If there's something about the DCM table I'm missing, then I won't be able to troubleshoot since my table is empty.

Adapted from: http://www.folkstalk.com/2013/03/grouped-rank-function-mysql-sql-query.html

Answered 03/31/2017 by: JasonEgg
Red Belt

  • I'm getting this error when I try to save your SQL as a report:

    mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'set @rn1 =1; set @val =''; SELECT MACHINE.NAME AS SYSTEM_NAME, RANKED.SEVERIT' at line 2] in EXECUTE( "set @pk1 =''; set @rn1 =1; set @val =''; SELECT MACHINE.NAME AS SYSTEM_NAME, RANKED.SEVERITY, MACHINE.USER_NAME, CHASSIS_TYPE, CSP_ID_NUMBER, CS_MODEL, RANKED.CREATED, RANKED.CATEGORY, RANKED.DESCRIPTION FROM ( SELECT SEVERITY,ID,CREATED,CATEGORY,DESCRIPTION, @rn1 := if(@pk1=ID, if(@val=CREATED, @rn1, @rn1+1),1) as myRank, @pk1 := ID, @val := CREATED FROM ( SELECT SEVERITY,ID,CREATED,CATEGORY,DESCRIPTION FROM MACHINE_DCM_LOG_ENTRY WHERE CREATED <= NOW() AND CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY) ORDER BY ID, CREATED desc ) A ) RANKED JOIN MACHINE on RANKED.ID = MACHINE.ID WHERE myRank <= 5 LIMIT 0")
    • Well apparently you can't use variables in reports. There's probably a way to do it without variables but I am not sure how. If I get a second maybe I'll try again tomorrow.
    • Maybe the Reporting tool won't generate it, but it still runs in Toad for me. So, worst case, you can manually run the query. http://www.toadworld.com/m/freeware/1469
Please log in to comment
-1
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
Red Belt

  • Running the SQL posted only returns one line entry total, when their should be multiple computers in the list.

    If I remove "HAVING COUNT(*) <= 5" I get all the computers that should be there, but only the first logged error instead of the most recent five.

    Any ideas what may need tweaked?

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