Kaseya Community

Getting source server from the Kserver or agent

  • Is there a way to locate the source server via a script - it's set in file source for patching but unable to locate a way to pull it into a script.

    Cheers
    Bodie

    Legacy Forum Name: Getting source server from the Kserver or agent,
    Legacy Posted By Username: Bodie
  • Apparently you can create a database view to pull this information from the SQL database. I'm not sure how to do this, someone has just posted this as a response for a suggestion in the VSA Suggestion forum, check it out.

    Legacy Forum Name: Scripts Forum,
    Legacy Posted By Username: LANWorx
  • You need to mess with the actual SQL database in order to create the view. Can't be too difficult.



    http://community.kaseya.com/xsp/f/28/t/5812.aspx

    http://community.kaseya.com/xsp/f/28/t/5806.aspx

    Legacy Forum Name: Scripts Forum,
    Legacy Posted By Username: CeruleanBlue
  • In SQL Server -> ksubscribers database -> Views

    Create a new view with the following SQL. The parameters for where the file source is located is cover in various fields. So you'll want to adjust the view to combine these into a common field or retrieve them all and then combine them after you make them script variables. If your file source is set to

    SELECT dbo.machNameTab.machName, dbo.patchParams.sourceType, dbo.patchParams.sourcePath, dbo.patchParams.sourceLocal,
    dbo.patchParams.sourceMachineId
    FROM dbo.machNameTab INNER JOIN
    dbo.patchParams ON dbo.machNameTab.agentGuid = dbo.patchParams.agentGuid

    Once you create the view you need to select the view and change the permissions to grant Select Permissions for the KaseyaReporting user.

    you can then access the above variables using vMyView (or whatever you save the view as) and variables from above like "sourceType", "sourcePath", etc.

    Legacy Forum Name: Scripts Forum,
    Legacy Posted By Username: doug.jenkins@ispire.ca
  • Cheers People ... fantastic ..

    Legacy Forum Name: Scripts Forum,
    Legacy Posted By Username: Bodie
  • Have looked at all the great info here and have been able to create a view for the patch source info. In the Management Studio the view works fine ie it returns columns with the right info.

    Here is the SQL producing the view:
    SELECT dbo.machNameTab.machName, dbo.patchParams.sourceType, dbo.patchParams.sourcePath AS Source, dbo.patchParams.sourceLocal,
    dbo.patchParams.sourceMachineId
    FROM dbo.machNameTab INNER JOIN
    dbo.patchParams ON dbo.machNameTab.agentGuid = dbo.patchParams.agentGuid

    However if I try to write a query such as the following:
    select source from vITWESTview where (machine_groupid='peterv-pc.itwest.dubbo')
    I always get:
    Msg 208, Level 16, State 1, Line 1
    Invalid object name 'vITWESTview'.

    And when I run this simple script:
    Script Name: Test Patch Source
    Script Description:

    IF True
    THEN
    Get Variable
    Parameter 1 : 11
    Parameter 2 : vITWESTview/source
    Parameter 3 : ksource
    OS Type : 0
    Write Script Log Entry
    Parameter 1 : Source Path is: #ksource#
    OS Type : 0
    ELSE

    It errors with:
    FAILED in processing THEN step 1, Get Variable, with error SQL query failed to return data in GetVariable task, SELECT source FROM vITWESTview WHERE (Machine_GroupID='peterv-pc.itwest.dubbo')

    I have given the KaseyaReporting role Select permission to the view.

    Anyone can point me in the right direction?

    Legacy Forum Name: Scripts Forum,
    Legacy Posted By Username: petervane
  • You are looking for 'where machine_groupid=...' in your second query, but machine_groupID does is not included in the view.

    You need to utilize 'machinename' or 'sourceMachineID' as these exist in the query. Or moidfy the query to include a field called machine_groupid

    Legacy Forum Name: Scripts Forum,
    Legacy Posted By Username: doug.jenkins@ispire.ca
  • Doug

    Thanks so much for your input....I took your original sql as gospel and hadnt really looked at what was there. Modified as below and all is good. Once again really appreciate your help:

    SELECT dbo.machNameTab.machName + '.' + dbo.machNameTab.groupName AS Machine_GroupID, dbo.machNameTab.machName AS mach_name,
    dbo.patchParams.sourceType, dbo.patchParams.sourcePath AS Source, dbo.patchParams.sourceLocal, dbo.patchParams.sourceMachineId
    FROM dbo.machNameTab INNER JOIN
    dbo.patchParams ON dbo.machNameTab.agentGuid = dbo.patchParams.agentGuid

    Legacy Forum Name: Scripts Forum,
    Legacy Posted By Username: petervane