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
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
2 Comments
[ + ] Show comments
Answers (2)
Please log in to answer
Posted by:
JasonEgg
7 years ago
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
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
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/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