cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted

bppllist powershell parse

I am starting to create a script to create an array or multiple arrays that i can create reports from.   I am not sure of the best way to approach this.  In the example below there are many lines that contain multiple fields all separated by spaces.  I am thinking of a single array where i would name each field and store the data for the field in the array.  But two of the problems i see with that are there can be an undermined number of clients listed and in most cases will be more than one schedule.

The way the output works is the first word is a keyword for the type of data, each space separated value after that is a field.  

One thought i had was to set everything to a var for its use and create a new entry in the array for the client / schedule ...Thoughts?

Example data?

CLASS oma11_ew_01_fr_2300_30_s *NULL* 0 760000 187200 *NULL*
NAMES
INFO 13 0 0 0 *NULL* 0 0 2147483647 0 0 0 0 0 0 0 0 0 0 1411059246 2863920C5DEC11E492FD73411F055F87 1 0 0 0 0 0 0 0 -1 0 1 0 0 0 0 3 0 6 0 0 1 0 0 0 1
KEY *NULL*
BCMD *NULL*
RCMD *NULL*
RES oma00_ar_den06_dp30w_sp *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL*
POOL NetBackup NetBackup NetBackup NetBackup NetBackup NetBackup NetBackup NetBackup NetBackup NetBackup
FOE 0 0 0 0 0 0 0 0 0 0
SHAREGROUP *ANY*
DATACLASSIFICATION *NULL*
CLIENT oma11cfswww02 Windows-x64 Windows2008 0 0 0 0 *NULL*
CLIENT oma11cfswww01 Windows-x86 Windows2003 0 0 0 0 *NULL*
CLIENT oma11cfsts02 Windows-x64 Windows2003 0 0 0 0 *NULL*
CLIENT oma11cfsts05 Windows-x86 Windows2003 0 0 0 0 *NULL*
CLIENT oma11cfsts04 Windows-x86 Windows2003 0 0 0 0 *NULL*
CLIENT oma11cfsts03 Windows-x86 Windows2003 0 0 0 0 *NULL*
CLIENT oma11cfsdoc01 Windows-x86 Windows2003 0 0 0 0 *NULL*
CLIENT oma11ccfsftp01 Windows-x64 Windows2008 0 0 0 0 *NULL*
CLIENT oma11ccfs01 Windows-x64 Windows2008 0 0 0 0 *NULL*
CLIENT oma11cfsctx01 Windows-x86 Windows2003 0 0 0 0 *NULL*
CLIENT oma11cfsctx02 Windows-x86 Windows2003 0 0 0 0 *NULL*
CLIENT oma11cfsctx03 Windows-x86 Windows2003 0 0 0 0 *NULL*
CLIENT oma11cfsctx04 Windows-x86 Windows2003 0 0 0 0 *NULL*
CLIENT oma11cfsctx05 Windows-x86 Windows2003 0 0 0 0 *NULL*
CLIENT oma00cfsctx02 Windows-x86 Windows2003 0 0 0 0 *NULL*
CLIENT oma00cfsctx01 Windows-x86 Windows2003 0 0 0 0 *NULL*
INCLUDE ALL_LOCAL_DRIVES
SCHED Full 0 1 604800 0 0 0 0 *NULL* 0 0 0 0 0 0 -1 0 0 0
SCHEDWIN 0 0 0 0 0 0 0 0 0 0 82800 25200 0 0
SCHEDRES *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL*
SCHEDPOOL *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL*
SCHEDRL 0 1 1 1 1 1 1 1 1 1
SCHEDFOE 0 0 0 0 0 0 0 0 0 0
SCHEDSG *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL*
SCHED Diff 1 1 86400 0 0 0 0 *NULL* 0 0 0 0 0 0 -1 0 0 0
SCHEDWIN 82800 25200 82800 25200 82800 25200 82800 25200 82800 25200 0 0 82800 25200
SCHEDRES *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL*
SCHEDPOOL *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL*
SCHEDRL 0 1 1 1 1 1 1 1 1 1
SCHEDFOE 0 0 0 0 0 0 0 0 0 0
SCHEDSG *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL* *NULL*

1 Solution

Accepted Solutions
Highlighted
Accepted Solution!

Re: bppllist powershell parse

yes i did look there, but it was not going to work out from there.   This was for an audit automation for our internal auditors.   I thought you guys might like to see what that script looked like:

 

## BPPLIST AUDIT INFO

#Functions -- Only need to update the client list for audits
#region Functions
add-type -assembly "system.io.compression.filesystem"
Function Get-UnixDate ($UnixDate) {[timezone]::CurrentTimeZone.ToLocalTime(([datetime]'1/1/1970').AddSeconds($UnixDate))}
#endregion

