Get list of SQL Server Jobs from multiple instance via SSIS package

Get list of SQL Server Jobs from multiple instance via SSIS package

While managing multiple instance of SQL Server, it is little bit difficult to keep watch on every jobs running in every SQL Server Instances we are managing. I have created one SSIS package to list SQL Server Job of all instance I used to manage and insert that list in one database so that anytime I have list ready if any one ask me about anything related to job.

If I have list of jobs with along with its schedule, I can query every SQL Server instance to check whether every job of the instance ran or not. If any job is not running, I can send automated email with list of “Missing Jobs”.

I will cover complete sequence in two article. This article will show you how to prepare a list of all SQL Server Job from multiple instance.

We might change schedule of certain jobs, we might add/remove some of the jobs so it is necessary to have perfect updated list of jobs so the SSIS package I am going to run in this article, would be scheduled to run everyday and gather the latest/updated list of jobs from all instance and insert it in one database of one instance.

Let us start creating foundation for this SSIS package.

I used to have one database called “DBAdb” in one of the instance of SQL Server and I used to collect DBA related data in “DBAdb” database only so that none of the production database becomes big due to DBA related activity. Apart from “DBAdb” database, we need two tables inside “DBAdb” database.

1.) SQLServerInstance: this table will have details of all SQL Server instance and this will be the base table for our package as our package read detail of SQL Server Instance from this table and collect the JOB details from that particular instance.

2.) JobScheduleDetails: This table will have detail of each job along with its schedule which are collected from the each instance we have listed in “SQLServerInstance” table.

I will have only one entry in “SQLServerInstance” table as I am preparing this article from my laptop but you can have as many entries as you want. Package will be created dynamically which will iterate for each server we list.

Here is the TSQL to create database and tables we have listed above.

[sourcecode language=”sql”]
CREATE DATABASE DBAdb
GO

USE [DBAdb]
GO

CREATE TABLE [dbo].[SQLServerInstances](
[Sid] [INT] IDENTITY(1,1) NOT NULL,
[ServerName] [VARCHAR](100) NULL,
[ConnectionString] [VARCHAR](1000) NULL,
[IsActive] [bit] NULL,
[GetJobSchedules] [bit] NULL
) ON [PRIMARY]

INSERT INTO SQLServerInstances
–you have to give your servername alongwith its connection string
SELECT ‘USER12\SQL2K12DEV’,’Data Source=USER12\SQL2K12DEV;Initial Catalog=msdb;Provider=SQLNCLI10.1;INTegrated Security=SSPI;Auto Translate=False;’,1,1
GO

–Create table in centralize "DBADb" database in one instance
–which will collect information of job from all servers.
USE [DBAdb]
GO

CREATE TABLE [dbo].[JobSchedulesDetails](
[Seq] [INT] IDENTITY(1,1) NOT NULL,
[ServerName] [VARCHAR](100) NULL,
[JobId] [uniqueidentifier] NULL,
[JobName] [VARCHAR](500) NULL,
[IsEnabled] [VARCHAR](10) NULL,
[Occurrence] [VARCHAR](200) NULL,
[JobSchedule] [VARCHAR](4000) NULL,
[FrequencyType] [VARCHAR](100) NULL,
[Frequency] [INT] NULL,
[StartTime] [VARCHAR](20) NULL,
[EndTime] [VARCHAR](20) NULL,
[StartDateTime] AS (CONVERT([DATETIME],(CONVERT([CHAR](8),getdate(),(112))+’ ‘)+[StartTime],0)),
[EndDateTime] AS (CONVERT([DATETIME],(CONVERT([CHAR](8),getdate(),(112))+’ ‘)+[EndTime],0)),
[CategoryId] [INT] NULL
) ON [PRIMARY]
GO
[/sourcecode]

Now we are ready with information we want so let us start creating SSIS package.

1.) Create three variables for the package as given in following screen capture.

1DeclareVariable

2.) Get one “Execute SQL Task” in “Control Flow” of your package.

2ExecuteTaskForServerList

I have used following query in “Execute SQL Task”.

[sourcecode language=”sql”]
SELECT
ServerName,
ConnectionString
FROM
dbo.SQLServerInstances
WHERE
IsActive = 1
AND
GetJobSchedules =1
[/sourcecode]

