how to get email in excel format or macro?

[praveen..]

Hi,
I am using one scripts for the cpu / memory logs of last 24 hours using glance utility.

1. vi cpumem.sh

cd /opt/cpu_mem/cpumem_logs
touch cpumem.log

echo "CPU & Memory Utilization logs for last 24 Hours of Server " >> cpumem.log
echo "" >> cpumem.log
echo "----------------------------------------------------------------------------------" >> cpumem.log
echo " Date: Time: (%)CPU Used: (%)Memory Used: " >> cpumem.log
echo "----------------------------------------------------------------------------------" >> cpumem.log

cd /opt/cpu_mem/cpumem_logs
/opt/perf/bin/glance -adviser_only -j 600 -iterations 144 -syntax /opt/cpu_mem/cpumem.syn >> cpumem.log

echo " " >> cpumem.log
echo "----------------------------------------------------------------------------------" >> cpumem.log
echo "Monitoring of CPU and Memory done at `date` . " >> cpumem.log
echo "UPTIME= `uptime ` " >> cpumem.log
echo "----------------------------------------------------------------------------------" >> cpumem.log

mv cpumem.log cpumem.`date +%m%d%y`.log
cat /opt/cpu_mem/cpumem_logs/cpumem.`date +%m%d%y`.log | mailx -s "CPU/Mem logs" praveen@company.com



2. #vi cpumem.syn
print gbl_statdate, " ", gbl_stattime, " ", gbl_cpu_total_util, " ", gbl_mem_util


this mail is text formatted,

at present, output of this script is like....


CPU & Memory Utilization logs for last 24 Hours of Server

----------------------------------------------------------------------------------
Date: Time: (%)CPU Used: (%)Memory Used:
----------------------------------------------------------------------------------
08/20/2006 00:00:04 2.2 28.8
08/20/2006 00:10:04 4.9 28.8
08/20/2006 00:20:04 4.5 28.8
08/20/2006 00:30:04 4.7 28.8
08/20/2006 00:40:04 5.5 28.8
08/20/2006 00:50:04 5.7 28.8
08/20/2006 01:00:04 6.1 28.8
08/20/2006 01:10:04 6.1 28.8
08/20/2006 01:20:04 5.4 28.8
08/20/2006 01:30:04 5.4 28.8
08/20/2006 01:40:04 8.4 28.8
08/20/2006 01:50:04 8.4 28.8
:::::::::::::::::::::::::
:::::::::::::::::::::::::
:::::::::::::::::::::::::........


can i get all the logs in excel format , if yes, please help me ......

[Peter Godron]

Hi,
excel uses an internal format for full spreadsheets. The closest to 'excel format' in your case is probably CSV.
Write the output of your glance command to a temporary file and substitute spaces with commas (See "man tr"). You can then use this new file to load into excel, as long as it has a .csv filename extension.

[praveen..]

I tried with
# tr -cs test.log test.csv

But could not get the desired output

Please suggest

[Steven E. Protter]

Shalom,

excel will import ascii files with appropiate field delimters.

I put say | between every field, make sure the columns balance and when it hits excel import it comes out reasonable.

If you trust it, excel can be programmed to ssh onto the system and run the reports and capture the output.

SEP

[Peter Godron]

Hi,
# cat c
08/20/2006 00:00:04 2.2 28.8
08/20/2006 00:10:04 4.9 28.8
08/20/2006 00:20:04 4.5 28.8
08/20/2006 00:30:04 4.7 28.8
08/20/2006 00:40:04 5.5 28.8
# tr " " "," < c > d.csv
# cat d.csv
08/20/2006,00:00:04,2.2,28.8
08/20/2006,00:10:04,4.9,28.8
08/20/2006,00:20:04,4.5,28.8
08/20/2006,00:30:04,4.7,28.8
08/20/2006,00:40:04,5.5,28.8

[Arturo Galbiati · Accepted solution]

Hi Praveen,

1. check in your Xcel setup what's the separtor. Usually it should be ;

2. chnage the script cpumem.syn so:
print gbl_statdate, " ", gbl_stattime, ";", gbl_cpu_total_util, ";", gbl_mem_util
to obtain:
08/20/2006 00:00:04;2.2;28.8

3. change name of teh log file from .log to .csv

4. remove the comment header or change from:
----------------------------------------------------------------------------------
Date: Time: (%)CPU Used: (%)Memory Used:
----------------------------------------------------------------------------------
to:
Date;(%)CPU Used; (%)Memory Used

That's all.

N.B> Take care to use the same separtor defined in Xcell. In my example is ';'.

HTH,
Art

[praveen..]

Now I am getting the output in csv format using Arturo Galbiati' Solution


Thanks for your help