#Vars
#region Vars
$PSEmailServer="smtp.west.com"
$admincmd="C:\Program Files\Veritas\NetBackup\bin\admincmd"
$masters=("linux6160", "linux6164" ,"linux6168", "acw00006", "acw00007", "acw00008")
$auditlist=("NACODENA084", "den06vufinapp01", "den06vumissql01", "IBM505", "OMA11CFSTS02", "OMA00VCCFSCTX10", "Linux2225", "oma00vcwpepdb01", "Oma00cactdb03")
$bppllistOut = @()
$date = (get-date).tostring("yyyyMMdd")
$outputdir = "E:\data\scripts\output\Audits\tmp"
$auditfile = "E:\data\scripts\output\Audits\$date.auditors.data.zip"
$sqlcmdfile = "E:\data\Scripts\sql\Audit-JobFailures.sql"
#endregion

#dbisqlc vars
#region dbisqlc
$LD_LIBRARY_PATH="C:\Program Files\Symantec\OpsCenter\server\lib"
$machinename='wcw31340'
$DBACCESSSERVER='opscenter_wcw31340'
$INSTALLATIONDIR="C:\Program Files\Symantec\OpsCenter\server"
$EXECDBACCESS="C:\Program Files\Symantec\OpsCenter\server\db\WIN64\"
$DBACCESSPORT=13786
$DBACCESSUSER='DBA'
$DBACCESSPWD='SQL'
$DBACCESSNAME='vxpmdb'
#endregion


#HASH Lookup Tables
#region HASH's
$YesNoHash = @{"0" = "Yes"; "1" = "No"}
$NoYesHash = @{"0" = "No"; "1" = "Yes"}
$MachineTypeHash = @{"0" = "No virtual machine"; "1" = "VMware"; "2" = "Hyper-V"; "3" = "VxVI"}
$PolicyTypeHash = @{"0" = "Standard"; "1" = "Proxy"; "4" = "Oracle"; "6" = "Informix-On-BAR"; "7" = "Sybase";
"8" = "MS-SharePoint portal server"; "11" = "DataTools-SQL-BackTrack"; "13" = "MS-Windows"; "15" = "MS-SQL-Server";
"16" = "MS-Exchange-Server"; "17" = "SAP"; "18" = "DB2"; "19" = "NDMP"; "20" = "FlashBackup"; "21" = "Splitmirror";
"25" = "Lotus Notes"; "29" = "FlashBackup-Windows"; "35" = "NBU-Catalog"; "36" = "Generic"; "38" = "PureDisk export";
"39" = "Enterprise_Vault"; "40" = "VMware"; "41" = "Hyper-V"}
$ScheduleTypeHash = @{"0" = "Full schedule"; "1" = "Differential incremental schedule"; "2" = "Cumulative incremental schedule";
"3" = "User backup schedule"; "4" = "User archive schedule"}
#endregion

## BPPLLIST Data Gather
#$bppllist holds the final data
#region BPPLLIST

#Gather Info

