Kaseya Community

Missing KES Report - Machines without KES

  • During our monthly KES audits my guys find themselves manually checking for machines that didn't have KES installed - which at times takes hours.  This prompted me to throw together a quick ASP page that pulls a list of all machines that according to the Kaseya database do not have KES properly installed.


    <%
    'Database Configuration
    DatabaseServer          = "localhost"
    DatabaseName            = "ksubscribers"
    DatabaseUsername        = "sa"
    DatabasePassword        = "EnterSAPasswordHere"

    'Database Connection
    DSNName = "PROVIDER=SQLOLEDB;DRIVER={SQL Server};SERVER=" & DatabaseServer & ";DATABASE=" & DatabaseName & ";UID=" & DatabaseUsername & ";PWD=" & DatabasePassword & ";"
    Set dbConn = CreateObject("ADODB.Connection")
    dbConn.Open DSNName
    %>
    <html>
    <head>
    <title>Machines with KES Issues</title>
    </head>
    <body>
    <table border="1" cellspacing="0" cellpadding="3">
    <tr bgcolor="#EAF1DD">
    <td>Machine Name</td>
    <td>Group Name</td>
    <td>OS Type</td>
    </tr>
    <%
    SQL = "SELECT [machName], [groupName], [OsInfo], [OsType] FROM [ksubscribers].[dbo].[vMachine] WHERE (groupName Not Like '%.template' And groupName <> 'root.kserver') AND agentGuid NOT IN (Select agentGuid from AVFeature WHERE InstallStatus = 1) ORDER By groupName"
    Set rsConn = dbConn.Execute(SQL)
    Do Until rsConn.BOF Or rsConn.EOF
       
        tmpMachine    = rsConn("machName")
        tmpGroup        = rsConn("groupName")
        tmpOsInfo        = rsConn("OsInfo")
        tmpOsType        = rsConn("OsType")
       
        If tmpGroup = "" Then tmpGroup = "&nbsp;"
        If tmpOsInfo = "" Then tmpOsInfo = "&nbsp;"
        If tmpOsType = "" Then tmpOsType = "&nbsp;"
       
        Response.Write("<tr>")
        Response.Write("<td>" & tmpMachine & "</td>")
        Response.Write("<td>" & tmpGroup & "</td>")
        Response.Write("<td><span title=""" & tmpOsInfo & """>" & tmpOsType & "</span></td>")
        Response.Write("</tr>")   
       
        Counter = Counter + 1
       
        rsConn.MoveNext
       
    Loop
    rsConn.Close
    Set rsConn = Nothing
    %>
    <tr>
    <td>Total: <%= Counter %></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    </tr>
    </table>
    </body>
    </html>


    (for the life of me I cannot find the forum tag to make the code section stand out...)

    Getting the script to work (the simple way)

    1. Copy/Paste the code above to a file named something like kes-missing-report.asp.  Since I wrote this in an outdated language that I love the extension must be .asp.
    2. Change the SQL password for the variable DatabasePassword.  This should be your SQL Server's SA password.
    3. This the edited file on the Kaseya server at c:\kaseya\webpages\.  This is the default directory where Kaseya is installed, if you modified this during installation you'll need to adjust.

    Ignoring Certain Group Names

    If you examine the SQL code used you see the following section:

    (groupName Not Like '%.template' And groupName <> 'root.kserver')

    This section basically instructs SQL to ignore any group that ends with ".template" or the group "root.kserver" as I didn't want to display the Kaseya server or templates that have never checked in.  Feel free to adjust this for your environment.

    That's about it.  Good luck!

    Oh, as always I didn't proof this.  Sorry.

  • One last thing - please watch for line wraps when using copy and paste.  The DSNName and SQL lines appear to have wrapped.

  • Josh i was just looking for a report that does this. Now that they released the new Reporting is there a more simple way to create this report

  • GOT IT!!!

    1. Create two custom fields (KES Status & KAM Status)

    2. Create a script that test for existence of file C:\Program Files\AVG\AVG9\avgui.exe (or program files x86). If anyone wants this script let me know and ill post.

    3. Schedule this script to run on regular basis

    4. Create reports based on these custom fields.

  • So if you are testing for the existence of avgui.exe, wouldn't using a view for machines missing application avgui.exe have the same result? Latest audits should be recent for this to be accurate. That's what I currently use, although it isn't perfect.

    How is your strategy any different? Just wondering, as this is a reoccuring task for us as well.

  • Yes that view would work (given that .exe is running).

    I wanted to create a report (in info center) that can be email to me on a monthly basis. I can also create pie charts that go on our executive report.

    Your way works too.