kes Print the Volume purged message only for real jobs to keep

[bacula/bacula] / bacula / src / dird / query.sql

diff --git a/bacula/src/dird/query.sql b/bacula/src/dird/query.sql
index efdfe25624ed59f64c53e1a5c86b83c55f925075..a287e4e884ff31244efdedb12a9e4e7b5c46073f 100644 (file)
--- a/bacula/src/dird/query.sql
+++ b/bacula/src/dird/query.sql
@@ -1,26 +1,19 @@
-:List Job totals:
-SELECT  count(*) AS Jobs, sum(JobFiles) AS Files, 
- sum(JobBytes) AS Bytes, Name AS Job FROM Job GROUP BY Name;
-SELECT max(JobId) AS Jobs,sum(JobFiles) AS Files,
- sum(JobBytes) As Bytes FROM Job;
-# 2 
-:List where a File is saved regardless of the directory:
+# 1 
+:List up to 20 places where a File is saved regardless of the directory

 *Enter Filename (no path):
 *Enter Filename (no path):

-SELECT Job.JobId as JobId, Client.Name as Client,
- Path.Path,Filename.Name,
- StartTime,Level,JobFiles,JobBytes
+SELECT DISTINCT Job.JobId as JobId, Client.Name as Client,
+  Path.Path,Filename.Name,StartTime,Level,JobFiles,JobBytes

  FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId
  AND JobStatus='T' AND Job.JobId=File.JobId
  AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId
  AND Filename.Name='%1' 
  FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId
  AND JobStatus='T' AND Job.JobId=File.JobId
  AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId
  AND Filename.Name='%1' 

- GROUP BY Job.JobId

  ORDER BY Job.StartTime LIMIT 20;
  ORDER BY Job.StartTime LIMIT 20;

-# 3
-:List where the most recent copies of a file are saved:
+# 2
+:List where the most recent copies of a file are saved

 *Enter path with trailing slash:
 *Enter filename:
 *Enter Client name:
 *Enter path with trailing slash:
 *Enter filename:
 *Enter Client name:

-SELECT Job.JobId,StartTime AS JobStartTime,VolumeName,Client.Name AS ClientName
+SELECT DISTINCT Job.JobId,StartTime AS JobStartTime,VolumeName,Client.Name AS ClientName

  FROM Job,File,Path,Filename,Media,JobMedia,Client
  WHERE File.JobId=Job.JobId
  AND Path.Path='%1'
  FROM Job,File,Path,Filename,Media,JobMedia,Client
  WHERE File.JobId=Job.JobId
  AND Path.Path='%1'


@@ -31,84 +24,81 @@ SELECT Job.JobId,StartTime AS JobStartTime,VolumeName,Client.Name AS ClientName
  AND JobMedia.JobId=Job.JobId
  AND JobMedia.MediaId=Media.MediaId
  AND Client.ClientId=Job.ClientId
  AND JobMedia.JobId=Job.JobId
  AND JobMedia.MediaId=Media.MediaId
  AND Client.ClientId=Job.ClientId

- GROUP BY Job.JobId

  ORDER BY Job.StartTime DESC LIMIT 5;
  ORDER BY Job.StartTime DESC LIMIT 5;

-# 4
-:List last 20 Full Backups for a Client:
+# 3
+:List last 20 Full Backups for a Client

 *Enter Client name:
 *Enter Client name:

-SELECT Job.JobId,Client.Name AS Client,StartTime,JobFiles,JobBytes,
-JobMedia.StartFile as VolFile,VolumeName
+SELECT DISTINCT Job.JobId,Client.Name AS Client,StartTime,JobFiles,JobBytes,
+  JobMedia.StartFile as VolFile,VolumeName

  FROM Client,Job,JobMedia,Media
  WHERE Client.Name='%1'
  AND Client.ClientId=Job.ClientId
  AND Level='F' AND JobStatus='T'
  AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
  FROM Client,Job,JobMedia,Media
  WHERE Client.Name='%1'
  AND Client.ClientId=Job.ClientId
  AND Level='F' AND JobStatus='T'
  AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId

- GROUP BY Job.JobId

  ORDER BY Job.StartTime DESC LIMIT 20;
  ORDER BY Job.StartTime DESC LIMIT 20;

-# 5
+# 4

 :List all backups for a Client after a specified time
 *Enter Client Name:
 *Enter time in YYYY-MM-DD HH:MM:SS format:
 :List all backups for a Client after a specified time
 *Enter Client Name:
 *Enter time in YYYY-MM-DD HH:MM:SS format:

-SELECT Job.JobId,Client.Name as Client,Level,StartTime,JobFiles,JobBytes,VolumeName
-  FROM Client,Job,JobMedia,Media
-  WHERE Client.Name='%1'
-  AND Client.ClientId=Job.ClientId
-  AND JobStatus='T'
-  AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
-  AND Job.StartTime >= '%2'
-  GROUP BY Job.JobId
-  ORDER BY Job.StartTime;
-# 6
+SELECT DISTINCT Job.JobId,Client.Name as Client,Level,StartTime,JobFiles,JobBytes,VolumeName
+ FROM Client,Job,JobMedia,Media
+ WHERE Client.Name='%1'
+ AND Client.ClientId=Job.ClientId
+ AND JobStatus='T'
+ AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
+ AND Job.StartTime >= '%2'
+ ORDER BY Job.StartTime;
+# 5

 :List all backups for a Client
 *Enter Client Name:
 :List all backups for a Client
 *Enter Client Name:

-SELECT Job.JobId,Client.Name as Client,Level,StartTime,JobFiles,JobBytes,VolumeName
-  FROM Client,Job,JobMedia,Media
-  WHERE Client.Name='%1'
-  AND Client.ClientId=Job.ClientId
-  AND JobStatus='T'
-  AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
-  GROUP BY Job.JobId
-  ORDER BY Job.StartTime;
-# 7
-:List Volume Attributes for a selected Volume:
+SELECT DISTINCT Job.JobId as JobId,Client.Name as Client,
+   FileSet.FileSet AS FileSet,Level,StartTime,
+   JobFiles,JobBytes,VolumeName
+ FROM Client,Job,JobMedia,Media,FileSet
+ WHERE Client.Name='%1'
+ AND Client.ClientId=Job.ClientId
+ AND JobStatus='T' AND Job.FileSetId=FileSet.FileSetId
+ AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
+ ORDER BY Job.StartTime;
+# 6
+:List Volume Attributes for a selected Volume

 *Enter Volume name:
 SELECT Slot,MaxVolBytes,VolCapacityBytes,VolStatus,Recycle,VolRetention,
 *Enter Volume name:
 SELECT Slot,MaxVolBytes,VolCapacityBytes,VolStatus,Recycle,VolRetention,

- VolUseDuration,MaxVolJobs,MaxVolFiles
+  VolUseDuration,MaxVolJobs,MaxVolFiles

  FROM Media   
  WHERE VolumeName='%1';
  FROM Media   
  WHERE VolumeName='%1';

-# 8
-:List Volumes used by selected JobId:
+# 7
+:List Volumes used by selected JobId

 *Enter JobId:
 *Enter JobId:

-SELECT Job.JobId,VolumeName 
+SELECT DISTINCT Job.JobId,VolumeName 

  FROM Job,JobMedia,Media 
  WHERE Job.JobId=%1 
  AND Job.JobId=JobMedia.JobId 
  FROM Job,JobMedia,Media 
  WHERE Job.JobId=%1 
  AND Job.JobId=JobMedia.JobId 

- AND JobMedia.MediaId=Media.MediaId 
- GROUP BY VolumeName;
-# 9
-:List Volumes to Restore All Files:
+ AND JobMedia.MediaId=Media.MediaId;
+# 8
+:List Volumes to Restore All Files

 *Enter Client Name:
 !DROP TABLE temp;
 !DROP TABLE temp2;
 *Enter Client Name:
 !DROP TABLE temp;
 !DROP TABLE temp2;

-CREATE TABLE temp (JobId INTEGER UNSIGNED NOT NULL,
- JobTDate BIGINT UNSIGNED,
- ClientId INTEGER UNSIGNED,
+CREATE TABLE temp (JobId BIGINT NOT NULL,
+ JobTDate BIGINT,
+ ClientId BIGINT,

  Level CHAR,
  StartTime TEXT,
  VolumeName TEXT,
  Level CHAR,
  StartTime TEXT,
  VolumeName TEXT,

- StartFile INTEGER UNSIGNED, 
- VolSessionId INTEGER UNSIGNED,
- VolSessionTime INTEGER UNSIGNED);
-CREATE TABLE temp2 (JobId INTEGER UNSIGNED NOT NULL,
+ StartFile BIGINT, 
+ VolSessionId BIGINT,
+ VolSessionTime BIGINT );
+CREATE TABLE temp2 (JobId BIGINT NOT NULL,

  StartTime TEXT,
  VolumeName TEXT,
  Level CHAR,
  StartTime TEXT,
  VolumeName TEXT,
  Level CHAR,

- StartFile INTEGER UNSIGNED, 
- VolSessionId INTEGER UNSIGNED,
- VolSessionTime INTEGER UNSIGNED);
+ StartFile BIGINT, 
+ VolSessionId BIGINT,
+ VolSessionTime BIGINT);

 # Select last Full save
 INSERT INTO temp SELECT Job.JobId,JobTDate,Job.ClientId,Job.Level,
 # Select last Full save
 INSERT INTO temp SELECT Job.JobId,JobTDate,Job.ClientId,Job.Level,