Foreach ($master in $masters) {
$policylist = & "$admincmd\bppllist" -M $master
#Define hash for retention on master
$bpretlevelHash = @{}
$bpretlevelcmd = & "$admincmd\bpretlevel" -l -M $master
foreach ($level in $bpretlevelcmd){
$level = $level.ToString()
$Level = $level -split(" ")
$RetLevel = $level[0]
$RetLevlSec = $level[1]
$RetperiodNum = $level[2]
$RetPeriodLabel = $level[3]
$Retperiod = "$RetperiodNum $Retperiodlabel"
#Add to hash Array
$bpretlevelHash.$RetLevel = $retPeriod


#Collect POlicy Info
Foreach ($policy in $policylist){
$grab = & "$admincmd\bppllist" $policy -M $master
#Class Fields
$class= $grab| sls "CLASS ".ToString()
$class = $class -split(" ")
$ClassPolicyName=$class[1]
#INFO Fields
$info = $grab|sls "INFO ".ToString()
$info = $info -split(" ")
$infoPolicyType = $info[1]
$infoPolicyActive = $info[11]
$infoLifeCyleInuse = $info[30]
$infoMachineTYpe = $info[33]
#RES Fields
$res = $grab|sls "RES ".ToString()
$res = $res -split(" ")
$resStorageUnit = $res[1]
#Client Fields
$client = $grab |sls "CLIENT "
$clients = @()
foreach ($line in $client){
$line.ToString()
$line = $line -split(" ")
$ClientName = $line[1]
$ClientBit = $line[2]
$clientOS = $line[3]
#Populate Array
$Properties = @{Name=$ClientName; Bit=$ClientBit; OS=$ClientOS}
$Newobject = New-Object PSObject -Property $Properties
$clients += $Newobject
}

#INCLUDE Fields
$include = $grab|sls "INCLUDE ".ToString()
$include = $include -split(" ")
$IncludeSelection = $include[1]

#SCHED and SCHEDWIN Combined
#Grab feild data
$sched = $grab|sls "SCHED "
$schedwin = $grab|sls "SCHEDWIN "
#Build Combo Arrry
$SCHEDALL = @()
$total = $sched.count
$i = 0
while ($i -ne $total) {
$newline = $sched[$i].ToString(), $schedwin[$i].ToString() -join ' '
$SCHEDALL += $newline
$i = $i+1
}
$SCHEDS =@()
Foreach ($line in $SCHEDALL) {
$line = $line -split(" ")
$SChedName = $line[1]
$schedType = $line[2]
$schedFreqSec = $line[4]
$SchedRetetionLevel = $line[5] # See the -rl retention_level option description for a complete list of default values and their retention levels
$schedStorgeLifeCycleUsed = $line[17]
$SChedwinSunStart = $line[21] #Start value is the number of seconds past midnight
$schedwinSUnDur = $line[22] # Duration is the number of seconds past start. $SChedwinMonStart = $line[23] #Start value is the number of seconds past midnight
$schedwinMonDur = $line[24] # Duration is the number of seconds past start. $SChedwinTueStart = $line[25] #Start value is the number of seconds past midnight
$schedwinTueDur = $line[26] # Duration is the number of seconds past start. $SChedwinWedStart = $line[27] #Start value is the number of seconds past midnight
$schedwinWedDur = $line[28] # Duration is the number of seconds past start. $SChedwinThuStart = $line[29] #Start value is the number of seconds past midnight
$schedwinTHuDur = $line[30] # Duration is the number of seconds past start. $SChedwinFriStart = $line[31] #Start value is the number of seconds past midnight
$schedwinFriDur = $line[32] # Duration is the number of seconds past start. $SChedwinSatStart = $line[33] #Start value is the number of seconds past midnight
$schedwinSatDur = $line[34] # Duration is the number of seconds past start.
#Populate Array
$Properties = @{Name=$SchedName; Type=$SchedType; FreqSec=$SchedFreqSec; RetentionLevel=$SchedRetetionLevel;
LifeCycle=$schedStorgeLifeCycleUsed; SunStart=$SchedwinSunStart; SunDur=$SchedWinSunDur;
MonStart=$SchedwinMonStart; MonDur=$SchedWinMonDur; TueStart=$SchedwinTueStart; TueDur=$SchedWinTueDur;
WedStart=$SchedwinWedStart; WedDur=$SchedWinWedDur; ThuStart=$SchedwinThuStart; ThuDur=$SchedWinThuDur;
FriStart=$SchedwinFriStart; FriDur=$SchedWinFriDur; SatStart=$SchedwinSatStart; SatDur=$SchedWinSatDur}
$Newobject = New-Object PSObject -Property $Properties
$SCHEDS += $Newobject
}


#Create Final Array

$clients | Foreach {
# Write-Host "Starting Processing for Client $_.Name"
$clientName = $_.Name
$clientBit = $_.bit
$ClientOS = $_.OS

$SCHEDS | Foreach {
$schedName = $_.Name
$schedType = $_.Type
$schedFreqDays = $_.FreqSec ; $schedFreqDays = [timespan]::FromSeconds($schedFreqDays); $schedFreqDays = $schedFreqDays.Days
$schedFreqHours = $_.FreqSec ; $schedFreqHours = [timespan]::FromSeconds($schedFreqHours); $schedFreqHours = $schedFreqHours.Hours
$schedRetentionLevel = $_.RetentionLevel
$schedStorageLifeCycleused = $_.LifeCycle
$SChedwinSunStart = $_.SunStart ; $SChedwinSunStart = [timespan]::FromSeconds($SChedwinSunStart); $SChedwinSunStart = ("{0:hh\:mm\:ss}" -f $SChedwinSunStart)
$schedwinSUnDur = $_.SunDur ; $schedwinSUnDur = [timespan]::FromSeconds($schedwinSUnDur); $schedwinSunDur =$schedwinSunDur.hours
$SChedwinMonStart = $_.MonStart ; $SChedwinMonStart = [timespan]::FromSeconds($SChedwinMonStart); $SChedwinMonStart = ("{0:hh\:mm\:ss}" -f $SChedwinMonStart)
$schedwinMonDur = $_.MonDur ; $schedwinMonDur = [timespan]::FromSeconds($schedwinMonDur); $schedwinMonDur = $schedwinMonDur.hours
$SChedwinTueStart = $_.TueStart ; $SChedwinTueStart = [timespan]::FromSeconds($SChedwinTueStart); $SChedwinTueStart = ("{0:hh\:mm\:ss}" -f $SChedwinTueStart)
$schedwinTueDur = $_.TueDur ; $schedwinTueDur = [timespan]::FromSeconds($schedwinTueDur); $schedwinTueDur = $schedwinTueDur.hours
$SChedwinWedStart = $_.WedStart ; $SChedwinWedStart = [timespan]::FromSeconds($SChedwinWedStart); $SChedwinWedStart = ("{0:hh\:mm\:ss}" -f $SChedwinWedStart)
$schedwinWedDur = $_.WedDur ; $schedwinWedDur = [timespan]::FromSeconds($schedwinWedDur); $schedwinWedDur = $schedwinWedDur.hours
$SChedwinThuStart = $_.ThuStart ; $SChedwinThuStart = [timespan]::FromSeconds($SChedwinThuStart); $SChedwinThuStart = ("{0:hh\:mm\:ss}" -f $SChedwinThuStart)
$schedwinTHuDur = $_.ThuDur ; $schedwinThuDur = [timespan]::FromSeconds($schedwinThuDur); $schedwinThuDur = $schedwinThuDur.hours
$SChedwinFriStart = $_.FriStart ; $SChedwinFriStart = [timespan]::FromSeconds($SChedwinFriStart); $SChedwinFriStart = ("{0:hh\:mm\:ss}" -f $SChedwinFriStart)
$schedwinFriDur = $_.FriDur ; $schedwinFriDur = [timespan]::FromSeconds($schedwinFriDur); $schedwinFriDur = $schedwinFriDur.hours
$SChedwinSatStart = $_.SatStart ; $SChedwinSatStart = [timespan]::FromSeconds($SChedwinSatStart); $SChedwinSatStart = ("{0:hh\:mm\:ss}" -f $SChedwinSatStart)
$schedwinSatDur = $_.SatDur ; $schedwinSatDur = [timespan]::FromSeconds($schedwinSatDur); $schedwinSatDur = $schedwinSatDur.hours
#write-host "Start Processing for Client $ClientName on sched $schedname"

#Populate Array
$Properties = @{PolicyName=$ClassPolicyName; PolicyType=$PolicyTypeHash.$infoPolicyType; PolicyActive=$YesNoHash."$infoPolicyActive" ;
LifeCycleInUse=$NoYesHash.$infoLifeCyleInuse; MachineType=$MachineTypeHash.$infoMachineTYpe; StorageUnit=$resStorageUnit;
IncudedSelection=$IncludeSelection; ClientName =$clientName; ClientBit=$clientBit; ClientOS=$ClientOS;
SchedName=$schedName; SchedType=$ScheduleTypeHash.$schedType; SchedFreqDays=$schedFreqDays; SchedFreqHours=$schedFreqHours;
SchedRetLevel=$bpretlevelHash.$schedRetentionLevel; SchedStorageLifecyleUsed=$NoYesHash.$schedStorageLifeCycleused; SunStart=$SChedwinSunStart;
SunDur = $schedwinSUnDur; MonStart=$SChedwinMonStart; MonDur=$schedwinMonDur; TueStart=$SChedwinTueStart; TueDur=$schedwinTueDur;
WedStart=$SChedwinWedStart; WedDur=$schedwinWedDur; ThuStart=$SChedwinThuStart; ThuDur=$schedwinThuDur; FriStart=$SChedwinFriStart; FriDur=$schedwinFriDur;
SatStart=$SChedwinSatStart; SatDur=$schedwinSatDur }
$Newobject = New-Object PSObject -Property $Properties
$bppllistout += $Newobject
}

}
}
}
}
#endregion


