We'd like to do some simple SSRS reports on what images and clones are available/in use so other members of IT can see usage. I am able to pull data from the SQLCLone_Config database but am not able to piece it all together as target instances for clones doesn't seem to be recorded in the database along with some other details.

so the questions are..
will you be supporting the ability to put together reports outside SQL Clone's console for us to use?
Is there a way to get the target instance name from the config database to show where Clones have been created?



  • Options
    We don't currently have a built-in reporting function, but thanks for the feedback - we will keep that in mind when planning future work.

    We don't commit to a stable schema in our internal configuration database, but would using PowerShell to query the clones on each instance work for you? A script like this would print out a list of clones on each instance that SQL Clone knows about:
    Connect-SqlClone -ServerUrl 'http://sql-clone.example.com:14145'
    $Instances = Get-SqlCloneSqlServerInstance
    foreach ($Instance in $Instances)
        $ServerInstance = $Instance.Server + '\' + $Instance.Instance
        Write-Host "Clones on $ServerInstance" -Foreground Green
        Get-SqlClone -Location $Instance

    If that doesn't work for you, let us know and we can explore it some more.
  • Options
    rpriesingrpriesing Posts: 12 Bronze 1
    That's a little cumbersome for daily reporting. We would only want to report on machines that have clones and want to tie the clone back to the image name as well. A tablix would be best as shown in attached image.

  • Options
    This would give you pretty much what you are looking for:

    sl.Server AS ComputerName
    ,c.Name AS CloneName
    ,i.FriendlyName AS ImageName
    ,CAST(RIGHT(i.FriendlyName, 19) AS DATE) AS ImageDate
    FROM dbo.Clones AS c
    INNER JOIN dbo.Images AS i
    ON c.ParentImageId = i.Id
    INNER JOIN dbo.SourceLocations AS sl
    ON c.LocationId = sl.Id
    WHERE c.DeletedOn IS NULL
    ORDER BY sl.Server
  • Options
    Awesome, Thanks. I was missing that join info.
  • Options
    I suspect you ignored this ",CAST(RIGHT(i.FriendlyName, 19) AS DATE) AS ImageDate" :-)
    We name our images on a special format and include the date in the image name
  • Options
    I caught the CAST and removed it. Unfortunately the Clone information does not work for named instances as the table only holds machine name. We'll have to try to find a work around for it but it's a great start. Thanks.
  • Options
    You could use the Powershell script above, augmented to write the data into the table of your choice in your SQL Server instance. Just a thought...
  • Options
    I thik you can join to SourceLocation from Clones on Clones.LocationId = SourceLocation.Id to get the instance name.
    But I would recommend to use Powershell instead.
Sign In or Register to comment.