OpsCenter Custom SQL Query (Help Needed)

How is this different from what we resolved here?

https://www-secure.symantec.com/connect/forums/opscenter-77-vmware-report-clientos

My last post noted I was stuck getting the desired results. My issue is trying to join the tables to get the desired results, after that last post the thread was locked.

You said shout if I got stuck, and I have :)

Hi,

Ok I see, sorry I missed the subsequent replies.

What I think might be happening is that you're finding snapshot (and parent) jobs in your top 100 list. Can try and put another criteria in to look only for domain_jobarchive.type "Backup" or lookup_jobtype.id "0".

HTH

So I tried that and it seems to return the same values as my case statement for Job Status

Below is the query

select

TOP 100 START AT 1 domain_Policy.name as "Policy",

domain_client.name as "VM Guest",

adjust_timestamp(domain_JobArchive.startTime,-14400000 ) as "Job Start Time",

adjust_timestamp(domain_JobArchive.endTime,-14400000 ) as "Job End Time",

nb_VirtualMachines.operatingSystem as "Virtual OS",

domain_JobArchive.throughput as "Throughput (KB/Sec)",

domain_jobarchive.type "Backup",

(case when domain_JobArchive.state =3 and domain_JobArchive.statusCode in (0,1) then 0 else 2 end) as "Job Status (Complete/Partial)"

from domain_client, nb_VirtualMachines, domain_JobArchive , domain_Policy , domain_MasterServer , domain_Client

where

nb_VirtualMachines.name=domain_client.name

select
domain_Policy.name as "Policy",
domain_client.name as "VM Guest",
adjust_timestamp(domain_JobArchive.startTime,-14400000 ) as "Job Start Time",
adjust_timestamp(domain_JobArchive.endTime,-14400000 ) as "Job End Time",
nb_VirtualMachines.operatingSystem as "Virtual OS",
domain_JobArchive.throughput as "Throughput (KB/Sec)",

(CASE
  WHEN domain_JobArchive.state = 3 AND domain_JobArchive.statusCode = 0 THEN 'Successful'
  WHEN domain_JobArchive.state = 3 AND domain_JobArchive.statusCode = 1 THEN 'Partial'
  WHEN domain_JobArchive.state = 3 AND domain_JobArchive.statusCode > 1 THEN 'Failed'
  ELSE 'Failed'
 END)  as "Job Status"

from domain_client, nb_VirtualMachines, domain_JobArchive , domain_Policy , domain_MasterServer , domain_Client, lookup_JobState

where
nb_VirtualMachines.name=domain_client.name AND
domain_JobArchive.clientName = domain_Client.name AND
domain_JobArchive.policyName = domain_Policy.name AND
domain_jobArchive.masterServerID = domain_MasterServer.id AND 
domain_JobArchive.isValid = 1 AND 
domain_JobArchive.type = 0 AND 
domain_JobArchive.statusCode not in (0,1)

order by "Throughput (KB/Sec)" desc

 The output is now close to what I was looking for. Now I just need to sort out the "job status" as the successfull jobs are showing as failed. I am not sure where I went wrong?

Disregard, I figured it out, I needed to remove the line "domain_JobArchive.statusCode not in (0,1) " with the new case. And Violla!...

Below is the final query that gives you VMguest backup report with Operating System.

SELECT domain_Policy.NAME AS "Policy"
	,domain_client.NAME AS "VM Guest"
	,adjust_timestamp(domain_JobArchive.startTime, - 14400000) AS "Job Start Time"
	,adjust_timestamp(domain_JobArchive.endTime, - 14400000) AS "Job End Time"
	,nb_VirtualMachines.operatingSystem AS "Virtual OS"
	,domain_JobArchive.throughput AS "Throughput (KB/Sec)"
	,(
		CASE 
			WHEN domain_JobArchive.STATE = 3
				AND domain_JobArchive.statusCode = 0
				THEN 'Successful'
			WHEN domain_JobArchive.STATE = 3
				AND domain_JobArchive.statusCode = 1
				THEN 'Partial'
			WHEN domain_JobArchive.STATE = 3
				AND domain_JobArchive.statusCode > 1
				THEN 'Failed'
			ELSE 'Failed'
			END
		) AS "Job Status"
FROM domain_client
	,nb_VirtualMachines
	,domain_JobArchive
	,domain_Policy
	,domain_MasterServer
	,domain_Client
	,lookup_JobState
WHERE nb_VirtualMachines.NAME = domain_client.NAME
	AND domain_JobArchive.clientName = domain_Client.NAME
	AND domain_JobArchive.policyName = domain_Policy.NAME
	AND domain_jobArchive.masterServerID = domain_MasterServer.id
	AND domain_JobArchive.isValid = 1
	AND domain_JobArchive.type = 0
	
ORDER BY "Job Start Time" DESC

Glad its working. I think you should also remove lookup_jobState from the "FROM" clause. You're not actually using it anywhere in your query and its not joined to any other table. 

You also dont need domain_Client (you got it listed twice), domain_Policy or domain_masterServer since all you're getting from there is the names and you can just pull that directly from the domain_jobArchive table (you're joining the tables on Name just so you can show the same name from the joined table). 

I think this should do the same as your query with a lot less table joins (so it will be faster): 

select
domain_JobArchive.policyName as "Policy",
domain_JobArchive.clientName as "VM Guest",
adjust_timestamp(domain_JobArchive.startTime,-14400000 ) as "Job Start Time",
adjust_timestamp(domain_JobArchive.endTime,-14400000 ) as "Job End Time",
nb_VirtualMachines.operatingSystem as "Virtual OS",
domain_JobArchive.throughput as "Throughput (KB/Sec)",

(CASE
  WHEN domain_JobArchive.state = 3 AND domain_JobArchive.statusCode = 0 THEN 'Successful'
  WHEN domain_JobArchive.state = 3 AND domain_JobArchive.statusCode = 1 THEN 'Partial'
  WHEN domain_JobArchive.state = 3 AND domain_JobArchive.statusCode > 1 THEN 'Failed'
  ELSE 'Failed'
 END)  as "Job Status"

from nb_VirtualMachines, domain_JobArchive

where
nb_VirtualMachines.name = domain_JobArchive.clientName AND
domain_JobArchive.isValid = 1 AND 
domain_JobArchive.type = 0 AND 
domain_JobArchive.statusCode not in (0,1)

order by "Throughput (KB/Sec)" desc