#BPIMAGELIST Data Gather
#$bpimages holds the final data
#region BPIMAGELIST
#Collect Last backup for each node

$bpimagelist = @()
foreach ($master in $masters){
Foreach ($client in $auditlist){


#Collect last backup Info for each Client
$collection = & "$admincmd\bpimagelist" -l -M $master -client $client | Select-Object -first 1
If ($collection -notlike '*no entity was found*'){$bpimagelist += $collection}


}
}# Finished Collecting image info
$bpimages = @()

Foreach ($image in $bpimagelist){
$image = $image.tostring()
$image = $image -split(" ")
$bpimageClient = $image[1]
$bpimageDate = $image[13]
$bpimageDate = Get-UnixDate $bpimageDate|Get-Date –f "yyyy-MM-dd HH:mm:ss"
#Populate Array with data
$Properties = @{Client=$bpimageClient; "Last Backup"=$BpimageDate}
$Newobject = New-Object PSObject -Property $Properties
$bpimages += $Newobject
}

#endregion
#dbisqlc Job Failure Gather #region dbisqlc Job Failure Gather
$sqlcmd = "Select ClientName, policyname, schedulename, adjust_timestamp(domain_JobArchive.startTime,-18000000 ) as StartTime, statuscode from domain_jobArchive Where statuscode
not in (1, 0, 150) AND policytype != '-1' AND ClientName in ('$($auditlist -join "','")'); output to $outputdir\JobFailureData.csv; go"
$sqlcmd |Out-File -Encoding ascii -FilePath $sqlcmdfile

# Executing Job Failure Query

& "$execdbaccess\dbisqlc" -q -c "ENG=$DBACCESSSERVER;DBN=$DBACCESSNAME;LINKS=all;UID=$DBACCESSUSER;PWD=$DBACCESSPWD" $sqlcmdfile |Out-Null

(Get-Content "$outputdir\JobFailureData.csv") | ForEach-Object { $_ -replace "'" } > "$outputdir\JobFailureData.csv"
$dbisqlcJobFailure = Import-Csv "$outputdir\JobFailureData.csv" -Header "ClientName", "PolicyName", "ScheduleName", "StartTime", "StatusCode"

