Trying to track new media usage

[nseniura]

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.

[tom_sprouse]

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

[nseniura]

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

[tom_sprouse]

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

[nseniura]

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

[nseniura · Accepted solution]

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