Showing posts with label Scripting. Show all posts
Showing posts with label Scripting. Show all posts

Custom Ant Task IsInList

I  had created this Custom Ant Task sometime ago while working on a project where I needed to check whether an item exists in the list. As I did not find any other efficient way to do it using any of the standard Ant tasks, I created one on my own. I'm publishing (see below GitHub project location) this Custom Ant Task source code as an Open Source. Feel free to use/modify/distribute it as per your need or suggest if you have any other better ways to do it.

What IsInList contains?
1) It contains one Java source file: com.sysgenius.tools.ant.customtask.IsInList.java

2) The GitHub project also has Ant build file build.xml to build the project from source code, sample-usage.xml - Ant build file that shows few usage scenarios of 'IsInList' task and README.txt that basically explains how to use it.

How to Use It?
Follow the steps below:

1) Make sure isinlist-<version>.jar file is in your build classpath. You can do it either by adding it into your $ANT_HOME/lib directory or by defining a custom library path like below and making a reference to it.

<path id="ant.opt.lib.path">
   <fileset dir="${basedir}/../target">
      <include name="isinlist-1.0.0.0.jar"/>
   </fileset>
</path>

2) Next, define the "isinlist" task, below is one of the few ways:

<typedef classname="com.sysgenius.tools.ant.customtask.IsInList" name="isinlist" classpathref="ant.opt.lib.path"/>

3) Use it, see the examples below:

Example 1:
You have a list of items like "ci;Inting.*;release;SystemOut_16.01.23.log;native_stdout.*;native_stderr.*" separated by ";". Here you need to find out whether or not any item starting with "native_stdout.log" exists. In this case you can do lookup using regular expression (isRegEx="true"). In your build file, you'll need to have:

<property name="item.list" value="ci;Inting.*;release;SystemOut_16.01.23.log;native_stdout.*;native_stderr.*"/>
<property name="regex.item.name" value="native_stdout.log"/>
<isinlist casesensitive="false" delimiter=";" value="${regex.item.name}" valueList="${item.list}" isRegEx="true"/>

Example 2:
You have a list of items like "ci;Inting.*;release;SystemOut_16.01.23.log;native_stdout.*;native_stderr.*" separated by ";".
Here you need to find out whether an item called "release" exists in the given list. In this case you can use regular lookup, meaning isRegEx="false".

<property name="item.list" value="ci;Inting.*;release;SystemOut_16.01.23.log;native_stdout.*;native_stderr.*"/>
<property name="regular.item.name" value="release"/>
<isinlist casesensitive="false" delimiter=";" value="${regular.item.name}" valueList="${item.list}" isRegEx="false"/>

See the sample-usage.xml for complete example and more detail usage scenarios.

You can  get/dowload files from GitHub location: https://github.com/pppoudel/customanttasks.
Posted by No comments:
Labels: , , , ,

Scripting Ideas

Important: This page will be continually updated as I find new work-around or ideas while working on scripts (any script - shell, windows, or others).

Running dos2unix in batch mode:


One of my teammates today seemed pretty frustrated while trying to run 'dos2unix' command in batch mode. His script (see below) was almost doing the thing, however instead of updating the file, the content were displayed on screen (stdout).

His script (with issue) that sent output to stdout:
find . -type f -name "*.sh" | xargs -i dos2unix {};
Here is the corrected script, which correctly updates each file under current directory by converting end of line from Windows format to Unix format.
find . -type f -name "*.sh" | xargs -i dos2unix {} {};
As you have noticed, the only thing missing was the last set of '{}', which basically tells dos2unix to use the same filename for output as per input. Below is example using 'exec' instead of 'xargs' to achieve the same.
find . -type f -name ".sh" -exec dos2unix {} {} \;
Command reference links: find, xargs,dos2unix

Using variable in SED:


file="myfile.txt";
replaceme="iamnew";
sed 's/iamold/'"${replaceMe}"'/g' < $file > $file".new";
OR
file="myfile.txt";
replaceme="iamnew";
sed "s/iamold/${replaceme}/g" < $file > $file".new";
Note: in above example, any occurrence of 'iamold' in 'myfile.txt' will be replace by 'iamnew' and written in 'myfile.txt.new'. Important thing here is the variable $replaceme should be in double quote. Below variant does not work. The variable '$replaceme' will not be expanded.
file="myfile.txt";
replaceme="iamnew";
sed 's/iamold/${replaceme}/g' < $file > $file".new";
Command reference links: sed


Finding which process owns/listens on which port


Here, I'm finding which process/process ID is listening on port 9080. Here is how I can find out.
Note: the following has been tested on CentOS Linux.

1) Using 'netstat -lnp'
$> netstat -lnp | grep 9080
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.) tcp6 0 0 :::9080 :::* LISTEN 3840/java