#endregion ## Copy script file for auditors and Clean tmp dir
#region Script Copy
remove-item "$outputdir\*"
If(Test-path "E:\data\output\Audits\$date.Auditscript.zip") {Remove-item $auditfile}
cp E:\data\scripts\bppllist-audits.ps1 $outputdir

#endregion

##Reports for Auditor: #region Reports
#Bppllist report
$bppllistOut|Where-Object {$_.clientname -in $auditlist}|select-object -Property PolicyName, Clientname, StorageUnit, IncudedSelection, SchedName, SchedType, SchedFreqDays, SchedFreqHours ,
SchedRetLevel, SunStart, SunDur, MonStart, MonDur, TueStart, TueDur, WedStart, WedDur, ThuStart, ThuDur,
FriStart, FriDur, SatStart, SatDur |export-csv -Path $outputdir\PolicyAudit.$data.csv -NoTypeInformation

#BPIMAGE Report
$bpimages |export-csv -path $outputdir\LastSucessfullBackup.$date.csv -NoTypeInformation

#Report for Failed Jobs
$dbisqlcJobFailure |sort Clientname|group policyname| foreach { $_ |Select-Object -ExpandProperty group |Select-Object -first 1}|export-csv -Path $outputdir\JobFailure.$date.csv -noTypeInformation


#endregion


##Prepare Auditor file
#region AuditorFile
[io.compression.zipfile]::CreateFromDirectory("$outputdir" , "$auditfile") Send-MailMessage -to tosomeone@somewhere.com -from host@somwhere.com -Subject "Audit Report for NBU" -Attachments $auditfile -body "Attached you will find the reports needed for auiditors. The StorageUnit column will indicate if there is replication. If there is thre will be an _ar_ in the name. The name will indicate source_ar_destination." #endregi

View solution in original post

2 Replies
Highlighted

Re: bppllist powershell parse

First a question, have you checked if you can get the needed report(s) from OpsCenter ?

If not this technote https://www.veritas.com/support/en_US/article.000029722 explain the different bppllist entries

For you purpose the -U or -L option might be better fit

There has also been a technote about the command lines for the different reports in the NetBackup Console that be useful

 

The standard questions: Have you checked: 1) What has changed. 2) The manual 3) If there are any tech notes or VOX posts regarding the issue
Highlighted
Accepted Solution!

Re: bppllist powershell parse

yes i did look there, but it was not going to work out from there.   This was for an audit automation for our internal auditors.   I thought you guys might like to see what that script looked like:

 

## BPPLIST AUDIT INFO

#Functions -- Only need to update the client list for audits
#region Functions
add-type -assembly "system.io.compression.filesystem"
Function Get-UnixDate ($UnixDate) {[timezone]::CurrentTimeZone.ToLocalTime(([datetime]'1/1/1970').AddSeconds($UnixDate))}
#endregion

#Vars
#region Vars
$PSEmailServer="smtp.west.com"
$admincmd="C:\Program Files\Veritas\NetBackup\bin\admincmd"
$masters=("linux6160", "linux6164" ,"linux6168", "acw00006", "acw00007", "acw00008")
$auditlist=("NACODENA084", "den06vufinapp01", "den06vumissql01", "IBM505", "OMA11CFSTS02", "OMA00VCCFSCTX10", "Linux2225", "oma00vcwpepdb01", "Oma00cactdb03")
$bppllistOut = @()
$date = (get-date).tostring("yyyyMMdd")
$outputdir = "E:\data\scripts\output\Audits\tmp"
$auditfile = "E:\data\scripts\output\Audits\$date.auditors.data.zip"
$sqlcmdfile = "E:\data\Scripts\sql\Audit-JobFailures.sql"
#endregion

#dbisqlc vars
#region dbisqlc
$LD_LIBRARY_PATH="C:\Program Files\Symantec\OpsCenter\server\lib"
$machinename='wcw31340'
$DBACCESSSERVER='opscenter_wcw31340'
$INSTALLATIONDIR="C:\Program Files\Symantec\OpsCenter\server"
$EXECDBACCESS="C:\Program Files\Symantec\OpsCenter\server\db\WIN64\"
$DBACCESSPORT=13786
$DBACCESSUSER='DBA'
$DBACCESSPWD='SQL'
$DBACCESSNAME='vxpmdb'
#endregion


