1. Create Maintenance Expiry Date field under Asset Tab

2. Create Email Notification using Maintenance Expiry Date field

3. Select "Maintenance Expiry < DATE_ADD(CURDATE(), INTERVAL 30 DAY)" for notification of maintenance expiry in 30 days

On clicking "Create Notification",  Reporting Email Alerts page will be displayed

Select the email alerts and select the run frequency, hourly, daily, weekly or monthly.

You will notice that apostrophe has been added in the front and back of the string of characters you supplied earlier, "DATE_ADD(CURDATE(), INTERVAL 30 DAY)",  Remove the 2 apostrophes as they turn the date into a character string that invalidates arithmetical comparison

CrystalReports is not too into the open source scene, consequently it doesn’t work with MySQL out of the box. But you can easily get the Crystal Reports Designer to work against a MySQL database by using JDBC.

1. Download the MySQL Connector J jar file. That download should contain a jar file that looks something like:
   mysql-connector-java-3.1.14-bin.jar
2. Add the location of your newly downloaded jar file to the Classpath, as defined in CrystalReports CRConfig.xml file. On a Windows machine, the config file will be located somewhere like:
   C:\Program Files\Business Objects\Common\3.5\java\CRConfig.xml
3. Once you have altered your CRConfig.xml, close and reopen Crystal Reports.
4. From the menu: File -> New -> Standard Report
5. In the “Available Data Sources” list, double-click to expand “Create New Connection”
6. Double-click to expand “JDBC (JNDI)”
7. Double-click “Make New Connection”
8. Connection URL: “jdbc:mysql://db.example.com/dbname” (Use your own database host name and db name).
9. Database Classname: “com.mysql.jdbc.Driver”
10. Click “Next”
11. Enter a database user/password combination when prompted.
12. You should now be able to inspect the tables/columns in the database to begin reporting.

If the connection is not allowed, make sure you are granted access settings in MySQL, allowing you to connect from wherever you are. Try connecting using the vanilla MySQL command line client.

I've been gettng asked for this a lot in my trainings. Here is a machine report for all labels belonging to the "Locations" label group.

Hopefully you can get some use out of it.

SELECT M.IP, 
       M. NAME, 
       M.USER_FULLNAME, 
       M.USER_LOGGED, 
       M.CS_MODEL, 
       M.CS_MANUFACTURER, 
       M.SYSTEM_DESCRIPTION, 
       M.OS_NAME, 
       M.SERVICE_PACK, 
       CHILD. NAME AS LABEL_NAME 
FROM   LABEL CHILD, 
       LABEL_LABEL_JT LL, 
       LABEL PARENT, 
       MACHINE M, 
       MACHINE_LABEL_JT ML 
WHERE  CHILD.ID = LL.CHILD_LABEL_ID 
   AND LL.LABEL_ID = PARENT.ID 
   AND M.ID = ML.MACHINE_ID 
   AND ML.LABEL_ID = CHILD.ID 
   AND PARENT. NAME = 'LOCATIONS' 
ORDER  BY CHILD. NAME, 
          M. NAME 

Here's som old SQL that makes a nice little table for the sat survery in the K1.

My idea was that this could be pasted into excel really easy so you could create charts, etc...

SELECT Coalesce(FULL_NAME, 'UNASSIGNED')          AS NAME, 
       Max(IF(SATISFACTION_RATING = 1, COUNT, 0)) AS 'POOR', 
       Max(IF(SATISFACTION_RATING = 2, COUNT, 0)) AS 'Below Average', 
       Max(IF(SATISFACTION_RATING = 3, COUNT, 0)) AS 'Average', 
       Max(IF(SATISFACTION_RATING = 4, COUNT, 0)) AS 'Good', 
       Max(IF(SATISFACTION_RATING = 5, COUNT, 0)) AS 'Excellent', 
       Max(IF(SATISFACTION_RATING = 0, COUNT, 0)) AS 'Not Rated' 
FROM   (SELECT U.FULL_NAME, 
               T.SATISFACTION_RATING, 
               COUNT(*) AS COUNT 
        FROM   HD_TICKET T 
               LEFT JOIN USER U 
                 ON T.OWNER_ID = U.ID 
        GROUP  BY U.FULL_NAME, 
                  T.SATISFACTION_RATING) RAW 
GROUP  BY FULL_NAME 
ORDER  BY NAME 

I hope this helps.

I made this report to make it easier to track the free space on the drives of replacation share machines that are used for replication share data.  Results are sorted so by free disk space with the lowest at the top to save time reviewing.

Just replace SERVER#/WINXP# and the corresponding drive letter you want to monitor in the code and you should be set. 

I have this set up in the Schedule Reports section so it gets emailed to me every morning for review.

Hopefully this can be useful for someone. 

Also, if anyone else has a better way of doing this, please let me know.  I can report on a label I have for my replication machines, but this pulls all of the drives and I only want/need to list the drive used for replication share data (for each machine).  Thus this approach.

John

______________________________________

*Title*
Replication Shares Drive Space

*Report Category*
Replication Shares (Custom)

*Description*
Lists available HDD space on HDD used for storing KBOX Replication Share data (patches, scripts, MIs, etc).  
Monitor daily to ensure HDD doesn't fill up as this would prevent updates from working.

*SQL Select Statement*
SELECT MACHINE.NAME AS SYSTEM_NAME,MACHINE_DISKS.DISK_FREE,MACHINE_DISKS.NAME AS DISK_NAME
FROM MACHINE_DISKS  
JOIN MACHINE ON (MACHINE.ID = MACHINE_DISKS.ID)
WHERE ((MACHINE.NAME = 'SERVER1') AND (MACHINE_DISKS.NAME like '%Drive T:%'))
OR ((MACHINE.NAME = 'SERVER2') AND (MACHINE_DISKS.NAME like '%Drive W:%'))
OR ((MACHINE.NAME = 'SERVER3') AND (MACHINE_DISKS.NAME like '%Drive F:%'))
OR ((MACHINE.NAME = 'WINXP1') AND (MACHINE_DISKS.NAME like '%Drive C:%'))
OR ((MACHINE.NAME = 'WINXP2') AND (MACHINE_DISKS.NAME like '%Drive C:%'))
OR ((MACHINE.NAME = 'WINXP3') AND (MACHINE_DISKS.NAME like '%Drive C:%'))
OR ((MACHINE.NAME = 'SERVER4') AND (MACHINE_DISKS.NAME like '%Drive E:%'))
OR ((MACHINE.NAME = 'SERVER5') AND (MACHINE_DISKS.NAME like '%Drive F:%'))
OR ((MACHINE.NAME = 'SERVER6') AND (MACHINE_DISKS.NAME like '%Drive E:%'))
OR ((MACHINE.NAME = 'WINXP4') AND (MACHINE_DISKS.NAME like '%Drive C:%'))
OR ((MACHINE.NAME = 'WINXP5') AND (MACHINE_DISKS.NAME like '%Drive C:%'))
OR ((MACHINE.NAME = 'SERVER7') AND (MACHINE_DISKS.NAME like '%Drive E:%'))
OR ((MACHINE.NAME = 'SERVER8') AND (MACHINE_DISKS.NAME like '%Drive D:%'))
OR ((MACHINE.NAME = 'WINXP6') AND (MACHINE_DISKS.NAME like '%Drive C:%'))
OR ((MACHINE.NAME = 'WINXP7') AND (MACHINE_DISKS.NAME like '%Drive C:%'))
ORDER BY MACHINE_DISKS.DISK_FREE

*Break on Columns*
<blank>