# using sudo:
$> sudo netstat -lnp | grep 9080
tcp6 0 0 :::9080 :::* LISTEN 3840/java


# Or find all ports in use by certain process/PID
$> sudo netstat -lnp | grep java
tcp6 0 0 :::9080 :::* LISTEN 3840/java
tcp6 0 0 :::10010 :::* LISTEN 3840/java
tcp6 0 0 :::9443 :::* LISTEN 3840/java
tcp6 0 0 127.0.0.1:57576 :::* LISTEN 3840/java

#by PID
$> sudo netstat -lnp | grep 3840
tcp6 0 0 :::9080 :::* LISTEN 3840/java
tcp6 0 0 :::10010 :::* LISTEN 3840/java
tcp6 0 0 :::9443 :::* LISTEN 3840/java
tcp6 0 0 127.0.0.1:57576 :::* LISTEN 3840/java

2) Using 'lsof -i :<port>'
$> lsof -i :9080
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
java 3840 osboxes 339u IPv6 40626 0t0 TCP *:glrpc (LISTEN)

3) Using ss -ntlp
$> ss -ntlp | grep 9080
LISTEN 0 128 :::9080 :::* users:(("java",pid=3840,fd=339))


Retrieving Certificate and Updating kestore file.


Following file show example of retrieving Google certificate from www.google.com and adding it to local key.jks file. script file: retrieveAndUpdateCert.sh

#! /bin/bash
# Remote host to retrieve certificate from
RHOST=www.google.com
# Remote port
RPORT=443
# key store file path
KS_FILEPATH=/opt/secrets/key.jks
# Certificate Alias
CERT_ALIAS=googlecert

# Retrieve the certificate and put in temporary file '/tmp/cert.crt' in this case.
# Refer to https://www.openssl.org/docs/man1.0.2/apps/openssl.html for openssl command details.
true | openssl s_client -connect ${RHOST}:${RPORT} 2>/dev/null | openssl x509 -in /dev/stdin > /tmp/cert.crt
# Install certificate using keytool
# keytool comes with Java.
# Refer to https://docs.oracle.com/javase/8/docs/technotes/tools/unix/keytool.html for keytool command details.
keytool -import -file /tmp/cert.crt -alias ${CERT_ALIAS} -keystore ${KS_FILEPATH} -storepass $1
# View certs in the keystore:
keytool -list -v -keystore ${KS_FILEPATH} -storepass $1

Run file as:
$> ./retrieveAndUpdateCert.sh <Your keystore password>


AWK numerical processing tricks



1. If you have number with 1000 separator (,) like 84,959, AWK fails to process the number correctly unless you remove the separator (,) from input. for example:
$> echo "84,959|34,600" | awk 'BEGIN{FS=OFS="|";}{print $1/1000,$2/1000}'
0.084|0.034
As seen from the above result, AWK only took the input values prefixed by comma. Fix is simple, just remove the "," from input value. The following line gives the correct result:

$> echo "84,959|34,600" | awk 'BEGIN{FS=OFS="|";}{gsub(",","",$1);gsub(",","",$2); print $1/1000,$2/1000}'
84.959|34.6

2. If you get some weird result while doing AWK numeric comparison, make sure the value is presented as number not string literal. For example:
$> echo "Is 99 ( ninety nine) higher than 100?" | awk 'BEGIN{FS="(";}{num=substr($1,4,2);if(num >= 100){ print num" is greater than 100"}else{print num" is less than 100"}}'
99 is greater than 100
As seen from above, the result is not correct/expected. It is because, the value of num above is '99 ', i.e. there is a space character after 99, and AWK processes this as string comparison. Simple fix is to multiply the value by 1 or add 0 before doing numeric comparison.
$> echo "Is 99 ( ninety nine) higher than 100?" | awk 'BEGIN{FS="(";}{num=substr($1,4,2)*1;if(num >= 100){ print num" is greater than 100"}else{print num" is less than 100"}}'
99 is less than 100
or
$> echo "Is 99 ( ninety nine) higher than 100?" | awk 'BEGIN{FS="(";}{num=substr($1,4,2)+0;if(num >= 100){ print num" is greater than 100"}else{print num" is less than 100"}}'
99 is less than 100

AWK printing from specific column/field to the end

In the following example, matrix.csv (comma delimited file) data is piped to awk which processes one row at a time (excluding first header row), first column is a time in milliseconds, so it converts into displayable date and prints, but rest of the columns (starting from 2nd column) require no processing, so it prints as it is.

cat matrix.csv | awk 'BEGIN{FS=OFS=","}{if(NR > 1) {print strftime("%c", ($1 + 500)/1000), substr($0, index($0,$2))}}'

Using comma as a delimiter in for loop

