This article details how to export Target Devices from a Provisioning Services (PVS) database in a formatted Comma Separated Values (CSV) file.

Requirements

SQL Server Management Studio or bcp.exe, both of which are components of a full SQL Server or SQL Server Express install.

Instructions

1. Connect to the SQL Server hosting the Provisioning Server database.

2. Right-click on the Provisioning Server database and select Tasks > Export Data.

    

3. Ensure that the value in the Database field is correct, and select Next.

   

4. Set Destination as Flat File Destination and specify a path and file name (with .csv extension) to export. Ensure Column names in the first data row check box is not selected.

   

5. Select Write a query to specify the data to transfer option and click Next.

    

6. Specify the following SQL query to export all Devices in a farm, alphabetically by Site, Collection, and Device name:

   SELECT D.deviceName, D.deviceMac, S.siteName, C.collectionName

   FROM dbo.Device D, dbo.Collection C, dbo.Site S
   WHERE D.collectionID = C.collectionID AND C.siteID = S.siteID
   ORDER BY S.siteName, C.collectionName, D.deviceName

7. Click Next.

   

8. Use the default options and click Next > Next > Finish.

1. Open a Command Prompt on the SQL Server hosting the Provisioning Server database. See Additional Resources section for an MSDN article detailing advanced usage of bcp.exe, including executing the utility from a remote machine.

2. Specify the following command to export all Devices in a farm, alphabetically by Site, Collection, and Device name:

bcp.exe "SELECT D.deviceName, D.deviceMac, S.siteName, C.collectionName FROM PVSDEMO.dbo.Device D,
PVSDEMO.dbo.Collection C, PVSDEMO.dbo.Site S WHERE D.collectionID = C.collectionID AND C.siteID = 
S.siteID ORDER BY S.siteName, C.collectionName, D.deviceName" queryout "C:\ExportedDevices.csv" -c -t"," –T

where, PVSDEMO is the name of the PVS database on the SQL Server, and C:\ExportedDevices.csv is the path and file name where you want to export.

Citrix eDocs - Importing Target Devices into a Collection

Additional example queries (if using with BCP, ensure to prefix the tables listed in the FROM clause with the name of the database followed by a period as seen in the example):

• Export only the Devices that are members of the Collection Desktops:

SELECT D.deviceName, D.deviceMac, S.siteName, C.collectionName  
FROM dbo.Device D, dbo.Collection C, dbo.Site S
WHERE D.collectionID = C.collectionID AND C.siteID = S.siteID AND C.collectionName='Desktops'
ORDER BY S.siteName, C.collectionName, D.deviceName

• Export Devices from all Sites and Collections within a Farm whose name begins with XD:

SELECT D.deviceName, D.deviceMac, S.siteName, C.collectionName, 
FROM dbo.Device D, dbo.Collection C, dbo.Site S
WHERE D.collectionID = C.collectionID AND C.siteID = S.siteID AND D.deviceName LIKE ‘XD%’
ORDER BY S.siteName, C.collectionName, D.deviceName

• Export Devices from all Sites and Collections within a Farm that belong to a View named XSP:

SELECT D.deviceName, D.deviceMac, S.siteName, C.collectionName
FROM dbo.Device D, dbo.Collection C, dbo.Site S, dbo.SiteView V, dbo.SiteViewDevice Z
WHERE D.collectionID = C.collectionID AND C.siteID = S.siteID AND S.siteID = V.siteID AND V.siteViewID = Z.siteViewID AND D.deviceID = Z.deviceID AND V.siteViewName=’XSP’
ORDER BY S.siteName, C.collectionName, D.deviceName