Get tape content via powershell

PowerShell script exchange

Re: Get tape content via powershell

Veeam Logoby winnt » Fri Oct 06, 2017 6:37 pm

My SQL query has an issue with a join returning extra information. bnieman, maybe your database team can fix my query, and in return they get the tape inventory they requested. I think the problem is properly matching up the media_sequence_number in Tape.Tape_mediums and Tape.File_parts. Through all my joins, I am getting fields linked that shouldn't be. I have this diagrammed in a color-coded Excel spreadsheet if you are up for the challenge. Thanks.
winnt
Influencer
 
Posts: 21
Liked: 9 times
Joined: Fri Apr 03, 2015 9:19 pm
Full Name: J D

Re: Get tape content via powershell

Veeam Logoby dbewernick » Thu Nov 23, 2017 7:46 pm

Thank you for the time you put into this. Did you achieve any further progress so far? Since Find-VBRTapeCatalog is not supported anymore the SQL solution seems to be a great idea. :wink:
dbewernick
Veeam Software
 
Posts: 86
Liked: 5 times
Joined: Wed Jul 20, 2016 8:02 am

Re: Get tape content via powershell

Veeam Logoby user77 » Fri Feb 02, 2018 11:42 am

Is anyone have a working experience how to get tape content via powershell or API?
user77
Lurker
 
Posts: 1
Liked: never
Joined: Fri Feb 02, 2018 11:39 am

Re: Get tape content via powershell

Veeam Logoby winnt » Thu May 03, 2018 9:07 pm 1 person likes this post

Well hello again everyone! I am back with an updated tape inventory script! I've spot-checked my results multiple times and they seem to be accurate. Hopefully we can finally get this question answered after almost four years of it being asked! :shock:

Here is the SQL query with the snippet of PowerShell code at the header if you wish to export to a CSV file, which I highly recommend.

Code: Select all
/*

File Name: VeeamTapeInventory.sql

This query will display a list of tapes sorted by barcode id, along with the file name/size, tape space used/free, and where the tape is located whether vaulted or in a library slot.

By winnt posted to Veeam forum on May 3, 2018

I have found this query most useful when exported to a CSV file from a PowerShell script such as this:

# ------- ExportVeeamTapeInventory.ps1 -------

# PowerShell script that runs an SQL query to inventory tapes within Veeam and then exports to a CSV file

# By winnt posted to Veeam forum on May 3, 2018

# ****************************************************************************************

# You will need to install the SQL Server PowerShell Module

# You need to change the Veeam backup server / SQL Server name/instance to match yours.

# You need to change the $ScriptDir and $ReportDir variables, or leave them as-is and make

# sure the paths exist for this script file, the SQL input file, and exported tape report.

# ****************************************************************************************

$VeeamSqlServer = 'WPBACKUP01\veeamsql2012'

$ScriptDir = 'C:\Scripts\Veeam'

$ReportDir = 'C:\Scripts\Veeam\Reports'

Add-PSSnapin SqlServerCmdletSnapin100

Add-PSSnapin SqlServerProviderSnapin100

$date = Get-Date -format "yyyyMMdd-HHmmss"

$TapeInventory = Invoke-Sqlcmd -InputFile $ScriptDir\VeeamTapeInventory.sql -ServerInstance "$VeeamSQLServer"

$TapeInventory | Export-Csv $ReportDir\VeeamTapeInventory_$Date.csv -NoTypeInformation

Write-Host "Tape inventory has been exported to: "

Write-Host "$ReportDir\VeeamTapeInventory_$Date.csv"

# ------- ExportVeeamTapeInventory.ps1 -------

*/




