Tuesday, 26 February 2013

Instance Monitor Script

#!/usr/bin/perl
use strict;
use DBI;
use Data::Table;
use MIME::Lite;

use Data::Dumper;

sub main() {
my ($i, @test, %hash);
my ($sid, $username, $password, $script, @args, $sid_stage);
my ($head_html, $tail_html, $table_html, $query_obj, $conn, $sql, $content);

$head_html = '';
$tail_html = '';


foreach $_ (sort `cat ARGS.env`) {
  ($sid, $username, $password, $script, @args) = split " ", $_;

  unless ($sid eq $sid_stage) {
   $sid_stage = $sid ;
   $table_html .= "

$sid Instance";
  }

  $conn = connect_db($sid, $username, $password);
  $sql = `cat $script`;
  $query_obj = Data::Table::fromSQL($conn, $sql);

  $table_html .=  "
" . $query_obj->html;
  $conn->disconnect();
}


$content = $head_html . $table_html . $tail_html;

send_mail($content);
}


sub connect_db()
{
  my ($sid, $username, $password) = @_;
  my $db = DBI->connect( "dbi:Oracle:$sid", "$username", "$password" )|| die( $DBI::errstr . "\n" );

  return $db;
}


sub send_mail(){
  my ($content) = @_;

  my $msg = MIME::Lite->new
              (
               Subject => "Instance Monitor",
               From    => ' kchandran@oneglobesystems.com',
               To      => ' dba@oneglobesystems.com',
               Type    => 'text/html',
               Data    => "$content"
              );

  $msg->send();
}

main();



Create blog script:

#!/usr/bin/perl
use strict;
use DBI;
use Data::Table;
#use MIME::Lite;

use Data::Dumper;

my $header= <<'EOF';
<HTML>


<HEAD>
<TITLE>Concurrent Request</TITLE>
<STYLE TYPE="text/css">
<!-- TD {font-size: 10pt; font-family: calibri; font-style: normal} -->
</STYLE>
</HEAD>


<BODY>
<script type="text/javascript">  
      function displayRow(row)
        {           if (row.style.display == '')
             row.style.display = 'none';  
          else row.style.display = '';  
        }
    function copyToClipboard(s)
{
    if( window.clipboardData && clipboardData.setData )
    {
        clipboardData.setData("Text", s);
    }
    else
    {
        // You have to sign the code to enable this or allow the action in about:config by changing
        user_pref("signed.applets.codebase_principal_support", true);
        netscape.security.PrivilegeManager.enablePrivilege('UniversalXPConnect');

        var clip = Components.classes['@mozilla.org/widget/clipboard;[[[[1]]]]'].createInstance(Components.interfaces.nsIClipboard);
        if (!clip) return;

        // create a transferable
        var trans = Components.classes['@mozilla.org/widget/transferable;[[[[1]]]]'].createInstance(Components.interfaces.nsITransferable);
        if (!trans) return;

        // specify the data we wish to handle. Plaintext in this case.
        trans.addDataFlavor('text/unicode');

        // To get the data from the transferable we need two new objects
        var str = new Object();
        var len = new Object();

        var str = Components.classes["@mozilla.org/supports-string;[[[[1]]]]"].createInstance(Components.interfaces.nsISupportsString);

        var copytext=meintext;

        str.data=copytext;

        trans.setTransferData("text/unicode",str,copytext.length*[[[[2]]]]);

        var clipid=Components.interfaces.nsIClipboard;

        if (!clip) return false;

        clip.setData(trans,null,clipid.kGlobalClipboard);    
    }
}  
</script>
EOF


my $tail= <<EOF;
</body>

</html>
EOF


my $db = DBI->connect( "dbi:Oracle:devr12", "apps", "apps" )|| die( $DBI::errstr . "\n" );