-   StartTime,VolumeName,JobMedia.StartFile,VolSessionId,VolSessionTime
+  StartTime,VolumeName,JobMedia.StartFile,VolSessionId,VolSessionTime

  FROM Client,Job,JobMedia,Media WHERE Client.Name='%1'
  AND Client.ClientId=Job.ClientId
  AND Level='F' AND JobStatus='T'
  FROM Client,Job,JobMedia,Media WHERE Client.Name='%1'
  AND Client.ClientId=Job.ClientId
  AND Level='F' AND JobStatus='T'


@@ -117,69 +107,82 @@ INSERT INTO temp SELECT Job.JobId,JobTDate,Job.ClientId,Job.Level,
  ORDER BY Job.JobTDate DESC LIMIT 1;
 # Copy into temp 2 getting all volumes of Full save
 INSERT INTO temp2 SELECT Job.JobId,Job.StartTime,Media.VolumeName,Job.Level,
  ORDER BY Job.JobTDate DESC LIMIT 1;
 # Copy into temp 2 getting all volumes of Full save
 INSERT INTO temp2 SELECT Job.JobId,Job.StartTime,Media.VolumeName,Job.Level,

-   JobMedia.StartFile,Job.VolSessionId,Job.VolSessionTime
+  JobMedia.StartFile,Job.VolSessionId,Job.VolSessionTime

  FROM temp,Job,JobMedia,Media WHERE temp.JobId=Job.JobId
  AND Job.Level='F' AND Job.JobStatus='T'
  AND JobMedia.JobId=Job.JobId
  AND JobMedia.MediaId=Media.MediaId;
 # Now add subsequent incrementals
  FROM temp,Job,JobMedia,Media WHERE temp.JobId=Job.JobId
  AND Job.Level='F' AND Job.JobStatus='T'
  AND JobMedia.JobId=Job.JobId
  AND JobMedia.MediaId=Media.MediaId;
 # Now add subsequent incrementals

-INSERT INTO temp2 SELECT Job.JobId,Job.StartTime,Media.VolumeName,
-   Job.Level,JobMedia.StartFile,Job.VolSessionId,Job.VolSessionTime
+INSERT INTO temp2 SELECT DISTINCT Job.JobId,Job.StartTime,Media.VolumeName,
+  Job.Level,JobMedia.StartFile,Job.VolSessionId,Job.VolSessionTime

  FROM Job,temp,JobMedia,Media
  WHERE Job.JobTDate>temp.JobTDate 
  AND Job.ClientId=temp.ClientId
  AND Job.Level IN ('I','D') AND JobStatus='T'
  AND JobMedia.JobId=Job.JobId 
  FROM Job,temp,JobMedia,Media
  WHERE Job.JobTDate>temp.JobTDate 
  AND Job.ClientId=temp.ClientId
  AND Job.Level IN ('I','D') AND JobStatus='T'
  AND JobMedia.JobId=Job.JobId 

- AND JobMedia.MediaId=Media.MediaId
- GROUP BY Job.JobId;
+ AND JobMedia.MediaId=Media.MediaId;

 # list results
 SELECT DISTINCT VolumeName from temp2;
 !DROP TABLE temp;
 !DROP TABLE temp2;
 # list results
 SELECT DISTINCT VolumeName from temp2;
 !DROP TABLE temp;
 !DROP TABLE temp2;

-# 10
-:List Pool Attributes for a selected Pool:
+# 9
+:List Pool Attributes for a selected Pool

 *Enter Pool name:
 SELECT Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes
  FROM Pool
  WHERE Name='%1';
 *Enter Pool name:
 SELECT Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes
  FROM Pool
  WHERE Name='%1';

+# 10
+:List total files/bytes by Job
+SELECT count(*) AS Jobs,sum(JobFiles) AS Files,sum(JobBytes) AS Bytes,Name AS Job
+ FROM Job GROUP by Name;

 # 11
 # 11