3.) Above query will return list of SQL Server Instance where we need to collect job details from so we are going to capture result set in one of the variable we have created. Go to “Result Set” tab in Execute SQL Task as given below and following the screen capture.

3ResultSetinVariable

4.)  Take one “Foreach Loop Container” and join it with previously created “Execute SQL Task”. set properties of Foreach task as given in screen capture.

4ForEachLoopFromVariable

5.) need to set other variables for data in Foreach task. see following screen capture.

5ForEachLoopVariableMapping

6.) Since we need latest and updated detail of JOB in master table, we are going to delete old job detail from the table before we insert new details via “Execute SQL Task”.

6DeleteOldRecordOfJobScheduleForServer

Here is the query I have used in above Execute SQL Task.

[sourcecode language=”sql”]
DELETE FROM DBAdb.dbo.JobSchedulesDetails
WHERE ServerName  = ?
[/sourcecode]

7.) Now take one “Data Flow” task and connect it with “Execute SQL Task” we created to delete data for old job detail before we capture fresh data.

7DataFlowTask

8.) As soon as you double click on newly created “Data Flow” task, you will be forwarded to “Data Flow” tab and you will have to have three task there. 1.) OLE DB Source 2.) Data Conversion 3.) OLE DB Destination.

8DataFlowInsight

OLEDB Source task here plays very crucial role. Following is the query I have used to read job details.

[sourcecode language=”sql”]
SELECT
cast(SERVERPROPERTY (‘servername’)  as nvarchar(200)) as ServerName,
a.job_id as JobId,
a.name as JobName,
a.Category_id as CategoryId,
case when a.Enabled = 1 then ‘Enable’ else ‘Disable’ end as JobStatus,
CASE [freq_type]
WHEN 1 THEN ‘One Time’
WHEN 4 THEN ‘Daily’
WHEN 8 THEN ‘Weekly’
WHEN 16 THEN ‘Monthly’
WHEN 32 THEN ‘Monthly – Relative to Frequency Interval’
WHEN 64 THEN ‘Start automatically when SQL Server Agent starts’
WHEN 128 THEN ‘Start whenever the CPUs become idle’
END [Occurrence]
, (CASE [freq_type]
WHEN 4 THEN ‘Occurs every ‘ + CAST([freq_interval] AS VARCHAR(3)) + ‘ day(s)’
WHEN 8 THEN ‘Occurs every ‘ + CAST([freq_recurrence_factor] AS VARCHAR(3))
+ ‘ week(s) on ‘
+ CASE WHEN [freq_interval] & 1 = 1 THEN ‘Sunday’ ELSE ” END
+ CASE WHEN [freq_interval] & 2 = 2 THEN ‘, Monday’ ELSE ” END
+ CASE WHEN [freq_interval] & 4 = 4 THEN ‘, Tuesday’ ELSE ” END
+ CASE WHEN [freq_interval] & 8 = 8 THEN ‘, Wednesday’ ELSE ” END
+ CASE WHEN [freq_interval] & 16 = 16 THEN ‘, Thursday’ ELSE ” END
+ CASE WHEN [freq_interval] & 32 = 32 THEN ‘, Friday’ ELSE ” END
+ CASE WHEN [freq_interval] & 64 = 64 THEN ‘, Saturday’ ELSE ” END
WHEN 16 THEN ‘Occurs on Day ‘ + CAST([freq_interval] AS VARCHAR(3))
+ ‘ of every ‘
+ CAST([freq_recurrence_factor] AS VARCHAR(3)) + ‘ month(s)’
WHEN 32 THEN ‘Occurs on ‘
+ CASE [freq_relative_interval]
WHEN 1 THEN ‘First’
WHEN 2 THEN ‘Second’
WHEN 4 THEN ‘Third’
WHEN 8 THEN ‘Fourth’
WHEN 16 THEN ‘Last’
END
+ ‘ ‘
+ CASE [freq_interval]
WHEN 1 THEN ‘Sunday’
WHEN 2 THEN ‘Monday’
WHEN 3 THEN ‘Tuesday’
WHEN 4 THEN ‘Wednesday’
WHEN 5 THEN ‘Thursday’
WHEN 6 THEN ‘Friday’
WHEN 7 THEN ‘Saturday’
WHEN 8 THEN ‘Day’
WHEN 9 THEN ‘Weekday’
WHEN 10 THEN ‘Weekend day’
END
+ ‘ of every ‘ + CAST([freq_recurrence_factor] AS VARCHAR(3))
+ ‘ month(s)’
END ) +
(CASE [freq_subday_type]
WHEN 1 THEN ‘ once at ‘
+ STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
WHEN 2 THEN ‘ every ‘
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ‘ Second(s) between ‘
+ STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
+ ‘ & ‘
+ STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
WHEN 4 THEN ‘ every ‘
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ‘ Minute(s) between ‘
+ STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
+ ‘ & ‘
+ STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
WHEN 8 THEN ‘ every ‘
+ CAST([freq_subday_interval] AS VARCHAR(3)) + ‘ Hour(s) between ‘
+ STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
+ ‘ & ‘
+ STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
END ) as JobSchedule,

