01-30-2014 09:31 PM
I am trying to create a report in Ops Center which will track when new (unused) media is inserted into our library.
I have created reports using the templates to track scratch media, but I would like to further filter the data of that to only show scratch media which has never been inserted before.
Any assistance would be appreciated
Thank you.
Solved! Go to Solution.
05-08-2014 08:46 PM
Quick update. I was able to get this working with powershelll and vmquery. a couple of splits of the output and I was able to track the created field which I have seen only updates the first time media is introduced to the system(usually).
$Id = @()
$Created = @()
$count = 0
$Month_End = [datetime]::Now
$Month_Start = $Month_End.AddMonths(-1)
$Poll_Date = $Month_Start.Month.ToString() + "-" + $Month_Start.Year.ToString()
<#Uncomment this section for test, should yield around 581
#$Month_End = "9/1/2013"
#$Month_Start = "8/1/2013"
#>
$Month_End = $Month_End.ToShortDateString()
$Month_Start = $Month_Start.ToShortDateString()
set-location "C:\Program Files\Veritas\volmgr\bin"
./vmquery -a | %{if($_.Contains("media ID:"))
{$Id += $_}
if($_.Contains("created:"))
{$Created += $_}
}
$Id = $Id | %{$_.Split(' ') | select -Last 1}
$Created = $Created | %{$_.Split(' ') | select -Last 3 | select -Index 0}
$i = 0
foreach($Date in $Created)
{if((get-date $Date) -gt (get-date $Month_Start))
{if((get-date $Date) -lt (get-date $Month_End))
{if(
$Id[$i].Contains("CLNU") -or
$Id[$i].Contains("CLNU") -or
$Id[$i].Contains("SRL") -or
$Id[$i].Contains("SRL") -or
$Id[$i].Contains("Z") -or
$Id[$i].Contains("Z") -or
$Id[$i].Contains("SE") -or
$Id[$i].Contains("SE") -or
$Id[$i].Contains("MN") -or
$Id[$i].Contains("MN") -or
$Id[$i].Contains("KP") -or
$Id[$i].Contains("KP") -or
$Id[$i].Contains("D") -or
$Id[$i].Contains("D") -or
$Id[$i].Contains("BOS") -or
$Id[$i].Contains("BOS") -or
$Id[$i].Contains("A") -or
$Id[$i].Contains("A")
){}else{$Count++}
}
}
$i++
}
set-location "script_out"
set-content "$Poll_Date.txt" $Count
blat - -body "Attached" -subject "New Media Count for $Poll_Date" -attach "..\script_out\$Poll_Date.txt" -to "(USERS)" -priority 1
02-24-2014 12:36 PM
nseniura,
If you are running OC-Analytics... try the following SQL I have created...
This should provide the fields (Media ID, Library, Slot Number, Volume Pool, Volume Group, and Date Created...
For any new media created in the past 7 days... which has never been mounted... contains no images and is not a cleaning tape..
SELECT
domain_media.id AS 'Media ID',
domain_media.libraryID AS 'Library',
domain_media.librarySlotNumber AS 'Slot Number',
domain_media.volumePoolName AS 'Volume Pool',
domain_media.volumeGroupName AS 'Volume Group',
utcbiginttonomtime(nb_media.unixDateCreated) AS 'Date Created'
FROM domain_media
JOIN nb_media
ON nb_media.id = domain_media.id
WHERE domain_media.isCleaning = 0
AND domain_media.isValid = 1
AND domain_media.deleted = 0
AND domain_media.imagecount = NULL
AND nb_media.nMounts = 0
AND DATEDIFF(day, utcbiginttonomtime(nb_media.unixDateCreated), GETDATE()) <=7
ORDER BY domain_media.id ASC
If you have any questions, please let me know....
--Tom
02-28-2014 04:03 AM
Unfourtunately, we are either not useing OC-Analytics or the server which hosts our webgui does not have SQL installed. It's also possible that I am not aware of how to find this feature of opscenter (I do everything in Opscenter through the webgui, and I am still learning that).
02-28-2014 01:25 PM
nseniura,
Log into OpsCenter - Click on Reports > Create New Report
It should be the 3rd radio button down...
Run SQL Query --- click next... (if this option is disabled, then you'll need an OC-Analytics Key)
If the option is available, copy and paste the custom SQL above, and follow the Wizard to run and save the report.
Let me know if you have any questions.
-Tom
04-11-2014 08:37 AM
I see now. No we do not have those features enabled so I only have the option of using an existing report template. I think if I am reading this correctly though, the SQL script you posted is just looking for valid media that is less than a week old that has no images, is clean, has no mounts
05-08-2014 08:46 PM
Quick update. I was able to get this working with powershelll and vmquery. a couple of splits of the output and I was able to track the created field which I have seen only updates the first time media is introduced to the system(usually).
$Id = @()
$Created = @()
$count = 0
$Month_End = [datetime]::Now
$Month_Start = $Month_End.AddMonths(-1)
$Poll_Date = $Month_Start.Month.ToString() + "-" + $Month_Start.Year.ToString()
<#Uncomment this section for test, should yield around 581
#$Month_End = "9/1/2013"
#$Month_Start = "8/1/2013"
#>
$Month_End = $Month_End.ToShortDateString()
$Month_Start = $Month_Start.ToShortDateString()
set-location "C:\Program Files\Veritas\volmgr\bin"
./vmquery -a | %{if($_.Contains("media ID:"))
{$Id += $_}
if($_.Contains("created:"))
{$Created += $_}
}
$Id = $Id | %{$_.Split(' ') | select -Last 1}
$Created = $Created | %{$_.Split(' ') | select -Last 3 | select -Index 0}
$i = 0
foreach($Date in $Created)
{if((get-date $Date) -gt (get-date $Month_Start))
{if((get-date $Date) -lt (get-date $Month_End))
{if(
$Id[$i].Contains("CLNU") -or
$Id[$i].Contains("CLNU") -or
$Id[$i].Contains("SRL") -or
$Id[$i].Contains("SRL") -or
$Id[$i].Contains("Z") -or
$Id[$i].Contains("Z") -or
$Id[$i].Contains("SE") -or
$Id[$i].Contains("SE") -or
$Id[$i].Contains("MN") -or
$Id[$i].Contains("MN") -or
$Id[$i].Contains("KP") -or
$Id[$i].Contains("KP") -or
$Id[$i].Contains("D") -or
$Id[$i].Contains("D") -or
$Id[$i].Contains("BOS") -or
$Id[$i].Contains("BOS") -or
$Id[$i].Contains("A") -or
$Id[$i].Contains("A")
){}else{$Count++}
}
}
$i++
}
set-location "script_out"
set-content "$Poll_Date.txt" $Count
blat - -body "Attached" -subject "New Media Count for $Poll_Date" -attach "..\script_out\$Poll_Date.txt" -to "(USERS)" -priority 1