Opscenter Query

[Sid1987]

Hi Techiz,

  I want to have a query report which gives me information on

Jobid Clientname FileList(Selection List) Policyname Schedulename Statuscode Exitstatus Size Starttime Endtime

SELECT
nb_JobFilesArchive.jobId as 'Job id',
domain_JobArchive.clientName as 'Client',
domain_JobArchive.policyName as 'Policy Name',
domain_JobArchive.scheduleName as 'Schedule Name',
lookup_JobStatuscode.id as 'Status Code',
lookup_JobStatusCode.name as 'Exit Status',
(domain_JobArchive.bytesWritten / 1024) as 'Size in KB',
nb_JobFilesArchive.fileinfo as 'File List',
UTCBigIntToNomTime(domain_JobArchive.startTime) as 'Start Time',
UTCBigIntToNomTime(domain_JobArchive.endTime) as 'End Time'
FROM (domain_JobArchive inner join nb_JobFilesArchive on nb_JobFilesArchive.jobId=domain_JobArchive.id) inner join lookup_JobStatusCode on domain_JobArchive.StatusCode=lookup_JobStatusCode.id
AND UTCBigIntToNomTime(domain_JobArchive.startTime) between '9/17/2014 7:43:19' and '9/18/2014 8:41:33'
AND domain_JobArchive.clientId<>'-2147483644'
ORDER BY lookup_JobStatuscode.id DESC

Now the above query gives me more than actual entries. What could be the possible reason for that, Am I doing anything wrong on the joins?

I have another query which is like

SELECT
DJ.id as 'Job id',
DJ.clientName as 'Client',
DJ.policyName as 'Policy Name',
DJ.scheduleName as 'Schedule Name',
DJ.StatusCode as 'Status Code',
LJ.name as 'Exit Status',
DJ.bytesWritten as 'Size in Bytes',
DJ.bytesWritten/1024 as 'Size in KBytes',
DJ.bytesWritten/1024/1024 as 'Size in MBytes',
FA.fileinfo as 'File List',
UTCBigIntToNomTime(DJ.startTime) as 'Start Time',
UTCBigIntToNomTime(DJ.endTime) as 'End Time'
FROM
domain_JobArchive DJ, nb_JobFilesArchive FA, lookup_JobStatusCode LJ
where DJ.id=FA.jobId
AND FA.clientName=DJ.clientName
AND DJ.StatusCode=LJ.id
AND UTCBigIntToNomTime(DJ.startTime) between '9/17/2014 7:43:19' and '9/18/2014 8:41:33'
AND DJ.clientId<>'-2147483644'
ORDER BY DJ.clientName, DJ.parentJobId, FA.jobId

Now the above query gives me right number of entries however gives me strange output, It showed me selection list from a different policy infront of different. Moreover it gives more than one jobid.

Please suggest

Thanks

Sid

[RiaanBadenhorst]

Hi

Try this (didn't add the date range but you can put it back)

select
nb_JobFilesArchive.jobId as 'Job id',
domain_JobArchive.clientName as 'Client',
domain_JobArchive.policyName as 'Policy Name',
domain_JobArchive.scheduleName as 'Schedule Name',
(domain_JobArchive.bytesWritten / 1024) as 'Size in KB',
nb_JobFilesArchive.fileinfo as 'File List',
lookup_JobStatuscode.id as 'Status Code',
lookup_JobStatusCode.name as 'Exit Status',
UTCBigIntToNomTime(domain_JobArchive.startTime) as 'Start Time',
UTCBigIntToNomTime(domain_JobArchive.endTime) as 'End Time'
from domain_jobarchive, nb_JobFilesArchive, lookup_JobStatuscode
where
domain_jobarchive.id= nb_JobFilesArchive.Jobid
and
domain_JobArchive.StatusCode=lookup_JobStatusCode.id

[tom_sprouse]

Sid1987,

Riaan appears to be correct... your join on your FileList is comparing clientname to clientname

You need to tie the FileList to a specific job id... 

This is the likely cause of multiple entries...

--Tom

[Sid1987]

Thanks for the replies.

What about the query with Join. I have joined domain_jobarchive with nb_jobfilearchive and the intermediate joned table is joined with lookup_jobstatuscode. 

Thanks

Sid

[RiaanBadenhorst]

Hi Sid,

I'm not sure mate, I know nothing about SQL queries, I just reverse engineer these. Still learning.

Does mine work?

Cheers