By default 'for loop' expects input delimited by space (or tab or newline) character. However, if you need to use ',' (comma), one of the easiest way is to override Internal Field Separator (IFS) value. However, make sure to set it back to the original value. See the script below, it opens a set of firewall ports delimited by comma ','. Before the for loop, we set IFS="," and after the for loop, we set value back to space " ".

#!/bin/sh
tcp_ports="179,443,80,2375,2376,2377,2380,4001,4443,4789,6443,6444,7001,7946,8080,10250,12376-12387"
udp_ports="4789,7946"

openFW() {
  IFS=",";
 for _port in $1; do
  echo "Opening ${_port}/$2";
  sudo firewall-cmd --permanent --zone=public --add-port=${_port}/$2;
 done
 IFS=" ";
}

openFW "${tcp_ports}" tcp;
openFW "${udp_ports}" udp;

# Recycle firewall
sudo firewall-cmd --reload

Posted by No comments:
Labels: , ,

Updating Property Files using Shell Script

Recently, one of my colleagues asked me some hints on how to read updated properties from one property file, search the same property in the target property file (files) and update/replace the corresponding property value (s) in the target property file (s) if property key matches. He wanted to do this operation using just the regular shell script, so No ANT or Java or Perl. I've put together a simple script file for him and decided to publish it here so that other people with similar needs can be benefited as I did not find any other similar posting.


Let's say, here is how the source property file looks like:

--------- sFile.properties --------
# Updated property values
connection.username=mssqluser
connection.password=*********
connection.hostname=thishost.domain
connection.time.format=yyyy-MM-dd HH:mm:ss

Let's say, here is how one of the target property files looks like:

--------- tFile.properties --------
# Connection properties
connection.username=xyz
connection.password=abc456789
connection.hostname=localhost

 connection.time.format =yyyy-MMM-dd HH:mm:ss 
 

Here is how the shell script file look like:

---------- propertyUpdater.sh ------

#!/bin/bash
# Source input property file that provides new/updated property values.
sFile='/cygdrive/c/temp/Bscripts/sFile.properties'

 # Target directory where multiple property files whose property value(s) need to be updated/replaced.
tDir='/cygdrive/c/temp/Bscripts/props'

 # Reads each line and assigns value to variable _line, excluding all commented (starting with #) lines and all empty lines
for _line in `cat "$sFile" | grep -v -e'#' | grep -v -e'^$'`; do
    echo "Reading line: $_line from source file: $sFile"
    _key=`echo $_line | cut -d '=' -f1`
    _value=`echo $_line | cut -d '=' -f2`
    echo "Retrieved property key: $_key with value: $_value"
    # Comment following 'for' loop if you are using 'tFile' variable.
    for _file in `find $tDir -type f -print | grep ".properties$"`; do
       echo "Updating target property file: $tDir/$_file"
       sed -i "s/^$_key=.*/$_line/g" "$tDir/$_file"
       # for those properties which have space between key and '=' sign.
       sed -i "s/^$_key[ \t]=.*/$_line/g" "$tDir/$_file"
    done   
done


--------- tFile.properties after the update -----

# Connection properties
connection.username=mssqluser
connection.password=*********
connection.hostname=thishost.domain
connection.time.format=yyyy-MM-dd HH:mm:ss
 

Hope, it helps!
Posted by 1 comment:
Labels: , ,

Quick Data Verification Tool

Recently, I had to put together a tool to verify data (in Microsoft SQL server database tables) in no time. Even though the time was very short, I had few requirements to follow while creating the tool as guidance. Those requirements were:
  1. Flexibility -  so that the tool is usable to more than one projects with little or no modifications.
  2. Lightweight - no use of third party scripting framework like Ant/Nant and no use of programming languages like Java , c# etc.
