Wednesday, December 25, 2013

Excel: How To Reference Cells in Other Worksheets

For an Excel formula that needs to reference a cell in a different worksheet in the same workbook, use the following format for your formula.


SheetName!CellAddress (Precede the cell address with the worksheet name, and follow it with an exclamation point).

Note: For worksheet names that include one or more spaces, you will need to enclose it in single quotation marks. The following is a formula that refers to a cell on a sheet by the name of Monthly Sales:

=B4*’Monthly Sales’!A12
- See more at: http://www.tech-recipes.com/rx/1604/excel_how_to_reference_cells_in_other_worksheets/#sthash.4lmJrLfM.dpuf

Wednesday, December 18, 2013

While restarting network service my name servers in /etc/resolv.conf got vanished

While restarting network service my name servers in /etc/resolv.conf got vanished in CentOS

[root@Hostname ~]# /etc/init.d/network restart
Shutting down interface eth0:  Device state: 3 (disconnected)
                                                           [  OK  ]
Shutting down loopback interface:                          [  OK  ]
Bringing up loopback interface:                            [  OK  ]
Bringing up interface eth0:  Active connection state: activated
Active connection path: /org/freedesktop/NetworkManager/ActiveConnection/3
                                                           [  OK  ]


Solution:
While troubleshooting i noticed the box have "NetworkManager" installed. I removed it

[root@Hostname ~]# yum remove NetworkManager
It helped me to solve that issue.

Wednesday, November 6, 2013

zimbra apache not running

Issue: While accessing IP/domain name i can't get the zimbra mail login page.

Checked the zimbra apache and it seems running, but while nmap i can't see port 80 opened.
Checked iptables, selinux,/etc/host file etc

 Couple of .pid files in /opt/zimbra/log were owned by root instead of zimbra including zmmailboxmgr.pid. That prevented mailboxmgr to come up as user zimbra. Once thepermissions were fixed, everything was normal. Most like cause is that zimbra was restarted by user root somehow.
Changed the ownership to zimbra in /opt/zimbra/log
Stop all the zimbra services (zmcontrol stop)
Start all the zimbra services (zmcontrol start)

Monday, October 21, 2013

Redirect all http request to https

I got a request to redirect all http request in apache to https

Backup current httpd.conf file

Enable mod_rewitre

Add the following line in httpd.conf just below

<Directory "/var/www/html"> (In my case only one domain is hosted in that server)
RewriteEngine On
RewriteCond %{HTTPS} off
RewriteRule (.*) https://%{HTTP_HOST}%{REQUEST_URI} 


Restart httpd

How to install and configure SSL from godaddy in Centos6

First we need to generate CSR

openssl req -new -newkey rsa:2048 -nodes -keyout domainname.key -out domainname.csr

Rename domainname.key and .csr with your domain name

Copy the domainanme.csr and upload to godaddy page for certifiying.

It will take couple of days to get authorized and you will get a mail notification. Once this process finished login to your godaddy account and download the zip formatted certificates.

Upload the certificate.zip to the server.


Include ssl.conf in conf.d

vi /etc/httpd/conf.d/ssl.conf

SSLCertificateFile /etc/pki/tls/certs/5fjr5j9dw2f.crt (I get from godaddy)
SSLCertificateKeyFile /etc/pki/tls/private/domainname.key (I get intially from openssl command)
SSLCertificateChainFile /etc/pki/tls/certs/gd_bundle.crt (I get from godaddy)

Restart httpds

 

Tuesday, August 6, 2013

Who is eating my memory in linux

I have a OEL5.8 64bit having 16GB memory.
In free -m command i can see almost 15GB used, but no software are installed.

[root@Server-2 tmp]# ps axu | awk '{print $2, $3, $4, $11}' | head -1 && ps axu | awk '{print $2, $3, $4, $11}' | sort -k2 -nr |head -5
PID %CPU %MEM COMMAND
PID %CPU %MEM COMMAND
96 0.0 0.0 [scsi_eh_3]
95 0.0 0.0 [scsi_eh_2]
91 0.0 0.0 [scsi_eh_1]
9 0.0 0.0 [migration/2]

[root@Hawai-2 tmp]# ps -eo pmem,pcpu,vsize,pid,cmd | sort -k 1 -nr | head -5
 0.1  0.0  32780  2608 iscsiuio