-:List total files/bytes by Job:
-SELECT count(*) AS Jobs, sum(JobFiles) AS Files,
- sum(JobBytes) AS Bytes, Name AS Job
- FROM Job GROUP by Name
-# 12
-:List total files/bytes by Volume:
-SELECT count(*) AS Jobs, sum(JobFiles) AS Files,
- sum(JobBytes) AS Bytes, VolumeName
+:List total files/bytes by Volume
+SELECT count(*) AS Jobs,sum(JobFiles) AS Files,sum(JobBytes) AS Bytes,VolumeName

  FROM Job,JobMedia,Media
  WHERE JobMedia.JobId=Job.JobId
  AND JobMedia.MediaId=Media.MediaId
  GROUP by VolumeName;  
  FROM Job,JobMedia,Media
  WHERE JobMedia.JobId=Job.JobId
  AND JobMedia.MediaId=Media.MediaId
  GROUP by VolumeName;  

-# 13
-:List Files for a selected JobId:
+# 12
+:List Files for a selected JobId

 *Enter JobId:
 *Enter JobId:

-SELECT Path.Path,Filename.Name FROM File,
- Filename,Path WHERE File.JobId=%1 
+SELECT Path.Path,Filename.Name FROM File,Filename,Path WHERE File.JobId=%1 

  AND Filename.FilenameId=File.FilenameId 
  AND Path.PathId=File.PathId ORDER BY
  Path.Path,Filename.Name;
  AND Filename.FilenameId=File.FilenameId 
  AND Path.PathId=File.PathId ORDER BY
  Path.Path,Filename.Name;

-# 14
-:List Jobs stored in a selected MediaId:
+# 13
+:List Jobs stored in a selected MediaId

 *Enter MediaId:
 *Enter MediaId:

-SELECT Job.JobId,Job.Name,Job.StartTime,Job.Type,
- Job.Level,Job.JobFiles,Job.JobBytes,Job.JobStatus
-  FROM JobMedia,Job
-  WHERE JobMedia.JobId=Job.JobId
-  AND JobMedia.MediaId=%1 
-  GROUP BY Job.JobId ORDER by Job.StartTime;
-# 15  
-:List Jobs stored for a given Volume name:
+SELECT DISTINCT Job.JobId,Job.Name,Job.StartTime,Job.Type,
+  Job.Level,Job.JobFiles,Job.JobBytes,Job.JobStatus
+ FROM JobMedia,Job
+ WHERE JobMedia.JobId=Job.JobId
+ AND JobMedia.MediaId=%1 
+ ORDER by Job.StartTime;
+# 14  
+:List Jobs stored for a given Volume name

 *Enter Volume name:
 *Enter Volume name:

-SELECT Job.JobId as JobId,Job.Name as Name,Job.StartTime as StartTime,
+SELECT DISTINCT Job.JobId as JobId,Job.Name as Name,Job.StartTime as StartTime,

   Job.Type as Type,Job.Level as Level,Job.JobFiles as Files,
   Job.JobBytes as Bytes,Job.JobStatus as Status
   Job.Type as Type,Job.Level as Level,Job.JobFiles as Files,
   Job.JobBytes as Bytes,Job.JobStatus as Status

-  FROM Media,JobMedia,Job
-  WHERE Media.VolumeName='%1'
-  AND Media.MediaId=JobMedia.MediaId              
-  AND JobMedia.JobId=Job.JobId
-  GROUP BY Job.JobId ORDER by Job.StartTime;
+ FROM Media,JobMedia,Job
+ WHERE Media.VolumeName='%1'
+ AND Media.MediaId=JobMedia.MediaId              
+ AND JobMedia.JobId=Job.JobId
+ ORDER by Job.StartTime;
+# 15
+:List Volumes Bacula thinks are in changer
+SELECT MediaId,VolumeName,VolBytes/(1024*1024*1024) AS GB,Storage.Name 
+  AS Storage,Slot,Pool.Name AS Pool,MediaType,VolStatus
+  FROM Media,Pool,Storage
+  WHERE Media.PoolId=Pool.PoolId
+  AND Slot>0 AND InChanger=1
+  AND Media.StorageId=Storage.StorageId
+  ORDER BY MediaType ASC, Slot ASC;
+# 16
+:List Volumes likely to need replacement from age or errors
+SELECT VolumeName AS Volume,VolMounts AS Mounts,VolErrors AS Errors,
+         VolWrites AS Writes,VolStatus AS Status
+  FROM Media
+  WHERE (VolErrors>0) OR (VolStatus='Error') OR (VolMounts>50) OR
+         (VolStatus='Disabled') OR (VolWrites>3999999)
+  ORDER BY VolStatus ASC, VolErrors,VolMounts,VolumeName DESC;