#HASH Lookup Tables
#region HASH's
$YesNoHash = @{"0" = "Yes"; "1" = "No"}
$NoYesHash = @{"0" = "No"; "1" = "Yes"}
$MachineTypeHash = @{"0" = "No virtual machine"; "1" = "VMware"; "2" = "Hyper-V"; "3" = "VxVI"}
$PolicyTypeHash = @{"0" = "Standard"; "1" = "Proxy"; "4" = "Oracle"; "6" = "Informix-On-BAR"; "7" = "Sybase";
"8" = "MS-SharePoint portal server"; "11" = "DataTools-SQL-BackTrack"; "13" = "MS-Windows"; "15" = "MS-SQL-Server";
"16" = "MS-Exchange-Server"; "17" = "SAP"; "18" = "DB2"; "19" = "NDMP"; "20" = "FlashBackup"; "21" = "Splitmirror";
"25" = "Lotus Notes"; "29" = "FlashBackup-Windows"; "35" = "NBU-Catalog"; "36" = "Generic"; "38" = "PureDisk export";
"39" = "Enterprise_Vault"; "40" = "VMware"; "41" = "Hyper-V"}
$ScheduleTypeHash = @{"0" = "Full schedule"; "1" = "Differential incremental schedule"; "2" = "Cumulative incremental schedule";
"3" = "User backup schedule"; "4" = "User archive schedule"}
#endregion

## BPPLLIST Data Gather
#$bppllist holds the final data
#region BPPLLIST

#Gather Info