%MEM %CPU    VSZ   PID CMD
 0.0  0.0  90152 24214 sshd: root@pts/1
 0.0  0.0   8528  3151 /usr/bin/hidd --server
 0.0  0.0  84456  3494 /usr/bin/Xorg :0 -br -audit 0 -auth /var/gdm/:0.Xauth -nolisten tcp vt7

[root@Hawai-2 tmp]# /etc/init.d/iscsid status
iscsid (pid  2617) is running...
[root@Hawai-2 tmp]# /etc/init.d/iscsid stop
Stopping iSCSI daemon:
[root@Hawai-2 tmp]#                                        [  OK  ]
[root@Hawai-2 tmp]#

After this i have rebooted the machine. Checked the free -m i can see a lot of free memory in the box.

Friday, August 2, 2013

How to allow open relay for a particular IP in QmailToaster

Open the file /etc/tcprules.d/tcp.smtp
Add the IP,
IP:allow,RELAYCLIENT=""
Save the file

tcprules /etc/tcprules.d/tcp.smtp.cdb /etc/tcprules.d/tcp.smtp.tmp < /etc/tcprules.d/tcp.smtp

This will allow relay from that IP.

Do you need to test this?

[root@bkserver1 ~]# telnet mailserver 25
Trying IP...
Connected to mailserver (IP).
Escape character is '^]'.
220 server.com - Welcome to Qmail Toaster Ver. 1.3 SMTP Server ESMTP
mail from:user@gmail.com
250 ok
rcpt to:user@yahoo.com
250 ok
data
354 go ahead
subject:This is a test
to:user@yahoo.com
from:user@gmail.com
This is the msg body of test
.
250 ok 1375419193 qp 22691
quit
221 server.com - Welcome to Qmail Toaster Ver. 1.3 SMTP Server
Connection closed by foreign host.
[root@bkserver1 ~]#

Tuesday, July 30, 2013

Mail Taping in Microsoft Exchange 2013

Requirement: I need to get one of my team mates send/receive mails with out knowing that user.
I am using Microsoft Exchange 2013 server

1.Login to ECP

2.Go to mail flow

3.In the rules tab add new rule

4.Select "send messages and save a copy for review "

5.Give a name for the rule

6.Select sender (for apply this rule if ..,condition)

7.Select the sender's mail box from the pop up window

8.In the do this condition drop down menu  select "BCC the message to "and select the  manager mail box.

9. At last save the settings

10.Done
   
Again for trapping  all received mails for the mail box create a new role (But select recipient in step 6)

Friday, July 12, 2013

How to configure Exchange 2013 email account in Thunderbird

Take proper backups of thunderbird (If currently using)
Upgraded Thunderbird to the latest 17.0.7
Install one plugin from https://addons.mozilla.org/en-us/thunderbird/addon/exquilla-exchange-web-services/
Restart Thunderbird
Add Email account from Tools>>Exquilla-For-Exchange server (After installing plugin you will get a document how to setup email account. Please follow that)

Checked Send/Receive.

It Works!!!

Wednesday, July 10, 2013

How to install To-Do plugin in Redmine

cd to your redmine vendor/plugins dir.