Since the tool was intended specifically for Microsoft SQL Server on Windows environments, I decided to use batch scripting, SQLCMD (http://technet.microsoft.com/en-us/library/ms162773.aspx), and SQL scripting.

In order to make it flexible and not to hard code anything (like Servername, database name, table name etc), so that same SQL script could be used from one project to another for similar type of verification with little or no modification, I decided to externalize these attributes in a ';' delimitted .csv file. Each line of my .csv file followed the following format:
<db-connection-info>;<db-name>.<table-name>;[<verification-criteria-expected-row-count>];[<other-criteria-1>];[<other-criteria-2>];[<column-name-1>];[<column-name-2>];[…]

Now, I had to put together a batch script file that could read each line as a row from .csv file, split it into columns and create and assign those values to corresponding variables into SQLCMD in order to pass to the SQL script during execution. 
I've used 'for' loop in the batch script to realize it. 'For' loop allows you to choose delimiter character, skipping any line(s) on the top of the file and what columns to read.  Here is a fragment of batch script file:
for /F "tokens=1-7 skip=1 delims=;" %%c in (%dataFile%) do (
   sqlcmd -S %%c -i %iSqlFiles% -v tableName="%%d" -v xRowCnt=%%e  -v col1-cond1=%%f -v col2-cond1=%%g -v col1Name="%%h" -v col2Name="%%i"  >> %oLogFile%
)

In this example, it skips the first line of .csv file (passed as variable %dataFile%), reads column (token) 1 to 7 from each line delimited by ';' and assigns to variable starting from letter 'c'. And also it executes SQLCMD command with the given options and provided SQL file '%iSqlFile%'.

One catch here is that the output file. Even though, the SQLCMD has -o <output-file> option, I did not use it here because this option does not allow to append into the existing file. If you have multiple sqlcmd command executing in a loop they either create new file each time deleting the previous one or create multiple files given the output file are unique for each execution. The easy out way out is just to redirect using stream redirection symbol '>>'.


The final part was to create the re-usable (kind of template) SQL script file. Fragment below shows how variable passed from SQLCMD command can be used in SQL script and without hard-coding the database name, table name, column name etc.

SELECT @CurrRowCount=count (*) FROM $(tableName) where $(tableName).$(col1Name) NOT LIKE '$(col1-cond1)' and $(tableName).$(col2Name) NOT LIKE '$(col2-cond1)';


I guess, the idea is clear, below you can find more details of my .csv file, batch script file and SQL script files.

Below are the complete file, which you may be able to use:
  • .csv file: testcases.csv
#<db-connection-info>;<db-name>.<table-name>;[<verification-criteria-expected-row-count>];[<other-criteria-1>];[<other-criteria-2>];[<column-name-1>];[<column-name-2>];[…]
tcp:mysqlserver1\mysqlinstance1,20234;mydatabase1.mytable1;25;col-cond1;col-cond-2;mycolumn1;mycolumn2
.;mydatabase2.mytable1;0

;col-cond1;col-cond-2;mycolumn1;mycolumn2
 mysqlserver1;mydatabase3.mytable1;10;
;col-cond1;col-cond-2;mycolumn1;mycolumn2
 ...


  • Batch script file: DataVerification.cmd

SETLOCAL
@echo off
REM ==============================================
REM DataVerification.cmd - executes a given SQL script containing logic.
REM ==============================================
  cls
set currDir=%~dp0
set dataFile=%currDir%\testcases.csv
REM SQLCMD can read (if required) multiple input sql files delimited by comma.
set iSqlFiles=%currDir%\RowCnt.sql
set oLogFile=%currDir%\DVerification.log
echo ============================================== > %oLogFile%
echo ============= Verification Details =========== >> %oLogFile%
echo ============================================== >> %oLogFile%
REM The following logic within for loop can be customized as per project requirement.
REM Currently it skips the first line in the csv file, and reads each line comma delimited line as row and column.
for /F "tokens=1-5 skip=1 delims=," %%c in (%dataFile%) do (
   echo ============================================== >> %oLogFile%
   echo Processing Database:Table: %%d >> %oLogFile%
   sqlcmd -S %%c -i %iSqlFiles% -v tableName="%%d" -v xRowCnt=%%e  -v col1-cond1=%%f -v col2-cond1=%%g -v col1Name="%%h" -v col2Name="%%i"  >> %oLogFile%
   echo ============================================== >> %oLogFile%
)
find "verification: failed" %oLogFile% > null
if "%ERRORLEVEL%" EQU "0" (
    echo Combined Verification FAILED. Please see the %oLogFile% for details.
) else (
    echo Combined Verification PASSED. Please see the %oLogFile% for details.
)


  • SQL file: RowCnt.sql
PRINT N'Generated as of: '+RTRIM(CAST(GETDATE() AS nvarchar(30)));
Declare @CurrRowCount int;
where $(tableName).$(col1Name) NOT LIKE '$(col1-cond1)' and $(tableName).$(col2Name) NOT LIKE '$(col2-cond1)'
PRINT N'Total row count: '+CAST(@CurrRowCount AS nvarchar(5))+N'.'
if (@CurrRowCount > $(xRowCnt)) PRINT N'verification: passed.';
else PRINT N'verification: failed.';


Finally, when all test cases defined in testcases.csv are executed, you'll get either "Passed" (if all test cases pass) or "Failed" (even if a single test case fails).  You can see the details in the thus generated log file, similar to following ...

==============================================
============= Verification Details ===========
==============================================

==============================================
Table: mydatabase1.mytable1
Generated as of: <timestamp>
Total row count: xx.
verification: passed[failed].

============================================== ==============================================
Table: mydatabase2.mytable1
Generated as of: <timestamp>
Total row count: yy.
verification: passed[failed].
==============================================
Obviously, this is just an idea, you can develop your won SQL file(s) as per your data processing requirement, modify .csv file as your input requirement and change the 'for' loop as change happens in .csv file.
Posted by No comments:
Labels: , , ,
Subscribe to: Posts (Atom)