K1000 Report Creation Help
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.
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
@rn1 := if(@pk1=ID, if(@val=CREATED, @rn1, @rn1+1),1) as myRank,
@pk1 := ID,
@val := CREATED
WHERE CREATED <= NOW()
AND CREATED > DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY ID, CREATED desc
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.
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