Forum Discussion

5 Replies

Sort By
  • JaWie's avatar
    JaWie
    Level 2
    11 years ago

    I need a SQL Query for a Appointment - which remembers me on the first of the Month to save my Tapes.

     

    Cheers 

  • tom_sprouse's avatar
    tom_sprouse
    Level 6
    11 years ago

    Guten Abend JaWie, 

    Ich bin nicht in der Lage zu bestimmen, was Sie erwarten, die im Bericht zu sehen. 

    Wir können Setup ein SQL-Skript, um Daten aus der Datenbank ziehen OpsCenter. 

    Sobald wir das SQL-Skript erstellt wurde, können wir es als ein Private / Public Report speichern und dann planen sie von OpsCenter einmal im Monat laufen (erste oder letzte Tag des Monats) 

    Im Wesentlichen gibt es 2 Teile ... 

    1) Welche Daten wollen Sie OpsCenter zu berichten? 
    2) Speichern und planen diesen Bericht aus der OpsCenter Web-UI 

    Wenn Sie irgendwelche Fragen haben, antworten Sie bitte. 
    Es tut mir leid, wenn diese Meldung nicht übersetzen und ich verwendet habe, Google-Translate. 

    Danke, 

    --Tom Sprouse

  • JaWie's avatar
    JaWie
    Level 2
    11 years ago

    Hallo Tom !
     

    It just should send a Mail for a reminder.

     

    Maybe something like - Today you need to backup !

    Just a simple mail.

    I know a bit to use the Ops Center - how to schedule and so on.

     

    Just need the SQL Skript.

     

     

    Cheers !

     

  • JaWie's avatar
    JaWie
    Level 2
    11 years ago

    select "nom_NBMedia"."MediaID" as "MediaID" , "nom_NBMedia"."VmPool" as "Volume Pool" , tz_adjust_timestamp ( "nom_NBMedia"."AllocatedDate" , 3600000, 3600000 ) as "AllocatedDate"  from nom_NBMedia where tz_adjust_timestamp ( "nom_NBMedia"."AllocatedDate" , 3600000, 3600000 ) >= NOMTimeToUTCBigint(dateadd( day, -5, current timestamp )) AND tz_adjust_timestamp ( "nom_NBMedia"."AllocatedDate" , 3600000, 3600000 ) <= NOMTimeToUTCBigint(current timestamp ) AND "Volume Pool" = 7  

     

     

    thats what ive currently have..

     

    i think here is the problem, if im changing "NOMTimeToUTCBigint" to "UtcBigIntToNomTime" it works, but there is no result.

     

    please please help me.

     

    cheers

     

  • tom_sprouse's avatar
    tom_sprouse
    Level 6
    11 years ago

    Jawie,

    Please try the following:

    SELECT 
    MediaID AS 'Media ID',
    VolumePoolName AS 'Volume Pool',
    utcbiginttonomtime(AllocatedDate) AS 'Date Allocated'
FROM nom_NBMedia
WHERE DATEDIFF(day, UtcBigIntToNomTime(AllocatedDate), GETDATE()) <= 5

    The end of the last line ' <= 5 ' is the number of days to go back... If you need more then 5 days, increase the value.  
    I pulled this number from the script above... 

    dateadd( day, -5, current timestamp

    I also changed the 3 line, to bring back the Volume Pool Name, instead of the number.

    If you still need to filter on vmPool = 7, then use the following script.

    SELECT 
    MediaID AS 'Media ID',
    VolumePoolName AS 'Volume Pool',
    utcbiginttonomtime(AllocatedDate) AS 'Date Allocated'
FROM nom_NBMedia
WHERE DATEDIFF(day, UtcBigIntToNomTime(AllocatedDate), GETDATE()) <= 5
AND vmPool = 7

     

    If that fails to return any data, perhaps your vmpool ID is wrong.

     

    --Tom