Foreach ($master in $masters) {
$policylist = & "$admincmd\bppllist" -M $master
#Define hash for retention on master
$bpretlevelHash = @{}
$bpretlevelcmd = & "$admincmd\bpretlevel" -l -M $master
foreach ($level in $bpretlevelcmd){
$level = $level.ToString()
$Level = $level -split(" ")
$RetLevel = $level[0]
$RetLevlSec = $level[1]
$RetperiodNum = $level[2]
$RetPeriodLabel = $level[3]
$Retperiod = "$RetperiodNum $Retperiodlabel"
#Add to hash Array
$bpretlevelHash.$RetLevel = $retPeriod


#Collect POlicy Info
Foreach ($policy in $policylist){
$grab = & "$admincmd\bppllist" $policy -M $master
#Class Fields
$class= $grab| sls "CLASS ".ToString()
$class = $class -split(" ")
$ClassPolicyName=$class[1]
#INFO Fields
$info = $grab|sls "INFO ".ToString()
$info = $info -split(" ")
$infoPolicyType = $info[1]
$infoPolicyActive = $info[11]
$infoLifeCyleInuse = $info[30]
$infoMachineTYpe = $info[33]
#RES Fields
$res = $grab|sls "RES ".ToString()
$res = $res -split(" ")
$resStorageUnit = $res[1]
#Client Fields
$client = $grab |sls "CLIENT "
$clients = @()
foreach ($line in $client){
$line.ToString()
$line = $line -split(" ")
$ClientName = $line[1]
$ClientBit = $line[2]
$clientOS = $line[3]
#Populate Array
$Properties = @{Name=$ClientName; Bit=$ClientBit; OS=$ClientOS}
$Newobject = New-Object PSObject -Property $Properties
$clients += $Newobject
}

#INCLUDE Fields
$include = $grab|sls "INCLUDE ".ToString()
$include = $include -split(" ")
$IncludeSelection = $include[1]

#SCHED and SCHEDWIN Combined
#Grab feild data
$sched = $grab|sls "SCHED "
$schedwin = $grab|sls "SCHEDWIN "
#Build Combo Arrry
$SCHEDALL = @()
$total = $sched.count
$i = 0
while ($i -ne $total) {
$newline = $sched[$i].ToString(), $schedwin[$i].ToString() -join ' '
$SCHEDALL += $newline
$i = $i+1
}
$SCHEDS =@()
Foreach ($line in $SCHEDALL) {
$line = $line -split(" ")
$SChedName = $line[1]
$schedType = $line[2]
$schedFreqSec = $line[4]
$SchedRetetionLevel = $line[5] # See the -rl retention_level option description for a complete list of default values and their retention levels
$schedStorgeLifeCycleUsed = $line[17]
$SChedwinSunStart = $line[21] #Start value is the number of seconds past midnight
$schedwinSUnDur = $line[22] # Duration is the number of seconds past start. $SChedwinMonStart = $line[23] #Start value is the number of seconds past midnight
$schedwinMonDur = $line[24] # Duration is the number of seconds past start. $SChedwinTueStart = $line[25] #Start value is the number of seconds past midnight
$schedwinTueDur = $line[26] # Duration is the number of seconds past start. $SChedwinWedStart = $line[27] #Start value is the number of seconds past midnight
$schedwinWedDur = $line[28] # Duration is the number of seconds past start. $SChedwinThuStart = $line[29] #Start value is the number of seconds past midnight
$schedwinTHuDur = $line[30] # Duration is the number of seconds past start. $SChedwinFriStart = $line[31] #Start value is the number of seconds past midnight
$schedwinFriDur = $line[32] # Duration is the number of seconds past start. $SChedwinSatStart = $line[33] #Start value is the number of seconds past midnight
$schedwinSatDur = $line[34] # Duration is the number of seconds past start.
#Populate Array
$Properties = @{Name=$SchedName; Type=$SchedType; FreqSec=$SchedFreqSec; RetentionLevel=$SchedRetetionLevel;
LifeCycle=$schedStorgeLifeCycleUsed; SunStart=$SchedwinSunStart; SunDur=$SchedWinSunDur;
MonStart=$SchedwinMonStart; MonDur=$SchedWinMonDur; TueStart=$SchedwinTueStart; TueDur=$SchedWinTueDur;
WedStart=$SchedwinWedStart; WedDur=$SchedWinWedDur; ThuStart=$SchedwinThuStart; ThuDur=$SchedWinThuDur;
FriStart=$SchedwinFriStart; FriDur=$SchedWinFriDur; SatStart=$SchedwinSatStart; SatDur=$SchedWinSatDur}
$Newobject = New-Object PSObject -Property $Properties
$SCHEDS += $Newobject
}


#Create Final Array

$clients | Foreach {
# Write-Host "Starting Processing for Client $_.Name"
$clientName = $_.Name
$clientBit = $_.bit
$ClientOS = $_.OS

$SCHEDS | Foreach {
$schedName = $_.Name
$schedType = $_.Type
$schedFreqDays = $_.FreqSec ; $schedFreqDays = [timespan]::FromSeconds($schedFreqDays); $schedFreqDays = $schedFreqDays.Days
$schedFreqHours = $_.FreqSec ; $schedFreqHours = [timespan]::FromSeconds($schedFreqHours); $schedFreqHours = $schedFreqHours.Hours
$schedRetentionLevel = $_.RetentionLevel
$schedStorageLifeCycleused = $_.LifeCycle
$SChedwinSunStart = $_.SunStart ; $SChedwinSunStart = [timespan]::FromSeconds($SChedwinSunStart); $SChedwinSunStart = ("{0:hh\:mm\:ss}" -f $SChedwinSunStart)
$schedwinSUnDur = $_.SunDur ; $schedwinSUnDur = [timespan]::FromSeconds($schedwinSUnDur); $schedwinSunDur =$schedwinSunDur.hours
$SChedwinMonStart = $_.MonStart ; $SChedwinMonStart = [timespan]::FromSeconds($SChedwinMonStart); $SChedwinMonStart = ("{0:hh\:mm\:ss}" -f $SChedwinMonStart)
$schedwinMonDur = $_.MonDur ; $schedwinMonDur = [timespan]::FromSeconds($schedwinMonDur); $schedwinMonDur = $schedwinMonDur.hours
$SChedwinTueStart = $_.TueStart ; $SChedwinTueStart = [timespan]::FromSeconds($SChedwinTueStart); $SChedwinTueStart = ("{0:hh\:mm\:ss}" -f $SChedwinTueStart)
$schedwinTueDur = $_.TueDur ; $schedwinTueDur = [timespan]::FromSeconds($schedwinTueDur); $schedwinTueDur = $schedwinTueDur.hours
$SChedwinWedStart = $_.WedStart ; $SChedwinWedStart = [timespan]::FromSeconds($SChedwinWedStart); $SChedwinWedStart = ("{0:hh\:mm\:ss}" -f $SChedwinWedStart)
$schedwinWedDur = $_.WedDur ; $schedwinWedDur = [timespan]::FromSeconds($schedwinWedDur); $schedwinWedDur = $schedwinWedDur.hours
$SChedwinThuStart = $_.ThuStart ; $SChedwinThuStart = [timespan]::FromSeconds($SChedwinThuStart); $SChedwinThuStart = ("{0:hh\:mm\:ss}" -f $SChedwinThuStart)
$schedwinTHuDur = $_.ThuDur ; $schedwinThuDur = [timespan]::FromSeconds($schedwinThuDur); $schedwinThuDur = $schedwinThuDur.hours
$SChedwinFriStart = $_.FriStart ; $SChedwinFriStart = [timespan]::FromSeconds($SChedwinFriStart); $SChedwinFriStart = ("{0:hh\:mm\:ss}" -f $SChedwinFriStart)
$schedwinFriDur = $_.FriDur ; $schedwinFriDur = [timespan]::FromSeconds($schedwinFriDur); $schedwinFriDur = $schedwinFriDur.hours
$SChedwinSatStart = $_.SatStart ; $SChedwinSatStart = [timespan]::FromSeconds($SChedwinSatStart); $SChedwinSatStart = ("{0:hh\:mm\:ss}" -f $SChedwinSatStart)
$schedwinSatDur = $_.SatDur ; $schedwinSatDur = [timespan]::FromSeconds($schedwinSatDur); $schedwinSatDur = $schedwinSatDur.hours
#write-host "Start Processing for Client $ClientName on sched $schedname"

#Populate Array
$Properties = @{PolicyName=$ClassPolicyName; PolicyType=$PolicyTypeHash.$infoPolicyType; PolicyActive=$YesNoHash."$infoPolicyActive" ;
LifeCycleInUse=$NoYesHash.$infoLifeCyleInuse; MachineType=$MachineTypeHash.$infoMachineTYpe; StorageUnit=$resStorageUnit;
IncudedSelection=$IncludeSelection; ClientName =$clientName; ClientBit=$clientBit; ClientOS=$ClientOS;
SchedName=$schedName; SchedType=$ScheduleTypeHash.$schedType; SchedFreqDays=$schedFreqDays; SchedFreqHours=$schedFreqHours;
SchedRetLevel=$bpretlevelHash.$schedRetentionLevel; SchedStorageLifecyleUsed=$NoYesHash.$schedStorageLifeCycleused; SunStart=$SChedwinSunStart;
SunDur = $schedwinSUnDur; MonStart=$SChedwinMonStart; MonDur=$schedwinMonDur; TueStart=$SChedwinTueStart; TueDur=$schedwinTueDur;
WedStart=$SChedwinWedStart; WedDur=$schedwinWedDur; ThuStart=$SChedwinThuStart; ThuDur=$schedwinThuDur; FriStart=$SChedwinFriStart; FriDur=$schedwinFriDur;
SatStart=$SChedwinSatStart; SatDur=$schedwinSatDur }
$Newobject = New-Object PSObject -Property $Properties
$bppllistout += $Newobject
}

}
}
}
}
#endregion