my $sql={'1'=>{'desc'=>'testing one','query'=><<'ONE'},'2'=>{'desc'=>'testing two','query'=><<'TWO'}};
SELECT          responsibility_name Responsibility_Name
FROM            fnd_request_groups frg,
                fnd_request_group_units frgu,
                fnd_concurrent_programs_vl fcpv,
                fnd_responsibility_vl frv
WHERE           frgu.request_unit_type = 'P'
AND             (UPPER(fcpv.concurrent_program_name) = UPPER('active users')
                OR
                UPPER(fcpv.user_concurrent_program_name) = UPPER('active users'))
AND             frgu.request_group_id = frg.request_group_id
AND             frgu.request_unit_id = fcpv.concurrent_program_id
AND             frv.request_group_id = frg.request_group_id
ORDER BY        responsibility_name
ONE
SELECT 'Pending' pphase, meaning status, count(*) numreqs
FROM fnd_concurrent_requests, fnd_lookups
WHERE LOOKUP_TYPE = 'CP_STATUS_CODE' AND lookup_code = status_code AND phase_code = 'P'
GROUP BY meaning
TWO

#print Dumper($sql);

my ($query_obj, $value, $content, $string, $table);
my @chars = ("A".."Z", "a".."z");

foreach (sort keys %$sql) {
$string = "";

$string .= $chars[rand @chars] for 1..8;

$query_obj = Data::Table::fromSQL($db, $sql->{$_}->{'query'});

$value = $query_obj->html(["","#ECECE4","#DEE6EF"]);

$table .=<<EOF;
<a href="#$string"> - $sql->{$_}->{'desc'} </a><br>
EOF

$sql->{$_}->{'query'} =~ s/\r?\n/<br>/g;

$content .= <<EOF;
<a name="$string"></a><B><U>$sql->{$_}->{'desc'}</B></U><BR>
<BR><b>Description:</b>
    <BR> Displays the E-Business Suite Version Information for the system being examined.<BR>

<BR><b>Action:</b>
    <BR> The output provided is for review and confirmation by your teams, and documents the current baseline versions for the system<BR>
<BR>

<TABLE border="1" cellspacing="0" cellpadding="2">

<TR bgcolor="#DEE6EF" bordercolor="#DEE6EF">
<TD COLSPAN=4 bordercolor="#DEE6EF"><font face="Calibri"><a name="$string"></a>
<B>$sql->{$_}->{'desc'}</B></font>
</TD>
<TD bordercolor="#DEE6EF">
<div align="right"><button onclick="displayRow(document.getElementById('$string'))" >SQL Script</button></div>
</TD>

<td bordercolor="#DEE6EF">
<div align="right"><button onclick="copyToClipboard(document.getElementById('$string txt').innerHTML)">Copy</button></div>
</td>
</tr>
<TR id="$string" style="display:none">
<TD BGCOLOR=#DEE6EF colspan="6" height="60">
<blockquote><p align="left" id="$string txt">
$sql->{$_}->{'query'}
</blockquote>
</TD>
</TR>

$value
                                                                                                                      
</TABLE><P><P>
<A href="#top"><font size="-1">Back to Top</font></A><BR><BR>

EOF
}

my $index=<<EOF;
<table width="95%" border="0">
<tr>
<td colspan="4" height="46">
<p><a name="top"><b><font size="+2">Table of Contents</font></b></a> </p>
</td>
</tr>
<tr>
<td width="50%">
<p>
<br>
<blockquote>
$table
</p>
</td>
</tr>
</table>
EOF

print $header.$index.$content.$tail;

__END__
my $msg=MIME::Lite->new
              (
               Subject => "Testing blog",
               From    => ' karthikeyanc.gdc@petrofac.com',
               To      => ' karthikeyanc.gdc@petrofac.com',
               Type    => 'text/html',
               Data    => "$header.$content.$tail"
              );
$msg->attach(Type    => 'text/html',
               Data    => "$header.$content.$tail");
$msg->send();

No comments:

Post a Comment