topic Re: how to get email in excel format or macro? in Operating System - HP-UX

how to get email in excel format or macro?

praveen.. — Tue, 07 Nov 2006 03:02:15 GMT

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 ......

Re: how to get email in excel format or macro?

Peter Godron — Tue, 07 Nov 2006 03:10:16 GMT

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.

Re: how to get email in excel format or macro?

praveen.. — Tue, 07 Nov 2006 07:08:59 GMT

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

But could not get the desired output

Please suggest

Re: how to get email in excel format or macro?

Steven E. Protter — Tue, 07 Nov 2006 07:26:02 GMT

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

Re: how to get email in excel format or macro?

Peter Godron — Tue, 07 Nov 2006 07:43:49 GMT

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

Re: how to get email in excel format or macro?

Arturo Galbiati — Wed, 08 Nov 2006 04:12:37 GMT

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

Re: how to get email in excel format or macro?

praveen.. — Thu, 09 Nov 2006 07:15:46 GMT

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

Thanks for your help