(CASE [freq_subday_type]
WHEN 1 THEN ‘Once’
WHEN 2 THEN ‘Second(s)’
WHEN 4 THEN ‘Minute(s)’
WHEN 8 THEN ‘Hour(s)’
END ) as ‘Every’,

(CASE [freq_subday_type]
WHEN 1 THEN NULL
WHEN 2 THEN CAST([freq_subday_interval] AS VARCHAR(3))
WHEN 4 THEN CAST([freq_subday_interval] AS VARCHAR(3))
WHEN 8 THEN CAST([freq_subday_interval] AS VARCHAR(3))
END ) as ‘Between’,

(CASE [freq_subday_type]
WHEN 1 THEN  STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
WHEN 2 THEN STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)

WHEN 4 THEN STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)

WHEN 8 THEN STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)

END ) as StartTime ,

(CASE [freq_subday_type]
WHEN 1 THEN STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_start_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
WHEN 2 THEN  STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
WHEN 4 THEN STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
WHEN 8 THEN STUFF(
STUFF(RIGHT(‘000000’ + CAST([active_end_time] AS VARCHAR(6)), 6)
, 3, 0, ‘:’)
, 6, 0, ‘:’)
END ) as EndTime
FROM
[msdb].[dbo].[sysjobs] a
INNER JOIN [msdb].[dbo].[sysjobschedules] b
ON (b.job_id = a.job_id)
INNER JOIN [msdb].[dbo].[sysschedules] c
ON (c.schedule_id = b.schedule_id)
ORDER BY a.name
[/sourcecode]

9.) We may have one or multiple instance where we need to collect this information from so we have to connect our OLE DB Source Task dynamically with different server instance. First use the query I gave above and create regular SQL Server connection. Once you are done, go to property of that connection task to change the name and give it dynamic connection string for different server. We have dynamic connection string coming in Foreach Loop and we have mapped that connection string in “ConnectionString” variable we have created in very first steps above.

9DynamicConnection

10.) Connect one “Data Conversion” task with OLE DB Source and see the property I have set in following screen capture.

10DataConversion

Now, we have read job details from server. Now, this is time to insert that detail in “DBAdb” database so that we can use it for our purpose.

10.) set the property of “OLE DB Destination” after connecting it to “Data Conversion Task”.

11OLEDBDestination

11.) We have to map column from source and destination in “OLE DB Destination” task.

12Mapping

Now you are ready to run. Execute the package and see its effect. If the servers you have inserted in “SQLServerInstance” table is having SQL Server Job, you will find job details in your “[JobSchedulesDetails]” table.

