Following is code to Automate Raw Data Meassurement in Excel. Because by default EPNM doesnt have mechanism to export their own database to other.
EPNM store file database under /opt/CSCO/lumos/da/cdb , those files are EPNM application data files to store the performance data and it cannot be accessed from outside.
So following is script to export hourly data periodically via cron job to a csv file. And configured it in a way to get accessed via ftp.
cdbexport.sh : script to generate raw data files
ade # cat cdbexport.sh
#!/bin/bash
enddate="$(date -d "$(date +"%h %d %H:00:00")" +'%s')"
startdate=$(expr $enddate - 3600)
date -d @$startdate
echo StartTime=$startdate
echo EndTime=$enddate
/opt/CSCOlumos/da/bin/cdbq 'SELECT * FROM CPU WHERE TIME >= '$startdate' AND TIME < '$enddate'' >> /localdisk/ftp/cdbexport/CPU_"$(date +%Y-%m-%d-%H_00)".csv
/opt/CSCOlumos/da/bin/cdbq 'SELECT * FROM MEMORY WHERE TIME >= '$startdate' AND TIME < '$enddate'' >> /localdisk/ftp/cdbexport/Memory_"$(date +%Y-%m-%d-%H_00)".csv
/opt/CSCOlumos/da/bin/cdbq 'SELECT * FROM CEPMINTERFACE WHERE TIME >= '$startdate' AND TIME < '$enddate'' >> /localdisk/ftp/cdbexport/CEPMINTERFACE_"$(date +%Y-%m-%d-%H_00)".csv
/opt/CSCOlumos/da/bin/cdbq 'SELECT * FROM ICMPJITTER WHERE TIME >= '$startdate' AND TIME < '$enddate'' >> /localdisk/ftp/cdbexport/ICMPJITTER_"$(date +%Y-%m-%d-%H_00)".csv
/opt/CSCOlumos/da/bin/cdbq 'SELECT * FROM ENVTEMP WHERE TIME >= '$startdate' AND TIME < '$enddate'' >> /localdisk/ftp/cdbexport/ENVTEMP_"$(date +%Y-%m-%d-%H_00)".csv
/opt/CSCOlumos/da/bin/cdbq 'SELECT * FROM CEPMQOS WHERE TIME >= '$startdate' AND TIME < '$enddate'' >> /localdisk/ftp/cdbexport/CEPMQOS_"$(date +%Y-%m-%d-%H_00)".csv
/opt/CSCOlumos/da/bin/cdbq 'SELECT * FROM OpticalSFP WHERE TIME >= '$startdate' AND TIME < '$enddate'' >> /localdisk/ftp/cdbexport/OpticalSFP_"$(date +%Y-%m-%d-%H_00)".csv
/opt/CSCOlumos/da/bin/cdbq 'SELECT * FROM DVAVAILABILITY WHERE TIME >= '$startdate' AND TIME < '$enddate'' >> /localdisk/ftp/cdbexport/DVAVAILABILITY_"$(date +%Y-%m-%d-%H_00)".csv
/opt/CSCOlumos/da/bin/cdbq 'SELECT * FROM CEPMCRC WHERE TIME >= '$startdate' AND TIME < '$enddate'' >> /localdisk/ftp/cdbexport/CEPMCRC_"$(date +%Y-%m-%d-%H_00)".csv
ade #
Above script will generate raw data to excel in each hours. And for capacity issue we will need to remove old unused file that already created in periodic time.
cdbexport_clean.sh : script to remove old files
ade # cat cdbexport_clean.sh
find /localdisk/ftp/cdbexport -type f -mtime +15 -exec rm -rf {} \;
ade #
And we will need to put this script to startup configuration contrab.
15 * * * * /root/cdbexport.sh >> cdbexport.log 2>&1
30 6 * * * /root/cdbexport_clean.sh >/dev/null 2>&1
Catatan Randy 