Filter tsm reprot

hi ,
i run this script -
#!/bin/bash -x
ID=test
PAS=test
HOST=$HOSTNAME
TODAY=$(date +"%Y.%m.%d")
tsm_output=/tmp/status.$TODAY.$HOST.txt
cat </dev/null> $tsm_output
/usr/bin/dsmadmc -id=$ID -passw=$PAS  << EOF >> $tsm_output
select cast(entity as char(18)) Node, -
cast(activity as char(6)) Task, -
date(start_time) Date, -
time(start_time) Time, -
successful -
from summary -
where activity='BACKUP' and -
    entity in ( 'NODENAME','NODENAME' ) -
and ( -
   (date(START_TIME)=date(current_timestamp-1 day) and hour(start_time)>=18) -
  or -
   (date(START_TIME)=date(current_timestamp) and hour(start_time)<8) -
      ) -
order by START_TIME
quit
EOF
/usr/bin/mutt -s "Missed/Failed schedule report" -a $tsm_output TEST@gmail.com < /dev/null

the result that i got is attached to this question

how can I filter so that the result look like this

NODENAME_NEW1   yes          
NODENAME_NEW2   yes          
NODENAME_NEW2   yes          
NODENAME_NEW1   no
NODENAME_NEW2   no

Thx
filter.txt
TSMILAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

woolmilkporcCommented:
Do you want to change the query itself so that its output is in the new format you posted, or do you want to leave the query result as it is and just filter it for sending?

If you want to keep the original output and just filter for sending add before the "/usr/bin/mutt" line:

awk '{printf "%-20s%-5s\n",$1,$NF}' $tsm_output > ${tsm_output}.tmp

and change the "mutt" command like this:

/usr/bin/mutt -s "Missed/Failed schedule report" -a ${tsm_output}.tmp TEST@gmail.com < /dev/null
rm ${tsm_output}.tmp
0
woolmilkporcCommented:
If you want to change the query itself:

#!/bin/bash -x
ID=test
PAS=test
HOST=$HOSTNAME
TODAY=$(date +"%Y.%m.%d")
tsm_output=/tmp/status.$TODAY.$HOST.txt
cat </dev/null> $tsm_output
/usr/bin/dsmadmc -id=$ID -passw=$PAS  << EOF >> $tsm_output
select cast(entity as char(18)) Node, -
successful -
from summary -

where activity='BACKUP' and -
    entity in ( 'NODENAME','NODENAME' ) -
and ( -
   (date(START_TIME)=date(current_timestamp-1 day) and hour(start_time)>=18) -
  or -
   (date(START_TIME)=date(current_timestamp) and hour(start_time)<8) -
      ) -
order by START_TIME
quit
EOF
/usr/bin/mutt -s "Missed/Failed schedule report" -a $tsm_output TEST@gmail.com < /dev/null

means: remove the 3 lines after "select cast(entity as char(18)) Node, -" and before "successful -"
0
TSMILAuthor Commented:
hi

this is not want i want to got

i attach the output file to here

i want to see if there nodename1 several  time = yes i want to display only one time

nodename1 yes
nodename1 yes
nodename1 no
nodename1 yes

 i want the display will be

nodename1 yes
nodename1 no
Ex.txt
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

woolmilkporcCommented:
Again: do you want a modified report, or do you want to keep the original output and just create a modified temporary file for sending?

Anyway, the modified report would look like this:

#!/bin/bash -x
ID=test
PAS=test
HOST=$HOSTNAME
TODAY=$(date +"%Y.%m.%d")
tsm_output=/tmp/status.$TODAY.$HOST.txt
cat </dev/null> $tsm_output
/usr/bin/dsmadmc -id=$ID -passw=$PAS  << EOF >> $tsm_output
select max(cast(entity as char(18))||'  '||successful) as  -
   "Node                Success"   -
from summary -
where activity='BACKUP' and -
    entity in ( 'NODENAME','NODENAME' ) -
and ( -
   (date(START_TIME)=date(current_timestamp-1 day) and hour(start_time)>=18) -
  or -
   (date(START_TIME)=date(current_timestamp) and hour(start_time)<8) -
      ) -
group by  entity||successful
quit
EOF
/usr/bin/mutt -s "Missed/Failed schedule report" -a $tsm_output TEST@gmail.com < /dev/null

On the other hand, if you need a modified, temporary file for sending please let me know!
0
TSMILAuthor Commented:
hi again

im sorry - what i want that even if node will appear several times in the report i will only see it one time  

