Reporting

rpriesing

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

ChrisHurley

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.

rpriesing

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.

Mattias_Canberger

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

rpriesing

Awesome, Thanks. I was missing that join info.

Mattias_Canberger

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

rpriesing

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.

CraigEddy

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

Mattias_Canberger

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.