/build/static/layout/Breadcrumb_cap_w.png

K1000 Report Creation Help

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
Asked 7 years ago 1202 views
2 Comments   [ + ] Show 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. - JasonEgg 7 years ago
    • 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 - asantia 7 years ago
  • 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 - asantia 7 years ago

Answers (2)

Posted by: JasonEgg 7 years ago
Red Belt
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

Comments:
  • 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") - asantia 7 years ago
    • 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. - JasonEgg 7 years ago
    • 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 - JasonEgg 7 years ago
Posted by: JasonEgg 7 years ago
Red Belt
-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

Comments:
  • 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. - asantia 7 years ago
 
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