SQL query and multistream job

[M_henriksen]

Hello everyone

I try to create a SQL query that shows clientname, policyname, schedulename, bytesWritten, filesBackedUp.
But since almost all jobs running as multistream backup jobs and I make a
WHERE id = parentjobid, the schdulename only appers as a dash (-)..

How can this be manipulated in SQL?

Br Morten

[RiaanBadenhorst]

Hi Morten

Can you post what you're seeing plus the entire query please.

[M_henriksen]

Hi Riaan.

What i need is every server on only one line with all the information about last full backup job.

Hope you can help.

Br Morten

SELECT
clientname as 'Client',
schedulename as 'Schedule Name',
(UtcBigIntToNomTime(starttime)) AS 'Last full backup start',
(UtcBigIntToNomTime(endtime)) AS 'Last full backup end',
(SELECT COALESCE(CAST(SUM(preSISSize)/1024.0/1024.0/1024.0 AS NUMERIC (20,2)), 0) AS 'Size (GiB)' from domain_job b where b.parentjobid = domain_job.parentjobid),
(SELECT SUM(filesBackedUp) AS 'Total files' from domain_job c where c.parentjobid = domain_job.parentjobid)

FROM
domain_Job
WHERE id = parentjobid and statusCode in (0,1)
and policyname like 'CUSTOMER_%'
and scheduletype = '0'
AND DATEDIFF(day,UtcBigIntToNomTime(endTime), GETDATE()) <= 7
GROUP BY clientName,schedulename,starttime,endtime,parentjobid
ORDER BY clientName

[RiaanBadenhorst]

Hi Morten,

I'm not a SQL expert so can you explain what the highlighted text below would achieve,

(SELECT COALESCE(CAST(SUM(preSISSize)/1024.0/1024.0/1024.0 AS NUMERIC (20,2)), 0) AS 'Size (GiB)' from domain_job b where b.parentjobid = domain_job.parentjobid),

What is the function of the "b" and what is the comparison doing?

I see this behaviour with the schedule name of '-' on my system when its looking at Oracle Jobs that are using the new Intelligent policies. It is the 1st parent job that get this value.