this the original report - as you can see nodename 1 appear 4 times

nodename1 yes
nodename1 yes
nodename1 no
nodename1 yes

and this is what i want to display

nodename1 no
nodename1 yes
0
woolmilkporcCommented:
I know what you want.

Did you test the new report I posted in my previous comment?
0
TSMILAuthor Commented:
yes

it will show like this

nodename1 yes
nodename1 yes
nodename1 no
nodename1 yes
0
woolmilkporcCommented:
Sorry, cannot confirm. It works for me.

I mean this suggestion just above in comment # 40818748:

#!/bin/bash -x
ID=test
PAS=test
HOST=$HOSTNAME
TODAY=$(date +"%Y.%m.%d")
tsm_output=/tmp/status.$TODAY.$HOST.txt
cat </dev/null> $tsm_output
/usr/bin/dsmadmc -id=$ID -passw=$PAS  << EOF >> $tsm_output
select max(cast(entity as char(18))||'  '||successful) as  -
   "Node                Success"   -
from summary -
where activity='BACKUP' and -
    entity in ( 'NODENAME','NODENAME' ) -
and ( -
   (date(START_TIME)=date(current_timestamp-1 day) and hour(start_time)>=18) -
  or -
   (date(START_TIME)=date(current_timestamp) and hour(start_time)<8) -
      ) -
group by  entity||successful
quit
EOF
/usr/bin/mutt -s "Missed/Failed schedule report" -a $tsm_output TEST@gmail.com < /dev/null

Open in new window

Do you ever read any of my answers up to the end?
0
TSMILAuthor Commented:
maybe because the server get one time to backup
in my case is reach every 15 min because of this is will get the same node name  and status so much times

i want to display only unique node name and his status

if i got 3 times that the same node name got yes and no for backup status i want to display him only one time like

node name no and also the start time
node name yes and also the start time

i think that the only its work it if i can play with the display after the SELECT

hope you can help me
0
woolmilkporcCommented:
Sorry, I already told you - it works for me (without the start time).

You cannot reliably display the start time along with the desired output if you have more than one failed and/or more than one one successful run in the given interval.

Which of the start times is TSM supposed to select when there are several suucessful or failed runs for the same node?
0
TSMILAuthor Commented:
ok i will try this

which script work for you ?
0
woolmilkporcCommented:
Please read this comment up to its end!

This query (which doesn't have the start_times) works for me:

#!/bin/bash -x
ID=test
PAS=test
HOST=$HOSTNAME
TODAY=$(date +"%Y.%m.%d")
tsm_output=/tmp/status.$TODAY.$HOST.txt
cat </dev/null> $tsm_output
/usr/bin/dsmadmc -id=$ID -passw=$PAS  << EOF >> $tsm_output
select max(cast(entity as char(18))||'  '||successful) as  -
   "Node                Success"   -
from summary -
where activity='BACKUP' and -
    entity in ( 'NODENAME','NODENAME' ) -
and ( -
   (date(START_TIME)=date(current_timestamp-1 day) and hour(start_time)>=18) -
  or -
   (date(START_TIME)=date(current_timestamp) and hour(start_time)<8) -
      ) -
group by  entity||successful
quit
EOF
/usr/bin/mutt -s "Missed/Failed schedule report" -a $tsm_output TEST@gmail.com < /dev/null

Open in new window

                                     
We can display the lowest start_time of several successful runs and the lowest start time of several failed runs for the same node.

Here is the query showing these minimum start times (works for me,too):

#!/bin/bash -x
ID=test
PAS=test
HOST=$HOSTNAME
TODAY=$(date +"%Y.%m.%d")
tsm_output=/tmp/status.$TODAY.$HOST.txt
cat </dev/null> $tsm_output
/usr/bin/dsmadmc -id=$ID -passw=$PAS  << EOF >> $tsm_output
select max(cast(entity as char(18))||'  '||successful) as  -
   "Node                Success"   ,  -
min(date(start_time)||' '||time(start_time)) as START_TIME  -
from summary -
where activity='BACKUP' and -
    entity in ( 'NODENAME','NODENAME' ) -
and ( -
   (date(START_TIME)=date(current_timestamp-1 day) and hour(start_time)>=18) -
  or -
   (date(START_TIME)=date(current_timestamp) and hour(start_time)<8) -
      ) -
group by  entity||successful
quit
EOF
/usr/bin/mutt -s "Missed/Failed schedule report" -a $tsm_output TEST@gmail.com < /dev/null

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Storage

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.