OPS ANALYTICS SQL QUERY

[m_karampasis]

Dear all,

I need assistance in order to to create the following report using SQLquery at OPS Center Analytics:

HOST	FULL SCHEDULE JOB COUNT	FULL SCHEDULE SUCCESFULL JOB COUNT	FULL SCHEDULE FAIL JOB COUNT	FULL SCHEDULE SUCCESS RATE	INCREMENTAL  SCHEDULE JOB COUNT	INCREMENTAL  SCHEDULE SUCCESFULL JOB COUNT	INCREMENTAL  SCHEDULE FAIL JOB COUNT	INCREMNETAL  SCHEDULE SUCCESS RATE
CLIENT1	10	5	5	50%	50	30	20	60%
CLIENT2
CLIENT3

Thank you in regards.

[m_karampasis]

The 9 columns are:

HOST

FULL SCHEDULE JOB COUNT

FULL SCHEDULE SUCCESFULL JOB COUNT

FULL SCHEDULE FAIL JOB COUNT

FULL SCHEDULE SUCCESS RATE

INCREMENTAL SCHEDULE JOB COUNT

INCREMENTAL SCHEDULE SUCCESFULL JOB COUNT

INCREMENTAL SCHEDULE FAIL JOB COUNT

INCREMENTAL SCHEDULE SUCCESS RATE

[DG-2005]

Whats the timeframe wrapping around this?

[m_karampasis]

The desired timeframe wrapping would be one month.

Thank you for your response!

[m_karampasis]

Hello again,

Somebody who can help me..????

Thank you in regards.

[DG-2005]

I am working to try and get this setup.. seemingly a bit more difficult than i initially thought.

[DG-2005]

Well.. here's the progress that I've gotten so far. Still looking into the other parts of it.. but it's a start if anyone else wants to further off of it.

Full Backup Count 1 Month

SELECT clientName AS "Client Name",
COUNT(*) AS "Backup Count",
lookup_ScheduleType.name AS "Schedule Type"
FROM domain_job
JOIN lookup_ScheduleType
ON lookup_ScheduleType.id = domain_job.scheduleType
WHERE DATEDIFF(month,UTCBigIntToNomTime(StartTime), GETDATE()) <= 1 AND lookup_ScheduleType.name LIKE 'Full'
GROUP BY clientName,lookup_ScheduleType.name
ORDER BY clientName ASC

Incremental  Backup Count 1 Month

SELECT clientName AS "Client Name",
COUNT(*) AS "Backup Count",
lookup_ScheduleType.name AS "Schedule Type"
FROM domain_job
JOIN lookup_ScheduleType
ON lookup_ScheduleType.id = domain_job.scheduleType
WHERE DATEDIFF(month,UTCBigIntToNomTime(StartTime), GETDATE()) <= 1 AND lookup_ScheduleType.name LIKE 'Differential Incremental'
GROUP BY clientName,lookup_ScheduleType.name
ORDER BY clientName ASC

User Backup Count 1 Month

SELECT clientName AS "Client Name",
COUNT(*) AS "Backup Count",
lookup_ScheduleType.name AS "Schedule Type"
FROM domain_job
JOIN lookup_ScheduleType
ON lookup_ScheduleType.id = domain_job.scheduleType
WHERE DATEDIFF(month,UTCBigIntToNomTime(StartTime), GETDATE()) <= 1 AND lookup_ScheduleType.name LIKE 'User backup'
GROUP BY clientName,lookup_ScheduleType.name
ORDER BY clientName ASC

[m_karampasis]

Hello DG-2005,

Could you inform us if there was any progress regarding the sql query?

BR,

[DG-2005]

last thing i posted is the current info i have on it. no additional progress was able to be made, posted hoping someone would be able to use as a reference to improve upon for what you need.