#BPIMAGELIST Data Gather
#$bpimages holds the final data
#region BPIMAGELIST
#Collect Last backup for each node

$bpimagelist = @()
foreach ($master in $masters){
Foreach ($client in $auditlist){


#Collect last backup Info for each Client
$collection = & "$admincmd\bpimagelist" -l -M $master -client $client | Select-Object -first 1
If ($collection -notlike '*no entity was found*'){$bpimagelist += $collection}


}
}# Finished Collecting image info
$bpimages = @()

Foreach ($image in $bpimagelist){
$image = $image.tostring()
$image = $image -split(" ")
$bpimageClient = $image[1]
$bpimageDate = $image[13]
$bpimageDate = Get-UnixDate $bpimageDate|Get-Date –f "yyyy-MM-dd HH:mm:ss"
#Populate Array with data
$Properties = @{Client=$bpimageClient; "Last Backup"=$BpimageDate}
$Newobject = New-Object PSObject -Property $Properties
$bpimages += $Newobject
}

#endregion
#dbisqlc Job Failure Gather #region dbisqlc Job Failure Gather
$sqlcmd = "Select ClientName, policyname, schedulename, adjust_timestamp(domain_JobArchive.startTime,-18000000 ) as StartTime, statuscode from domain_jobArchive Where statuscode
not in (1, 0, 150) AND policytype != '-1' AND ClientName in ('$($auditlist -join "','")'); output to $outputdir\JobFailureData.csv; go"
$sqlcmd |Out-File -Encoding ascii -FilePath $sqlcmdfile

# Executing Job Failure Query

& "$execdbaccess\dbisqlc" -q -c "ENG=$DBACCESSSERVER;DBN=$DBACCESSNAME;LINKS=all;UID=$DBACCESSUSER;PWD=$DBACCESSPWD" $sqlcmdfile |Out-Null

(Get-Content "$outputdir\JobFailureData.csv") | ForEach-Object { $_ -replace "'" } > "$outputdir\JobFailureData.csv"
$dbisqlcJobFailure = Import-Csv "$outputdir\JobFailureData.csv" -Header "ClientName", "PolicyName", "ScheduleName", "StartTime", "StatusCode"

#endregion ## Copy script file for auditors and Clean tmp dir
#region Script Copy
remove-item "$outputdir\*"
If(Test-path "E:\data\output\Audits\$date.Auditscript.zip") {Remove-item $auditfile}
cp E:\data\scripts\bppllist-audits.ps1 $outputdir

#endregion

##Reports for Auditor: #region Reports
#Bppllist report
$bppllistOut|Where-Object {$_.clientname -in $auditlist}|select-object -Property PolicyName, Clientname, StorageUnit, IncudedSelection, SchedName, SchedType, SchedFreqDays, SchedFreqHours ,
SchedRetLevel, SunStart, SunDur, MonStart, MonDur, TueStart, TueDur, WedStart, WedDur, ThuStart, ThuDur,
FriStart, FriDur, SatStart, SatDur |export-csv -Path $outputdir\PolicyAudit.$data.csv -NoTypeInformation

#BPIMAGE Report
$bpimages |export-csv -path $outputdir\LastSucessfullBackup.$date.csv -NoTypeInformation

#Report for Failed Jobs
$dbisqlcJobFailure |sort Clientname|group policyname| foreach { $_ |Select-Object -ExpandProperty group |Select-Object -first 1}|export-csv -Path $outputdir\JobFailure.$date.csv -noTypeInformation


#endregion


##Prepare Auditor file
#region AuditorFile
[io.compression.zipfile]::CreateFromDirectory("$outputdir" , "$auditfile") Send-MailMessage -to tosomeone@somewhere.com -from host@somwhere.com -Subject "Audit Report for NBU" -Attachments $auditfile -body "Attached you will find the reports needed for auiditors. The StorageUnit column will indicate if there is replication. If there is thre will be an _ar_ in the name. The name will indicate source_ar_destination." #endregi

View solution in original post