Sql Question
I've created a report that will query the scripting logs on the computers, and output the machine name, the time the script was run and the status logs from the script. I'm having a hard time filtering this query, 1 - so that it will display only the most recent log per machine (right now it displays all of the logs for the script name "McAfee Add-in Check"), and 2 - filter by what is in the status field. I've tried using Max() and "Limit 1" - but I don't think I'm using them correctly, can anyone help?
Select
MACHINE.NAME,
KBOT_LOG.STOP_TIME,
KBOT_LOG_DETAIL.TEXT
From
MACHINE Left Join
KBOT_LOG On MACHINE.ID = KBOT_LOG.MACHINE_ID Left Join
KBOT On KBOT.ID = KBOT_LOG.KBOT_ID Inner Join
KBOT_LOG_DETAIL On KBOT_LOG.STATUS_DETAIL_ID = KBOT_LOG_DETAIL.ID
Where
KBOT.NAME Like 'MCAFEE ADD-IN CHECK'
Order By
MACHINE.NAME
EDIT:
Here is the actual code I was able to use. To modify just change "ScriptName" and "ScriptStatus" below.
SELECT
MACHINE.NAME,
KBOT_LOG.STOP_TIME,
KBOT_LOG_DETAIL.TEXT,
KBOT.NAME
FROM
ORG1.KBOT_LOG_LATEST
LEFT JOIN MACHINE on KBOT_LOG_LATEST.MACHINE_ID = MACHINE.ID
LEFT JOIN KBOT_LOG on KBOT_LOG_LATEST.KBOT_LOG_ID = KBOT_LOG.ID
LEFT JOIN KBOT on KBOT_LOG_LATEST.KBOT_ID = KBOT.ID
LEFT JOIN KBOT_LOG_DETAIL on KBOT_LOG.STATUS_DETAIL_ID = KBOT_LOG_DETAIL.ID
WHERE
KBOT.NAME LIKE 'ScriptName' AND
KBOT_LOG_DETAIL.TEXT LIKE 'ScriptStatus'
Answers (2)
Add something like this to you WHERE. This will return the logs from the past 5 days. Change to what you need.
AND KBOT_LOG.STOP_TIME > DATE_SUB(NOW(), INTERVAL 5 DAY)
Comments:
-
The problem with this, and I thought about doing it this way, is that if a computer is offline and the script is not run on the next schedule, the computer is dropped from the report.
Some of my computers check-in daily and some only monthly, I really need the most recent log regarless of date. - baist111 10 years ago-
Got ya. I misunderstood. I'll take a look. Would looking for a "failed" status be better? - dugullett 10 years ago
-
Not a problem... I appreciate your help.
There are actually 2 failures in my script, expected failure and unexpected failure. After filtering for the latest log, I need to search the detailed text for the unexpected failures. If I filter first for the unexpected failures, I don't necessarily see the most recent log that the problem is fixed.
I hope this helps. - baist111 10 years ago-
Try this. Sorry I modified your SQL some.
Select DISTINCT
M.NAME,
MAX(KL.STOP_TIME) AS TIME,
KLD.TEXT
From MACHINE M
Left Join KBOT_LOG KL On M.ID = KL.MACHINE_ID
Left Join KBOT KB On KB.ID = KL.KBOT_ID
Inner Join KBOT_LOG_DETAIL KLD On KL.STATUS_DETAIL_ID = KLD.ID
Where KB.NAME = 'MCAFEE ADD-IN CHECK'
GROUP BY M.NAME
Order By M.NAME - dugullett 10 years ago
-
That's what I want, but it's not the correct data. The values in the KLD.TEXT fields do not line up with the values in the TIME fields. The TIME value is the most recent value by date, but the KLD.TEXT is the oldest value by date. - baist111 10 years ago
If you're concerned with the latest entry why not start with KBOT_LOG_LATEST:
SELECT MACHINE.NAME,
KBOT_LOG.STOP_TIME,
KBOT_LOG_DETAIL.TEXT,
KBOT.NAME
FROM ORG1.KBOT_LOG_LATEST
LEFT JOIN MACHINE on KBOT_LOG_LATEST.MACHINE_ID = MACHINE.ID
LEFT JOIN KBOT_LOG on KBOT_LOG_LATEST.KBOT_LOG_ID = KBOT_LOG.ID
LEFT JOIN KBOT_LOG_DETAIL on KBOT_LOG_LATEST.KBOT_LOG_ID = KBOT_LOG_DETAIL.ID
LEFT JOIN KBOT on KBOT_LOG_LATEST.KBOT_ID = KBOT.ID
WHERE KBOT.NAME LIKE 'MCAFEE ADD-IN CHECK';
If this basic query works then you can add an AND KBOT_LOG_DETAIL.TEXT = 'whatever' to filter on the status.
Comments:
-
The Kbot_log_detail ID doesn't match up with anything in the kbot_log_latest table.
The kbot_log_detail ID has a relationship with the kbot_log status_detail_id. - baist111 10 years ago-
Ah, I missed that. Change the join for the KBOT_LOG_DETAIL table to the following:
LEFT JOIN KBOT_LOG_DETAIL on KBOT_LOG.STATUS_DETAIL_ID = KBOT_LOG_DETAIL.ID
and move it after the join to KBOT_LOG. - chucksteel 10 years ago-
That was it - thanks so much. - baist111 10 years ago
