#!/bin/bash # Tablespace check script # Author: Nabeel Moidu # Website: nmkuttiady.blogspot.com # Set threshold here THRESHOLD=70 # Set the Oracle user here ORACLE_USER=ora10g # Set the mail id of the DBA DBA="dba@organization.tld" # Get output of tablespace usage on the database su - $ORACLE_USER -c "sqlplus -s \"/ as sysdba\" < /backup/script/tablespace_usage.sql > /tmp/output.txt "
# Remove blank lines from file
cat /tmp/output.txt | grep -v ^$ | grep -v "no rows selected" > tablespaces.txt
# If no tablespace crosses threshold, the file will be empty. So send mail only if file not empty
if test -s "tablespaces.txt"
then
# Prepare the mail
echo "The following tablespaces have exceeded the 60 percent threshold" > mail.txt
#Send the mail with the hostname in subject
HOST=`hostname`
SUBJECT="Daily Tablespace Check - "$HOST
echo $SUBJECT
mail -s "$SUBJECT" $DBA < mail.txt
fi
This is the SQL query to get the tablespace output set pages 700 set linesize 1000 select t.tablespace_name,total_MB,round((total_MB-free_MB),2) used_MB, round(free_MB,2) free_MB,round(((total_MB-free_MB)/total_MB)*100,2) per_used,round((free_MB/total_MB)*100,2) per_free from (select tablespace_name,sum(bytes/1024/1024) free_MB from dba_free_space group by tablespace_name) f,(select tablespace_name,sum(bytes/1024/1024) total_MB from dba_data_files group by tablespace_name) t where f.tablespace_name(+)=t.tablespace_name order by 5;
Dim objADSysInfo,strUser,objUser,inputPhone,strEngInfo,strEngMsg,strAraInfo,strAraMsg,Result
Dim xmlhttp,httpURL,strDate,strMsg
' Function to display the retrieved information to a user and accept his modification
Function EnterExtension
'Collect AD information from system where the application is executed
Set objADSysInfo = CreateObject("ADSystemInfo")
'Retrieve username of the logged in user
strUser = objADSysInfo.UserName
'Lookup LDAP for other properties of the user
Set objUser = GetObject("LDAP://" & strUser)
'Display the retrieved information to the user
strEngInfo = "Your Name is " & objUser.CN & VbCrLf & VbCrLf & "Telephone Extension is " & objUser.ipPhone & "." & VbCrLf
strEngMsg = " If this is incorrect, enter your extension (only the last four digits Eg. 5159) and click OK. Otherwise click Cancel"
'Accept User's modified extension number
inputPhone=InputBox( strEngInfo & VbCrLf & strEngMsg & VbCrLf & VbCrLf )
End Function
'Function to Process the extension number entered by the user
Function ProcessExtension
'Upload the modified number and useraccount to a php application which can process it
httpURL = "http://servername.domain/TelExtension.php?UName=" + objUser.sAMAccountName + "&UExt=" + inputPhone
Set xmlhttp = CreateObject("Microsoft.XMLHTTP")
xmlhttp.Open "GET", httpURL, false
xmlhttp.Send
strDate = formatDateTime(Now(), vbLongDate)
strMsg = "Your request will be processed by end of " & strDate
MsgBox strMsg,64,"Information Systems Department"
End Function
'Function to trigger an email
Function SendMail
'Launch Outlook
Set MyApp = CreateObject("Outlook.Application")
Set MyItem = MyApp.CreateItem(0) 'olMailItem
With MyItem
.To = "admin@domain.tld"
' Properly set subject so as to filter out mail at the mail client
.Subject = "Request for Extension change :" + objUser.sAMAccountName + "," + inputPhone
.ReadReceiptRequested = False
' Compose body of the Email
.HTMLBody = objUser.sAMAccountName + " was previously " + objUser.ipPhone + " and needs to be modified to " + inputPhone
' Send the mail
.Send
End With
End Function
'Function to Check the validity of extension entered
Function CheckExtension
If IsEmpty(inputPhone) Then
MsgBox strEngInfo & VbCrLf & "Thank You for your cooperation",64,"Information Systems Department"
'This is expected if the user clicks Cancel, i.e. his extension in our records is correct. So Set Flag to 1
Result=1
If (inputPhone > 4000 And inputPhone < 4199) OR (inputPhone > 5000 And inputPhone < 5999) Then
ProcessExtension()
Result=1
Else
strMsg = "Please enter your extension again. Enter only the last four digits.For Eg. 5678 "
MsgBox strMsg,48,"UPDA Information Systems Department"
Result=0
End If
End If
End Function
' Main code Begins here
Result=0
'Run till the user enters a valid number or cancels
Do While Result=0
EnterExtension()
CheckExtension()
Loop
// Input of the format
// field1 in file1 (eg, 2446)
// field2,field1 in file2 (Nabeel, 2446)
$filename='file1.csv';
$handle = fopen("$filename", "r");
$ctr=0;
while (($read = fgetcsv($handle, 1000, ",")) !== FALSE){
$num[$ctr]= (int)$read[0];
// echo $num[$ctr]."
";
$ctr = $ctr + 1;
}
$filename1='file2.csv';
$handle1 = fopen("$filename1", "r");
$ctr1=0;
while (($rawname = fgetcsv($handle1, 1000, ",")) !== FALSE)
{ $ext[$ctr1]= (int)$rawname[0];
$name[$ctr1]= $rawname[1];
// echo $ext[$ctr1]."
";
$ctr1 = $ctr1 + 1;
}
for ($n=0; $n <= $ctr1; $n++) { $match[$n]=1; }
for ($x=0; $x <= $ctr; $x++)
{
for ($y=0; $y <= $ctr1; $y++) {
if ($num[$x]==$ext[$y]){$match[$y]=0;}
else {};
}
}
for ($y=0; $y <= $ctr1; $y++) {
if ($match[$y])
{ echo $name[$y]." | ".$ext[$y]."
";}
else {};
}
fclose($handle);
fclose($handle1);
?>
// Open file handles for input and output file
$ip_handle = fopen('input.csv', 'r');
$op_handle = fopen('output.ldif','w');
$server="xx.xx.xx.xx";//Enter DC ip here
$basedn="DC=domain,DC=tld";
//User to bind to the active directory to search for the users, can be any ordinary user
$bindname="CN=binder,OU=Users,DC=domain,DC=tld";
// Read from csv, with the comma , as the delimiter
while (($rawname = fgetcsv($ip_handle, 1000, ",")) !== FALSE)
{
$searchname=$rawname[1]; // First column is the useraccount
$extension=$rawname[2]; // Second column is the extension number
$filter="sAMAccountName=".$searchname; // search filter set as per the first column
// Attempt to connect to the LDAP server
if (!($connect = ldap_connect($server))) { die ("Could not connect to LDAP server"); }
ldap_set_option($connect, LDAP_OPT_PROTOCOL_VERSION, 3);ldap_set_option($connect, LDAP_OPT_REFERRALS, 0);
// Test if the user can be bound to the LDAP
if (!($bind = ldap_bind($connect, $bindname, "passwd"))) { die ("Could not bind to $bindname"); }
// Search for the user
$result = ldap_search($connect, $basedn,$filter);
if ($result) {
$info = ldap_get_entries($connect, $result);
for ($i=0; $i<$info["count"]; $i++)
{
// Retrieve user information from the result
$userdn = $info[$i]["dn"];
$usersamname = $info[$i]["samaccountname"][0];
$usertelno = $info[$i]["telephonenumber"][0];
$useript = $info[$i]["ipphone"][0];
// Echo output to screen and to file. If phone number exists, set changetype to modify, else to add
if ($usertelno) {
echo "
dn: ".$userdn."
changetype: modify
replace: telephonenumber
telephonenumber: ".$extension."
-
";
fwrite($op_handle, "\ndn: ".$userdn."\nchangetype: modify\nreplace: telephonenumber\ntelephonenumber: ".$extension."\n-\n");
}
else {
echo "
dn: ".$userdn."
changetype: modify
add: telephonenumber
telephonenumber: ".$extension."
-
";
fwrite($op_handle, "\ndn: ".$userdn."\nchangetype: modify\nadd: telephonenumber\ntelephonenumber: ".$extension."\n-\n");
}
if ($useript) {
echo "replace: ipPhone
ipPhone: ".$extension ;
fwrite($op_handle, "replace: ipPhone\nipPhone: ".$extension."\n" );
}
else {
fwrite($op_handle, "add: ipPhone\nipPhone: ".$extension."\n" );
echo "add: ipPhone
ipPhone: ".$extension ;
}
}
echo "
";
}
else {}
ldap_unbind($connect);
}
fclose($op_handle);
fclose($ip_handle);
// Output will be
//dn: CN=Name1,OU=Department,OU=Users,DC=domain,DC=tld
//changetype: modify
//replace: telephonenumber
//telephonenumber: 5266222
//-
//replace: ipPhone
//ipPhone: 5266222
?>