Git-clone the plugin from this repo into a folder in there (git clone git://github.com/dalyons/redmine-todos-scrum-plugin.git redmine_todos_plugin)

Run the plugin migrations +rake db:migrate_plugins+

Restart your Redmine web servers (e.g. mongrel, thin, mod_rails)

Login to your Redmine install as an Administrator

Setup the permissions for the todos module for your roles

Enable the module on a per-project basis as needed.

How to install chart plugin in Redmine

cd {REDMINE_ROOT} [for me its /var/www/redmine]
$ ./script/plugin install git://github.com/pullmonkey/open_flash_chart.git

$ cd {RAILS_ROOT}
$ git clone https://github.com/cou2jpn/redmine_charts.git vendor/plugins/redmine_charts

Restart apache

Login as admin, go to the project settings >>  Module tab >> We need to enable Charts

Friday, June 14, 2013

Space Usage Issue in Linux

Today i faced a new issue,
[root@bkserver1 ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda3             222G  218G     0 100% /
/dev/sda1              99M   11M   84M  11% /boot
tmpfs                 1.8G     0  1.8G   0% /dev/shm
I can see only 218G is using out of 222G still its showing 100% Usage.

[root@bkserver1 ~]# tune2fs -l /dev/sda3
Inode count:              59998208
Block count:              59974661
Reserved block count:     2998733
Free blocks:              1647082
Free inodes:              58882035
First block:              0
Block size:               4096

Solution :
[root@bkserver1 ~]# tune2fs -m 1 /dev/sda3
tune2fs 1.39 (29-May-2006)
Setting reserved blocks percentage to 1% (599746 blocks)
After this i tried df -h, i got the Available space count.

Tuesday, May 14, 2013

httpd: Could not reliably determine the server's fully qualified domain name, using localhost.localdomain for ServerName

In one of our Centos server, i can't restart httpd.
Error :
[root@localhost ~]# /etc/init.d/httpd restart
Stopping httpd:                                            [FAILED]
Starting httpd: httpd: Could not reliably determine the server's fully qualified domain name, using localhost.localdomain for ServerName
(98)Address already in use: make_sock: could not bind to address [::]:80
(98)Address already in use: make_sock: could not bind to address 0.0.0.0:80
no listening sockets available, shutting down
Unable to open logs
                                                           [FAILED]

In nmap i can see port 80 is opened by httpd. I tried to kill the process and httpd but it failed

[root@localhost ~]# netstat -ltnp | grep ':80'
tcp        0      0 0.0.0.0:80                  0.0.0.0:*                   LISTEN      1016/nginx

[root@localhost ~]# ps -wlp1016
F S   UID   PID  PPID  C PRI  NI ADDR SZ WCHAN  TTY          TIME CMD
5 S    99  1016     1  0  80   0 -  5497 ep_pol ?        00:00:00 nginx


[root@localhost ~]# fuser -k -n tcp 80
80/tcp:               1016  1017  1018  1019

[root@localhost ~]# netstat -ltnp | grep ':80'

[root@localhost ~]# /etc/init.d/httpd start
Starting httpd: httpd: Could not reliably determine the server's fully qualified domain name, using localhost.localdomain for ServerName
                                                           [  OK  ]
[root@localhost ~]#

Now i can start httpd in that server, later i noticed that in crontab

[root@localhost html]# crontab -l
3,18,33,48 * * * * killall -6 nginx httpd; sleep 1; /usr/local/nginx/sbin/nginx
[root@localhost html]#

I removed that entry and seems fine. Its like an attack from outside.

Tuesday, April 30, 2013

How to force comments on Subversion Commit with Perl hook script

Today i got a requirement from one of our senior developer to force a comments on svn commit.
We are using Subversion in linux.

We have to create a file pre-commit under /{repository-location}/hooks/
 

cd /{repository-location}/hooks/
touch pre-commit
chmod 775 pre-commit

Copy paste the following code in pre-commit file.

#!/usr/bin/perl

# config section
$minchars = 4;
$svnlook = '/usr/bin/svnlook';

#--------------------------------------------
$repos = $ARGV[0];
$txn = $ARGV[1];
$comment = `$svnlook log -t "$txn" "$repos"`;
chomp($comment);

if ( length($comment) == 0 ) {
  print STDERR "A comment is required!";
  exit(1);
  }
elsif ( length($comment) < $minchars ) {
  print STDERR "Comment must be at least $minchars characters.";
  exit(1);
  }

exit(0);

Tuesday, April 23, 2013

Sendmail not stating, some errors

While starting sendmail am getting some errors, and i can't see port 25 is opened.

[root@ip-10-190-51-120 ~]# /etc/init.d/sendmail start
Starting sendmail: -bd is not supported by sSMTP
                                                           [  OK  ]
[root@ip-10-190-51-120 ~]#

Fix: Uninstall ssmtp from the server

[root@ip-10-190-51-120 ~]# rpm -qa | grep ssmtp
ssmtp-2.61-19.el6.x86_64
[root@ip-10-190-51-120 ~]# rpm -e ssmtp-2.61-19.el6.x86_64
warning: /etc/ssmtp/ssmtp.conf saved as /etc/ssmtp/ssmtp.conf.rpmsave
[root@ip-10-190-51-120 ~]# rpm -qa | grep ssmtp
[root@ip-10-190-51-120 ~]#

This helps to solve this issue.

Friday, April 12, 2013

How to create a cron job with URL


Normally we have to give some commands at the end to execute cron jobs in a particular time. Today i need to execute the result of the url in cron job

01 04 * * * wget -O - -q -t 1 http://IP/Apps/Adminemail/admin_report

This helps.

If the site is SSL

wget -O - -q -t 1 https://domain.com/report --no-check-certificate

Monday, March 11, 2013

Yum not working in Oracle Linux

We have a new Oracle Linux 5.8 box. Yum is not working in that box.

[root@oraclelinux129 ~]# yum install compat-db
Loaded plugins: rhnplugin, security
This system is not registered with ULN.
ULN support will be disabled.
Setting up Install Process
No package compat-db available.
Nothing to do

Fix :

cd /etc/yum.repos.d
wget http://public-yum.oracle.com/public-yum-el5.repo
 
I need to edit the repo file to set enabled=1 for the correct 
channel [ol5_u7_base] and then I can install the oracle-validated RPM 
using yum
 
yum install oracle-validated -y
 
 

Sunday, March 3, 2013

badmailfrom

I need to block an email address in Qmailtoaster.(Incomming)

Take a backup of file /var/qmail/control/badmailfrom
If we add

shivinvijai@gmail.com
yahoo.com

Save
(No need to reload cdb)

Qmailserver will block any mail from shivinvijai@gmail.com and it will block any users from domain yahoo.com (user1@yahoo.com,user2@yahoo.com) etc...

Thursday, February 21, 2013

FFMPEG installation on Centos

I installed FFMPEG via. yum.

[root@ip-10-190-51-120 ~]# uname -a
Linux ip-10-190-51-120 2.6.32-279.2.1.el6.centos.plus.x86_64 #1 SMP Thu Jul 19 16:20:44 CDT 2012 x86_64 x86_64 x86_64 GNU/Linux
[root@ip-10-190-51-120 ~]#

[root@ip-10-190-51-120 ~]# cat /etc/redhat-release
CentOS release 6.3 (Final)
[root@ip-10-190-51-120 ~]#

rpm -Uvh http://apt.sw.be/redhat/el6/en/x86_64/rpmforge/RPMS/rpmforge-release-0.5.2-2.el6.rf.x86_64.rpm

Installing FFMPEG.

yum install ffmpeg ffmpeg-devel

Install FFMPEG-PHP Extension

FFmpeg-php is a very good extension and wrapper for PHP which can pull useful information about video through API interface. Inorder to install it you will need to download the source file and then compile and install extension in your server.

cd /usr/local/src

wget http://downloads.sourceforge.net/project/ffmpeg-php/ffmpeg-php/0.6.0/ffmpeg-php-0.6.0.tbz2?use_mirror=nchc

tar -xjf ffmpeg-php-0.6.0.tbz2
cd ffmpeg-php-0.6.0.tbz2
phpize
./configure
make
make install

Once you have done that without any problems then you will see the php extension file /usr/local/lib/php/extensions/no-debug-non-zts-20060613/ffmpeg.so and you will need mention that extension in php.ini file.

nano /usr/local/lib/php.ini

Put the below two lines at the end of the php.ini file

[ffmpeg]
extension=ffmpeg.so

Then restart apache using — service httpd restart

If you get an error like this 
/usr/src/ffmpeg-php-0.6.0/ffmpeg_frame.c: In function âzim_ffmpeg_frame_toGDImageâ:
/usr/src/ffmpeg-php-0.6.0/ffmpeg_frame.c:336: error: âPIX_FMT_RGBA32â undeclared (first use in this function)
/usr/src/ffmpeg-php-0.6.0/ffmpeg_frame.c:336: error: (Each undeclared identifier is reported only once
/usr/src/ffmpeg-php-0.6.0/ffmpeg_frame.c:336: error: for each function it appears in.)
/usr/src/ffmpeg-php-0.6.0/ffmpeg_frame.c: In function âzim_ffmpeg_frame_ffmpeg_frameâ:
/usr/src/ffmpeg-php-0.6.0/ffmpeg_frame.c:421: error: âPIX_FMT_RGBA32â undeclared (first use in this function)
make: *** [ffmpeg_frame.lo] Error 1

Under the ffmpeg-php-0.6.0 directory modify the file: ffmpeg_frame.c with nano or vi editor and replace every instance of PIX_FMT_RGBA32 with PIX_FMT_RGB32

# nano ffmpeg_frame.c
# Search for PIX_FMT_RGBA32 and replace it with PIX_FMT_RGB32
# Exit from the editor

Then run the following commands:

# cd /usr/local/src/ffmpeg-php-0.6.0
# cp -aP ffmpeg_frame.loT ffmpeg_frame.lo
# make clean
# ./configure
#   make
#   make install

This should fix the errors given above. Finally add the ffmpeg.so extension in php.ini and check phpinfo for the server you should see ffmpeg listed.


Installing Jira in Centos

Login as root
cd /opt
Download Jira for linux from http://www.atlassian.com/software/jira/download
chmod a+x atlassian-jira.bin
./atlassian-jira
I choose express install

Access URL http://ip:8080
/etc/init.d/jira start
/etc/init.d/jira stop

Then we have to run the wizard

I then created a mysql DB,user and password.
Give necessary permissions.

CREATE DATABASE jiradb CHARACTER SET utf8 COLLATE utf8_bin;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on <JIRADB>.* TO '<USERNAME>'@'<JIRA_SERVER_HOSTNAME>' IDENTIFIED BY '<PASSWORD>';
flush privileges;

Then we need to copy the mysql JDBC driver to jira
Download the driver from http://dev.mysql.com/downloads/connector/j
Untar the package
cd to that folder
Copy mysql-connector-java-5.x.x-bin.jar to /opt/jira/lib

Stop Jira
Start Jira

Give it as a public site
Give name of the site
Admin user details
Email notification etc.


Installing PhpMyAdmin in centos

yum install phpmyadmin

vi /etc/httpd/conf.d/phpMyAdmin.conf

Alias /phpMyAdmin /usr/share/phpMyAdmin
Alias /phpmyadmin /usr/share/phpMyAdmin

<Directory /usr/share/phpMyAdmin/>
Options Indexes FollowSymLinks MultiViews
AllowOverride all
Order Allow,Deny
Allow from all
Allow from 127.0.0.1
Allow from 50.16.104.91
</Directory>



Access url : http://ip/phpmyadmin , it will prompt for username/password. Enter your mysql root password.

While accessing the above url, you are getting this error in your apache error log

AH01630: client denied by server configuration: /usr/share/phpMyAdmin

Take a backup of your httpd.conf
Edit as follows

<Directory "/usr/share/phpMyAdmin">
    AllowOverride None
    Options None
    Require all granted
</Directory>
Restart httpd

Saturday, February 9, 2013

Can't login in hudson as administrator

Today i can't login to our hudson application as administrator. I am sure the password i tried is correct. I restarted tomcat and still the issue persists.

Error log :
INFO: Hudson is ready.
9 Feb, 2013 4:11:43 PM hudson.security.AuthenticationProcessingFilter2 onUnsuccessfulAuthentication
INFO: Login attempt failed
org.acegisecurity.BadCredentialsException: Bad credentials
        at org.acegisecurity.providers.dao.AbstractUserDetailsAuthenticationProvider.authenticate(AbstractUserDetailsAuthenticationProvider.java:122)
        at org.acegisecurity.providers.ProviderManager.doAuthentication(ProviderManager.java:195)
        at org.acegisecurity.AbstractAuthenticationManager.authenticate(AbstractAuthenticationManager.java:45)
        at org.acegisecurity.ui.webapp.AuthenticationProcessingFilter.attemptAuthentication(AuthenticationProcessingFilter.java:71)
        at org.acegisecurity.ui.AbstractProcessingFilter.doFilter(AbstractProcessingFilter.java:252)
        at hudson.security.ChainedServletFilter$1.doFilter(ChainedServletFilter.java:87)
        at org.acegisecurity.ui.basicauth.BasicProcessingFilter.doFilter(BasicProcessingFilter.java:173)
        at hudson.security.ChainedServletFilter$1.doFilter(ChainedServletFilter.java:87)

Solution :
I created a new user in hudson via. backend.
Logged in server.
cd /root/.hudson/users
Create a folder shivin and copy the config.xml file from /root/.hudson/users/Administrator folder
Go to /root/.hudson/
Edit config.xml file for giving privileges for the new user "shivin"
Once you give all permission, Stop tomcat and start again.
Try the web for login with the user "shivin" , you can
There go to People and select "Administrator", change password and save
Logout from user shivin and try with the new Administrator password

Issue fixed. I can login fine.

Tuesday, January 29, 2013

Error while accessing web sites


I am getting this error while accessing any sites. Issue solved by itself.
Solution : Login to the FG. UTM profiles > select the profile
Edit
Add a tick to Allow Websites when a rating error occurs.

Tuesday, January 22, 2013

Alfresco getting stoped while accesing pdf files from document libary

We have an Alfresco Community edition , version 3.4 installed on a Windows 2008 server. We are using this alfresco as our DMS. Yesterday we found some unusual issue in alfresco.
After login, we go for a particular site, select Document Libary, change folder , select PDF at that time alfresco got stopped. We tried to access the docs and xls files in the same site, but no issues, also we checked some other PDFs in other sites no issues.

Solution :
1. In Alfresco server we installed Open Office.
2. Stop Alfresco
3. C:\Alfresco\tomcat\webapps\alfresco\WEB-INF\lib i have updated fontbox-1.5.0.jar and pdfbox-1.5.0.jar from http://archive.apache.org/dist/pdfbox/ and http://archive.apache.org/dist/pdfbox/1.5.0/
(Old versions in the system are pdfbox-1.2.1.jar and pdfbox-1.2.1.jar)
4. Start Alfresco

Checked the document library, pdf and everything is working fine.
I spend 6 hours for fixing this issue.

Friday, January 18, 2013

Relaying mails in Sendmail

Today i noticed mails are not sending from our bugzilla application. Its a debian box,
first i checked the log files from /var/log/mail.info i can see DEFFERED unable to reach the server

Solution :
add a field called “SMART_HOST” in our Sendmail configuration file:
vi /etc/mail/sendmail.mc
and add the following line near the beginnnig of the file:
define(`SMART_HOST',`mail.server.com')
m4 /etc/mail/sendmail.mc > /etc/mail/sendmail.cf
/etc/init.d/sendmail restart
tail /var/log/mail.info

Thursday, January 17, 2013

Disabling New Account creation in bugzilla

After installing bugzilla, in the home page itself we can see "New Account" creation link. For disabling this

1. Log in as an Administrator
2. Go to: Administration->Parameters->User Authentication
3. Scan down to the bottom of the screen till you find 'createemailregexp'.
4. Clear out the value in the box
5. Client "Save Changes"

Adding multiple ssl virtual hosting

For this we need to add another ip to the machine.

/etc/network/interfaces

auto eth0:1
iface eth0:1 inet static
address 192.168.1.122
netmask 255.255.255.0

/etc/init.d/networking restart
Check ipconfig -a

/etc/apache2/ports.conf
NameVirtualHost *:80
Listen 80

<IfModule mod_ssl.c>
    # If you add NameVirtualHost *:443 here, you will also have to change
    # the VirtualHost statement in /etc/apache2/sites-available/default-ssl
    # to <VirtualHost *:443>
    # Server Name Indication for SSL named virtual hosts is currently not
    # supported by MSIE on Windows XP.

    Listen 192.168.1.121:443
    Listen 192.168.1.122:443
    NameVirtualHost 192.168.1.121:443
    NameVirtualHost 192.168.1.122:443
</IfModule>

<IfModule mod_gnutls.c>
    Listen 443
</IfModule>

vi /etc/apache2/sites-enabled/000-default-ssl
<Directory /var/www>
 Options +ExecCGI Indexes FollowSymLinks MultiViews
 AddHandler cgi-script cgi pl
 AllowOverride Limit
 DirectoryIndex index.cgi
 Order allow,deny
 allow from all
</Directory>

how to disable or turn off the ssl_redirect option in bugzilla

Bugzilla setup I made a rookie mistake of enabling SSL without any SSL mods loaded on Apache. Now i can't access Bugzilla site using either https or http
how to disable or turn off the ssl_redirect option

Login to the server
cd /var/www/bugzilla (Your bugzilla document root)
Edit your data/params file, change ssl_redirect to 0.

This helps.