USE VeeamBackup;

    WITH PathInfo AS
    (
     SELECT  [Id]
    ,Parent_Id
    ,Name
    ,FolderPath = CONVERT(NVARCHAR(800), name)
       FROM [dbo].[Tape.directories]
      WHERE Parent_Id IS NULL
      UNION ALL
     SELECT  TempTD.Id
    ,TempTD.Parent_Id
    ,TempTD.name
    ,FolderPath = CONVERT(NVARCHAR(800), cte.FolderPath+'\'+TempTD.name)
       FROM [dbo].[Tape.directories] TempTD
       JOIN PathInfo cte ON cte.Id = TempTD.Parent_Id
    )

SELECT
TTM_Barcode AS BarcodeID,
TH_Name AS Backup_Server,
Folder_Path,
TF_Name AS File_Name,
TFP_Incompletion AS FileSegmentNumber,
File_Size_GB,
Tape_Capacity_GB,
Tape_Remaining_GB,
TTM_Protected AS IsTapeProtected,
CASE WHEN
Tape_Physical_Location IS NULL THEN 'Offline'
ELSE Tape_Physical_Location
END AS Tape_Physical_Location,
TB_Name AS Tape_Backup_Job,
TBS_Name AS Tape_Backup_Set,
TBS_ExpirationDate AS Tape_Backup_Set_Expiration,
TTM_LastWriteTime AS Last_Write_Time,
TTM_Description AS Tape_Description,
TMP_Name AS Tape_Media_Pool,
TMP_Description AS Tape_Media_Pool_Description

FROM
(SELECT TFV.file_id AS TFV_FileID,
TFV.backup_set_id AS TFV_BackupSetID,
TFV.id AS TFV_ID,
CAST(TFV.Size / 1073741824.0E AS DECIMAL(10, 2)) AS File_Size_GB,
TF.directory_id AS TF_DirectoryID,
TF.name AS TF_Name,
TFP.media_sequence_number AS TFP_MediaSequenceNumber,
TFP.id AS TFP_ID,
TFP.file_version_id AS TFP_FileVersionID,
TFP.incompletion AS TFP_Incompletion,
TH.name AS TH_Name,
PathInfo.folderpath AS Folder_Path
     FROM [Tape.file_versions] AS TFV
LEFT JOIN [dbo].[Tape.file_parts] TFP 
ON TFV.id = TFP.file_version_id
LEFT JOIN [Tape.files] TF
ON TFV.file_id = TF.id
LEFT JOIN [Tape.directories] TD
ON TF.directory_id = TD.id
LEFT JOIN [Tape.hosts] TH
ON TD.host_id = TH.id
INNER JOIN PathInfo
ON PathInfo.id = TD.id
) AS FileParts
  RIGHT JOIN
(SELECT TTM.id AS TTM_ID,
TTM.barcode as TTM_Barcode,
TTM.media_sequence_number AS TTM_MediaSequenceNumber,
TTM.location_address AS TTM_LocationAddress,
TTM.Last_Write_Time AS TTM_LastWriteTime,
TTM.Description AS TTM_Description,
CASE TTM.Protected
WHEN '0' THEN 'No'
WHEN '1' THEN 'Yes'
ELSE 'Other'
END AS TTM_Protected,
TTMBS.tape_medium_id AS TTMBS_TapeMediumID,
TTMBS.backup_set_id AS TTMBS_BackupSetID,
TBS.id AS TBS_ID,
TBS.name AS TBS_Name,
TBS.backup_id AS TBS_BackupID,
TBS.expiration_date AS TBS_ExpirationDate,
TB.name AS TB_Name,
TMV.description AS TMV_Description,
TMV.name AS TMV_Name,
CAST(TTM.Capacity / 1073741824.0E AS DECIMAL(10, 2)) AS Tape_Capacity_GB,
CAST(TTM.Remaining / 1073741824.0E AS DECIMAL(10, 2)) AS Tape_Remaining_GB,
TL.Name AS TL_Name,
TL.id AS TL_ID,
TL.tape_server_id AS TL_TapeServerID,
TTM.Location_type AS TTM_LocationType,
CASE TTM.Location_Type
WHEN '0' THEN TL.Name + ' - Tape Drive'
WHEN '1' THEN TL.Name + ' - Slot ' + CAST((TTM.Location_Address + 1) AS NVARCHAR(255))
WHEN '2' THEN 'Tape Vault - ' + TMV.Name
ELSE 'Other'
END AS Tape_Physical_Location,
TMP.name AS TMP_Name,
TMP.Description AS TMP_Description 
FROM [Tape.tape_mediums] AS TTM
LEFT JOIN [dbo].[Tape.tape_medium_backup_sets] TTMBS 
ON TTM.id = TTMBS.tape_medium_id
LEFT JOIN  [dbo].[Tape.backup_sets] TBS
ON TTMBS.backup_set_id = TBS.id
LEFT JOIN [Tape.backups] TB
ON TBS.backup_id = TB.id
LEFT JOIN [Tape.media_in_vaults] TMIV
ON TTM.id = TMIV.media_id
LEFT JOIN [Tape.media_vaults] TMV
ON TMIV.vault_id = TMV.id
LEFT JOIN [Tape.libraries] TL
ON TTM.location_library_id = TL.id
INNER JOIN [Tape.media_pools] TMP
ON media_pool_id = TMP.id
) AS BackupSets
ON BackupSets.TBS_ID = FileParts.TFV_BackupSetID
AND BackupSets.TTM_MediaSequenceNumber = FileParts.TFP_MediaSequenceNumber

WHERE NOT (NOT (BackupSets.TBS_ID IS NULL) AND (TF_Name IS NULL))

ORDER BY TTM_Barcode ASC





If anyone is good with macros, maybe they could script these steps in Excel?
1. Bold top row
2. Freeze top row (View, Freeze Panes, Freeze Top Row)
3. Enable data filter on all columns (highlight entire worksheet, Data, Filter)
4. Save as .xlsx

Thanks!
winnt
Influencer
 
Posts: 21
Liked: 9 times
Joined: Fri Apr 03, 2015 9:19 pm
Full Name: J D

Re: Get tape content via powershell

Veeam Logoby winnt » Fri May 04, 2018 2:50 pm 1 person likes this post

Here is the PowerShell script separated out.

Code: Select all
# ------- ExportVeeamTapeInventory.ps1 -------

# PowerShell script that runs an SQL query to inventory tapes within Veeam and then exports to a CSV file

# By winnt posted to Veeam forum on May 3, 2018

# ****************************************************************************************

# You will need to install the SQL Server PowerShell Module

# You need to change the Veeam backup server / SQL Server name/instance to match yours.

# You need to change the $ScriptDir and $ReportDir variables, or leave them as-is and make

# sure the paths exist for this script file, the SQL input file, and exported tape report.

# ****************************************************************************************

$VeeamSqlServer = 'WPBACKUP01\veeamsql2012'

$ScriptDir = 'C:\Scripts\Veeam'

$ReportDir = 'C:\Scripts\Veeam\Reports'

Add-PSSnapin SqlServerCmdletSnapin100

Add-PSSnapin SqlServerProviderSnapin100

$date = Get-Date -format "yyyyMMdd-HHmmss"

$TapeInventory = Invoke-Sqlcmd -InputFile $ScriptDir\VeeamTapeInventory.sql -ServerInstance "$VeeamSQLServer"

$TapeInventory | Export-Csv $ReportDir\VeeamTapeInventory_$Date.csv -NoTypeInformation

Write-Host "Tape inventory has been exported to: "

Write-Host "$ReportDir\VeeamTapeInventory_$Date.csv"

# ------- ExportVeeamTapeInventory.ps1 -------

winnt
Influencer
 
Posts: 21
Liked: 9 times
Joined: Fri Apr 03, 2015 9:19 pm
Full Name: J D

Re: Get tape content via powershell

Veeam Logoby ekisner » Mon Jun 11, 2018 3:15 pm

Winnt, you are fantastic. This is fantastic. It shouldn't be this difficult!
ekisner
Expert
 
Posts: 167
Liked: 32 times
Joined: Thu Jul 26, 2012 8:04 pm
Full Name: Erik Kisner

Previous

Return to PowerShell



Who is online

Users browsing this forum: No registered users and 8 guests