BTW, I have written few more articles related to SQL Server Job. If you are interested, have a look at following list:

  • Find SQL Server Agent job ran on specific date with its status (Click Here)
  • Search SQL Server Job based on keyword provided (Click Here)
  • Script all SQL Server Job automatically by SSIS and SMO (Click Here)
  • Get list of failed SQL Server Agent job in SQL Server (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Get list of failed SQL Server Agent job in SQL Server

failedJobGet list of failed SQL Server Agent job in SQL Server

When you are working with mission critical database environment, you have to closely keep a watch on every database operation being performed. SQL Server jobs are one of the very important critical things to watch at.

We might have jobs for some:

  • heavy aggregation or calculations
  • ETL processes
  • different kind of alerts and logs
  • any much more……..

We may have job running once in a day or many be scheduled to run for few times a day or week. No matter how and when job is running, it is prime duty of DBA to keep watch on each and every job. If any job get failed due to any reason then fix it and run it again.

If job can’t run for some or other reason, it might adversely affect business operation.

Now, question comes, how to keep watch on jobs? You may have many jobs running in SQL Server instance or you may have multiple sql server instance which is executing multiple jobs. I have created on small user defined function and one stored procedure which I execute in SQL Server database and it will give me result of failed job.

If I have recurring job and it failed once but if it runs successfully next time, SP will not give you that job name. Whenever you execute the stored procedure give below, it will check only last occurrence of job, if it is failed, SP will return it to draw your attention towards it.

[sourcecode language=”sql”]
CREATE FUNCTION [Conv_DateTime](@id int, @it int)
RETURNS DATETIME
AS
BEGIN
DECLARE @vt char(6), @d char(8), @dt datetime
SELECT @d = convert(varchar, @id)

IF len(@it) = 5
SET @vt = ‘0’ + convert(char,@it)
ELSE if len(@it) = 4
SET @vt = ’00’ + convert(char,@it)
ELSE if len(@it) = 3
SET @vt = ‘000’ + convert(char,@it)
ELSE if len(@it) = 2
SET @vt = ‘0000’ + convert(char,@it)
ELSE if len(@it) = 1
SET @vt = ‘00000’ + convert(char,@it)
ELSE if len(@it) = 6
SET @vt = convert(char,@it)
SELECT @dt = left (@d,4) + ‘-‘ + substring(@d,5,2) + ‘-‘ + right(@d,2) + ‘ ‘ + left(@vt,2) + ‘:’ + substring(@vt, 3,2) + ‘:’ + right(@vt, 2)
RETURN (@dt)
END
GO
[/sourcecode]

Once you are ready with function which will return exact time of when the job failed, we will call that function in following stored procedure.

[sourcecode language=”sql”]
CREATE PROCEDURE Getfailedjobsdetail
AS
BEGIN
SET NOCOUNT ON
SELECT CAST(Serverproperty(‘servername’) as varchar(100)) AS ServerName,
res.jobname AS JobName,
CONVERT(varchar(5),
DATEADD(ss,(CASE len(RunDuration)
WHEN 1 THEN RunDuration
WHEN 2 THEN RunDuration
WHEN 3 THEN (cast(Left(right(RunDuration,3),1) as int)*60) + (right(RunDuration,2))
WHEN 4 THEN (cast(Left(right(RunDuration,4),2) AS int)*60) + (right(RunDuration,2))
WHEN 5 THEN (cast(Left(right(RunDuration,5),1) AS int)*3600) + (cast(Left(right(RunDuration,4),2) AS int)*60) + right(RunDuration,2)
WHEN 6 THEN (cast(Left(right(RunDuration,6),2) AS int)*3600) + (cast(Left(right(RunDuration,4),2) AS int)*60) + right(RunDuration,2)
END ),
conv_datetime(run_date,run_time)),108)as FailedTime
FROM (SELECT Row_number()
OVER(
partition BY sj.name
ORDER BY run_time DESC) AS rnk,
sj.name AS JobName,
run_date,
run_time,
run_duration AS RunDuration,
run_status
FROM msdb.dbo.sysjobhistory sjh WITH (nolock)
INNER JOIN msdb.dbo.sysjobs sj WITH (nolock)
ON sjh.job_id = sj.job_id
WHERE sjh.step_id <> 0
AND run_date = CONVERT(VARCHAR(8), Getdate(), 112)) res
WHERE res.run_status = 0
AND res.rnk = 1
END
GO
[/sourcecode]

After having SP and Function both, you have to execute the stored procedure like this:

[sourcecode language=”sql”]
EXEC Getfailedjobsdetail
GO
[/sourcecode]

If any job is failed and that job is not ran again successfully, above SP will give you list for the same. I used to call above given SP from my monitoring web page, developed in asp.net, I have created. This web page execute SP in all the servers I have maintain and list Job name with Server name if it fails. As soon as support person see anything failed on that web page, they used to come to me to find the issue and fix it.

This is making my life very easy. Istn’t it!!!!!

BTW, I have written few more articles related to SQL Server Job. If you are interested, have a look at following list:

  • Find SQL Server Agent job ran on specific date with its status (Click Here)
  • Search SQL Server Job based on keyword provided (Click Here)
  • Script all SQL Server Job automatically by SSIS and SMO (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Script all SQL Server Job automatically by SSIS and SMO

Script all SQL Server Job automatically by SSIS and SMO

As per MSDN, SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.

I have one of my production server in SQL Server 2012 and I wanted to take backup of SQL Server Job script automatically once in a day.

We may have replication / mirroring / log shipping for database but there are lot more things out side the database too. In time of failover the server, I need all the job in other server too and that is the reason I planned to backup all SQL Server Agent jobs automatically.

Let us see, how it goes!!!

Create one SSIS project and take one “Script Task”. Double click on “Script Task” to open its editor. From the editor, click on “Edit Script” button.

1ScriptTaskEditor

Once you get script editor, you will find namespace section in the script, I have added few more namespace listed below:

[sourcecode language=”c”]
using Microsoft.SqlServer.Management.Smo.Agent;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using System.IO;
using System.Collections.Specialized;
[/sourcecode]

Here is the code I have written in “Main()” method of script editor.

[sourcecode language=”c”]
// TODO: Add your code here
StringCollection strCol = new StringCollection();
ScriptingOptions scriptOpt = new ScriptingOptions();
scriptOpt.IncludeDatabaseContext = true;

ServerConnection conn = new ServerConnection();
conn.LoginSecure = false;
conn.Login = "sa";
conn.Password = "sa";
conn.ServerInstance = "serverName";
Server srv = new Server(conn);

try
{
string script = "";
string JobName;
//Looping through the job
foreach (Job J in srv.JobServer.Jobs)
{
script ="";
JobName = J.Name.ToString();
strCol = J.Script(scriptOpt);

//concate the text of job
foreach (string s in strCol)
{
script += s;
}
//save the job file
TextWriter tw = new StreamWriter("D:\\RiteshShah\\ServerName\\JobBackup\\" + JobName.Replace(‘:’, ‘_’).ToString() + ".sql");
tw.Write(script);
tw.Close();
}
Dts.TaskResult = (int)ScriptResults.Success;
}
catch
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
[/sourcecode]

Here is the screen capture of my script editor.

2ScriptEditor

Now, save the script, close the script editor, click “ok” button for “Script Task” property and run your package. If the path for the script you have provided, is exists and the credential is proper, you will be able generate script of all SQL Server Agent Job.

If you like this article, do like “Extreme-Advicepage in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Find SQL Server agent job ran on specific date with its status

It is very mandatory for SQL Server DBA to keep an eye on the SQL Server Job and take an immediate action if job gets failed. There are several ways of doing this. I will be showing one query which will return all jobs ran on the specific date along with its status like whether it was failed or succeed.
In order to gain information regarding JOBs, we have to query following list of system tables in MSDB database because all SQL Server jobs are stored in MSDB database only.
1.)     Sysjobhistory
2.)    Sysjobs
3.)    sysjobsteps
Here is the query which will return the desired result:

[sourcecode language=”sql”]USE MSDB
GO

DECLARE @Today AS VARCHAR(8)
SET @Today = CONVERT(VARCHAR(8),GETDATE(),112)
SELECT * FROM (
SELECT
SysJob.name,
CASE WHEN  JobHist.run_status =1
THEN ‘Success’
WHEN JobHist.run_status =0
THEN ‘Fail’
END AS JobRunStatus,
JobHist.run_date,
JobHist.run_time,
JobHist.run_duration AS RunDuration,
JobStep.step_id,
JobStep.command,ROW_NUMBER() OVER(PARTITION BY SysJob.name,JobStep.step_id ORDER BY run_time DESC) AS NumberOfExecution
FROM
dbo.sysjobhistory AS jobHist
INNER JOIN
dbo.sysjobs AS SysJob
ON
JobHist.job_id = SysJob.job_id
INNER JOIN
dbo.sysjobsteps AS JobStep
ON
(JobStep.job_id = SysJob.job_id)
WHERE
JobHist.run_date = @Today
)
AS T
WHERE
NumberOfExecution=1[/sourcecode]

As a DBA, I keep this script handy as I never know when I will need this. We already have monitoring over each and every jobs and failure of any jobs will be notified to me via email though this has become time saving for me so many times.
One tip I would like to give especially when I am talking about JOB is, keep regular backup policy for MSDB database too as all the JOBs are being stored here and while restoring SQL Server from any disaster, we need to create all the JOBs again and MSDB database backup become life savior in that case.
I have explained the importance of backup in one of my past article, if you are interested to read, click here.
Happy Scripting!!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah