Jump to content

SQL Snippets

5 files

  1. Agent Status and User Idle Time Dataview

    This Dataview is basically the same as the "Computer Status" Dataview, but I have added a column for Agent Idle Time. I find it helpful when I need to see quickly which users are on their systems, and which machines are not being used or have long idle times so that I can work without disrupting an active user. I have added another column, `Agent Additional Users`. This shows any other logins reported by Automate, such as on a Terminal Server.  For only a couple of columns difference, I have found it to be a very useful dataview and refer to it often.
    To import, just extract the .sql file from the zip. In Control Center select Tools->Import->SQL File. It will ask if you want to import 3 statements. This is normal.
    When finished, select Tools->Reload Cache.
     

    406 downloads

       (3 reviews)

    4 comments

    Submitted

  2. Add Agent DNS Servers field to Dataviews

    The attached SQL will modify all dataviews that include the computer's local IP (Agent IP Address) to also include the DNS server information as "Agent DNS Servers". I started out planning to create a specific dataview, but I think it is better to make this column available in any dataview.  I suggest copying one or a few of the stock Dataviews, and then running this. While it will update all dataviews, many will be flagged as having changes the next time Solution Center is ran and may be reset to original. The copied Dataviews should preserve the additional column.
    Save the file and import into Control Center using Tools -> Import -> SQL File. It will ask if you want to "Import 1 Statement into the database". If it says there are many statements choose "No", you have an older (buggy) Control Center and you will need to import using SQLYog or some other tool.

    441 downloads

       (4 reviews)

    1 comment

    Submitted

  3. Add Ticket ExternalID to Dataviews

    The attached SQL will modify all dataviews that include a ticketid to also include the external ticketid as "Ticket External ID". I suggest copying one or a few of the stock Dataviews, and then running this. While it will update all dataviews with ticket information, many will be flagged as having changes the next time Solution Center is ran and may be reset to original. The copied Dataviews should preserve the additional column.
    Save the file and import into Control Center using Tools -> Import -> SQL File. It will ask if you want to "Import 1 Statement into the database". If it says there are many statements choose "No", you have an older (buggy) Control Center and you will need to import using SQLYog or some other tool.
    Keywords: remote monitor free drive drives disk disks volume volumes mounted mount point no letter space percent percentage

    26 downloads

       (0 reviews)

    1 comment

    Submitted

  4. Computer Audit Dataview

    This Dataview incorporates some data from the Disk Inventory Dataview and the Computer Chassis Dataview, including Video Card, Memory, and CPU columns.. I created it when a client asked for assistance gathering this information. The first time we exported from multiple Dataviews and merged the columns in Excel. The next time they asked I made this so that 1 Dataview had all the columns that were important in this instance. Perhaps you would like these columns in a single Dataview also. 
    The SQL is safe to import through Control Center->Tools->Import SQL. The Dataview should be added into the Inventory folder.

    349 downloads

       (0 reviews)

    1 comment

    Submitted

  5. CUSTOM Bad Processes Detected Internal Monitor

    UPDATE - 20190411 - The stock "PROC - Bad Processes Detected" has been improved. I don't know specifically when, but when looking on an up to date system running 2019 Patch3, the results match this monitor's results. It now actually matches the exe name (not just the process name). I'll leave this post if someone would like this for an old system, but if your system is current this monitor will not improve the results and I would suggest staying with the stock monitor.
    I got tired of the useless/wrong BAD PROCESS tickets created by the stock "PROC - Bad Processes Detected" monitor, such as the "Reg.hta" classification alerting when Reg.exe is found running.
    This SQL will clone the stock monitor (Or update itself if you run it again) targeting the same computer groups, and using the same alert settings. (If you have removed the stock monitor it will still create the new one but it will not target any specific groups). This monitor has been adjusted to only match when the process executable matches the Bad Process Executable name.  In a perfect world it would match based on the entire path, but the process classification table only holds the EXE name. Still, this should be much more accurate, much less noisy, and when you re-classify a process you are doing for the EXE name, not just the process title.
    This SQL only creates/updates the new monitor, so don't forget to disable the alerts assigned to the stock monitor or you will get tickets from both of them.

    86 downloads

       (0 reviews)

    0 comments

    Updated


×
×
  • Create New...