Reporting

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?

Thanks
Tagged:

Comments

  • 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.
  • 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.

    q10a6llalkk6.jpg
  • This would give you pretty much what you are looking for:

    SELECT
    sl.Server AS ComputerName
    ,c.Name AS CloneName
    ,c.CreatedBy
    ,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
    ,ImageDate
    ,c.Name
  • Awesome, Thanks. I was missing that join info.
  • 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
  • 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.
  • 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...
  • 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.