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' 

 

 

 

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

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)

Answered 05/22/2013 by: dugullett
Red Belt

  • 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.
    • Got ya. I misunderstood. I'll take a look. Would looking for a "failed" status be better?
  • 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.
    • 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
  • 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.
Please log in to comment
0

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.

Answered 05/23/2013 by: chucksteel
Red Belt

  • 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.
    • 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.
      • That was it - thanks so much.
Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share