Kaseya Community

DYO : Create your Own datasets from some tables

  • Hi everyone !
    I've found a way to create some new Datasets on the Reporting Service. I've created this part for AppAssure 5 Module.

    Create View

    First you'll need to create a View in your Kserver : 

    [CODE] Create view vReplay5Repository as
    Select
    v.agentguid,
    v.fullName,
    r5r.name,
    CAST((ISNULL(SUM(CAST((r5r.size)-(r5r.free) AS Numeric(30,4))),0)/1024/1024/1024) AS NUMERIC(15,2)) as Storage,
    CAST((ISNULL(SUM(CAST((r5r.size) AS Numeric(30,4))),0)/1024/1024/1024) AS NUMERIC(15,2)) as "Size",
    CAST((ISNULL(SUM(CAST(r5r.free AS Numeric(30,4))),0)/1024/1024/1024) AS NUMERIC(15,2)) as Free
    from replay5Machines v
    RIGHT JOIN replay5Repositories r5r on r5r.replayMachineId = v.id
    Group by
    v.fullName,
    r5r.name,
    v.agentguid
    [/CODE]

    Then verify that's the view is well created with this simple query
    Select * From vReplay5Repository

    Create the Category Registration File

    Create a xml file in yourdrive:\Kaseya\xml\Reporting\CategoryRegistration\ with the following node :
    [CODE] "<?xml version="1.0" encoding="utf-8" ?> <StandardCategoryDefinition CategoryId="200" Description="AppAssure 5" funcListId="16101" SortOrder="1" ModuleId="0" xmlns="http://www.kaseya.com/vsa/2010/05/Reporting" />" [/CODE]

    Create The Datasets Files

    Create xml file in yourdrive:\Kaseya\xml\Reporting\DataSetRegistration\1\AppAssure\ with the following nodes: [CODE] <?xml version="1.0" encoding="UTF-8"?> <DataSetDefinition DateFilterColumn="" RegistrationId="90004" DataSetTypeId="0" name="AppAssure Agent" Description="AppAssure Agent" funcListId="16102" CategoryId="200" isDisabled="false" ContextName="MachineFilter"> <DBDataSetDefinition> <ConnectionSource>KaseyaDataSource</ConnectionSource> <Columns> <Column RegistrationId="90004011" fullyQualifiedName="v.AgentGuid" datatype="INT32" length="" caption="AgentID" description="AgentID" Grouping="General" allowAdvancedFiltering="true" hidden="false" weight="10" format="" /> <Column RegistrationId="90004012" fullyQualifiedName="v.id" datatype="INT32" length="" caption="Agent Replay ID" description="AgentID Replay ID" Grouping="General" allowAdvancedFiltering="true" hidden="false" weight="10" format="" /> <Column RegistrationId="90004013" fullyQualifiedName="v.MachineTypeId" datatype="INT32" length="" caption="MachineTypeId" description="MachineTypeId" Grouping="General" allowAdvancedFiltering="true" hidden="false" weight="10" format="" /> <Column RegistrationId="90004014" fullyQualifiedName="v.version" datatype="DECIMAL" length="" caption="Version" description="Version" Grouping="General" allowAdvancedFiltering="true" hidden="false" weight="10" format="" /> <Column RegistrationId="90004015" fullyQualifiedName="v.VersionDecimal" datatype="Decimal" length="" caption="Version Decimal" description="Version Decimal" Grouping="General" allowAdvancedFiltering="true" hidden="false" weight="10" format="" /> <Column RegistrationId="90004016" fullyQualifiedName="v.licenseId" datatype="DECIMAL" length="" caption="License ID" description="ID of the License" Grouping="General" allowAdvancedFiltering="true" hidden="false" weight="10" format="" /> <Column RegistrationId="90004017" fullyQualifiedName="v.MachineStatusId" datatype="INT32" length="" caption="MachineStatusId" description="MachineStatusId" Grouping="General" allowAdvancedFiltering="true" hidden="false" weight="10" format="" /> <Column RegistrationId="90004018" fullyQualifiedName="v.port" datatype="STRING" length="" caption="Port" description="Port" Grouping="General" allowAdvancedFiltering="true" hidden="false" weight="10" format="" /> <Column RegistrationId="90004019" fullyQualifiedName="v.ip" datatype="STRING" length="" caption="ip" description="ip" Grouping="General" allowAdvancedFiltering="true" hidden="false" weight="10" format="" /> <Column RegistrationId="90004020" fullyQualifiedName="v.computerName" datatype="STRING" length="" caption="computerName" description="computerName" Grouping="General" allowAdvancedFiltering="true" hidden="false" weight="10" format="" /> <Column RegistrationId="90004021" fullyQualifiedName="v.fullName" datatype="STRING" length="" caption="Full Name" description="Full Name" Grouping="General" allowAdvancedFiltering="true" hidden="false" weight="10" format="" /> <Column RegistrationId="90004022" fullyQualifiedName="v.replayAgentGuid" datatype="STRING" length="" caption="replayAgentGuid" description="replayAgentGuid" Grouping="General" allowAdvancedFiltering="true" hidden="false" weight="10" format="" /> <Column RegistrationId="90004023" fullyQualifiedName="v.MAchineTypeId" datatype="STRING" length="" caption="Machine Type" description="Machine Type ID (core = 1 agent = 2)" Grouping="General" allowAdvancedFiltering="true" hidden="false" weight="10" format="" /> </Columns> <Query> FROM replay5Machines v JOIN ReportCenter.MachineFilterList mfl ON mfl.AgentGuid = v.agentGuid </Query> <Filter> WHERE mfl.ReportSessionId=@ReportSessionId</Filter> </DBDataSetDefinition> </DataSetDefinition> [/CODE]

    Or like this one : [CODE]

    "<?xml version="1.0" encoding="UTF-8"?> <DataSetDefinition DateFilterColumn="" RegistrationId="90005" DataSetTypeId="0" name="AppAssure Repository" Description="AppAssure Repository" funcListId="16102" CategoryId="200" isDisabled="false" ContextName="MachineFilter"> <DBDataSetDefinition> <ConnectionSource>KaseyaDataSource</ConnectionSource> <Columns> <Column RegistrationId="90005011" fullyQualifiedName="v.AgentGuid" datatype="INT32" length="" caption="AgentID" description="AgentID (can be found at Agent info)" Grouping="General" allowAdvancedFiltering="true" hidden="false" weight="10" format="" /> <Column RegistrationId="90005012" fullyQualifiedName="v.version" datatype="DECIMAL" length="" caption="Version" description="v.Version" Grouping="General" allowAdvancedFiltering="true" hidden="false" weight="10" format="" /> <Column RegistrationId="90005013" fullyQualifiedName="v.name" datatype="STRING" length="" caption="Repository Name" description="Repository Name" Grouping="General" allowAdvancedFiltering="true" hidden="false" weight="10" format="" /> <Column RegistrationId="90005014" fullyQualifiedName="v.Size" datatype="DECIMAL" length="" caption="Repository Size" description="Repository Size" Grouping="General" allowAdvancedFiltering="true" hidden="false" weight="10" format="" /> <Column RegistrationId="90005015" fullyQualifiedName="v.Free" datatype="DECIMAL" length="" caption="Repository Free" description="Repository Free" Grouping="General" allowAdvancedFiltering="true" hidden="false" weight="10" format="" /> <Column RegistrationId="90005016" fullyQualifiedName="v.fullName" datatype="STRING" length="" caption="computerName" description="computerName" Grouping="General" allowAdvancedFiltering="true" hidden="false" weight="10" format="" /> <Column RegistrationId="90005017" fullyQualifiedName="v.Storage" datatype="DECIMAL" length="" caption="Repository Occuped" description="Repository Occuped" Grouping="General" allowAdvancedFiltering="true" hidden="false" weight="10" format="" /> </Columns> <Query> FROM vReplay5Repository v JOIN ReportCenter.MachineFilterList mfl ON mfl.AgentGuid = v.agentGuid </Query> <Filter> WHERE mfl.ReportSessionId=@ReportSessionId</Filter> </DBDataSetDefinition> </DataSetDefinition>" [/CODE]



    Format... (trying to make format here is really complicated !)
    [edited by: Etienne Deneuve at 1:06 PM (GMT -7) on Oct 14, 2013]
  • What are the chances that it might get overwritten or that it might cause compatibility issues later on?  

  • I've no more idea about that than you, but maybe or have more information about this...

    In my opinion as we add new file, they will not be overriden. I've contacted Chad multiple time about the reporting and he told me that's on the roadmap without any date of avaiblility, as I've found a way from myself, I wanted to share with other Kaseya Users.

    There's some kind of 'limitation' as you must create a view to have always a 'agentguid' to match with the machinefilter. With this solution I've been able to create a view for the machine who aren't in KES or KAV table and have a report of this.

    Maybe some other Kaseya power user could create nice datasets and share with everyone here. SAAS customers are excluded as you need to have access to the DB and to the Filesystem.

    If you put a bad datasets in the VSA, they don't register at all so I think there's no real danger for the DB... But as usal with DB manipulation don't forget to have a valid backup or better have a dev' test kserver with a similar configuration.

    I'm planning to put all my SSRS query into the new reporting service when I could have the time to create them.

    #Staytuned

  • I just want to confirm Etienne's comment.  

    Creating your own dataset is on the list and I am certain it will be completed within the next 2 release cycles.  If any of you attended the executives webinars this week you will have heard that we will be doing 3 releases a year.  Winter, Spring, and Fall.

  • I know this is an old post, but would mind sharing your experience/expertise with this?

    I've finally run into a reason to create my own dataset because of an old problem that I'm sure others have run into, the application reports can only show what has been installed and I needed to create a report that can list computers with no agent installed.

    I have read the help doc about this and googled as much as I could find but I have a few pieces that I can't figure out and I hope you could point me in the right direction. (FYI I'm still on V6.5 atm)

    With some effort I managed to figure out how to create the SQL query needed for this, SQL is not strong point but after a day of thinking about it I now have a working database view.

    The next step I created the XML file and this is the part that I'm struggling with, how do you get/generate a unique "RegistrationId"?

    I tried by create a throwaway "Name Value Parts" dataset to generate the "RegistrationId" and modified the XML to look like the other Dataset XML's that use Database views using my view but I simply can't get it to show up under the Report Parts.

    For what it is worth if I can get this to work I would be happy to share the dataset.

    Thanks

    HardKnoX

  • Nevermind it started working now just not sure about the ID's Smile

  • NoAVInstalledReportPart.zip

    Use this at your own risk.

    Okay this custom report part (see attached zip file) will allow you to show all agents with no AV installed. It contains an XML file and a Readme.txt

    The Database View query uses the Application Audit information from and filters for these exe files: ('SmcGui.exe', 'VisthAux.exe', 'avgui.exe', 'avgnt.exe', 'ekrn.exe', 'avp.exe', 'nod32krn.exe', 'Ntrtscan.exe', 'Rtvscan.exe', 'SAV32CLI.exe', 'SavMain.exe', 'odscanui.exe', 'InoRT.exe', 'Mcshield.exe', 'msseces.exe') located in path with "Program Files" in the name.

    You can add additional AV products to the list by locating the exe file that it most commonly uses across all versions and adding it to the SQL query used by the database view.

    Once you have followed the readme file instructions in the zip file use the "Change Reporting Config" button located under "System > Server Manager > Configure" to register it.

    If all went well you will see the report part;

  • I've moved away from the conventional Info Center and simply create my views in SQL and export these into Bright Gauge.

    You not only get a report that looks current, but it also does not involve editing these files every time you wish to do something new.

    :)

  • The noAVinstalled thing I made actually uses a Database view so to update it you would only need to update the View query and you can use it with Brightgauge too.