Kaseya Community

SQL Assist - Adding Agent Notes to Email but remove error if notes not found.

This question is answered

Hi all, I'm hoping some of our generous SQL guru's can give a hand here.  We have a variable built into Kaseya that will add notes to the email, however if there are no notes entered there is a error message instead.  I'd like to know if it is possible to return a default "No Notes" value or something similar instead when nothing is found.

Here is the view from SQL - dbo.vAdminNotesLog

SELECT     m.machName + '.' + m.groupName AS Machine_GroupID, m.agentGuid, n.sessionAdmin AS AdminLogin, n.eventTime, n.description AS NoteDesc
FROM         dbo.adminNotesLog AS n LEFT OUTER JOIN
                      dbo.machNameTab AS m ON n.agentGuid = m.agentGuid

Can I simply add this to the end?

IF NoteDesc IS NULL THEN 'No Notes' ELSE NoteDesc

Edit: Here is a sample error if there are no notes

Agent Notes:

Time: [Database tag (vAgentNotes.EventTime) - no value for machine (agent.machinegroup)]

Desc: [Database tag (vAgentNotes.NoteDesc) - no value for machine (agent.machinegroup)]



_
[edited by: MrJoshua at 4:14 PM (GMT -7) on Sep 23, 2014]
Verified Answer
  • Sorry I understand now what you are trying to do you want to update the dbo.vAdminNotesLog view.

    Might I suggest you create a new database view instead of updating the existing one as it might break something in the VSA and/or get overwritten in an update, especially since I'm converting the eventTime values from a date type to a string to allow for the 'N/A' default value.

    I changed some of the table column names in the first script I gave you, here is the updated version with the same column name as what you are using.

    SELECT m.machName + '.' + m.groupName AS Machine_GroupID,

    m.agentGuid,

    ISNULL((n.sessionAdmin),'N/A') AS AdminLogin,

    ISNULL((CONVERT(VARCHAR(20),n.eventTime,120)),'N/A') AS EventTime,  

    ISNULL((n.description),'No Notes') AS NoteDesc

    FROM dbo.machNameTab AS m

    LEFT OUTER JOIN dbo.adminNotesLog AS n ON n.agentGuid = m.agentGuid

    ORDER By m.agentGuid, n.eventTime

All Replies
  • I'm no expert but I have been playing around with SQL queries recently.

    Give this a try;

    SELECT     m.machName + '.' + m.groupName AS Machine_GroupID,
        m.agentGuid AS AgentGUID,
        ISNULL((n.sessionAdmin),'N/A') AS AdminLogin,
        ISNULL((CONVERT(VARCHAR(20),n.eventTime,120)),'N/A') AS LoginTime,  
        ISNULL((n.description),'No Notes') AS AgentNotes
    FROM dbo.machNameTab AS m
    LEFT OUTER JOIN dbo.adminNotesLog AS n ON n.agentGuid = m.agentGuid
    ORDER By m.agentGuid, n.eventTime

    I swapped the machineNameTab and the adminNotesLog because if you don't it will only show the rows with entries making the null entries values pointless.

    This website "www.w3schools.com/.../" is very handy for learning how to use SQL.



    updated
    [edited by: HardKnoX at 7:59 PM (GMT -7) on Sep 23, 2014]
  • Thanks HardKnoX.  I didn't work out but I'll see if I can tweak it from there, appreciate the input.

    Here's what came out.  Time:  [Invalid database tag (vAgentNotes.EventTime) - no such view/column]

    Desc:  [Invalid database tag (vAgentNotes.NoteDesc) - no such view/column]

  • Where are you running this from?

    The syntax will change depending on where you run it from.

  • I'm using the remote control disable email alert to test.  This is in the email, one is for the original view, the other for my copied view to test with.  The copied one has your script.

    Agent Notes:

    Time: <db-vAgentNotes.EventTime>

    Desc: <db-vAgentNotes.NoteDesc>

    Agent Notes Original:

    Time: <db-vAdminNotesLog.EventTime>

    Desc: <db-vAdminNotesLog.NoteDesc>

    Copied View table is dbo.vAgentNotes

  • Sorry I understand now what you are trying to do you want to update the dbo.vAdminNotesLog view.

    Might I suggest you create a new database view instead of updating the existing one as it might break something in the VSA and/or get overwritten in an update, especially since I'm converting the eventTime values from a date type to a string to allow for the 'N/A' default value.

    I changed some of the table column names in the first script I gave you, here is the updated version with the same column name as what you are using.

    SELECT m.machName + '.' + m.groupName AS Machine_GroupID,

    m.agentGuid,

    ISNULL((n.sessionAdmin),'N/A') AS AdminLogin,

    ISNULL((CONVERT(VARCHAR(20),n.eventTime,120)),'N/A') AS EventTime,  

    ISNULL((n.description),'No Notes') AS NoteDesc

    FROM dbo.machNameTab AS m

    LEFT OUTER JOIN dbo.adminNotesLog AS n ON n.agentGuid = m.agentGuid

    ORDER By m.agentGuid, n.eventTime

  • Perfect!  Thanks for all the help, its exactly what I needed.