cancel
Showing results for 
Search instead for 
Did you mean: 

OpsCenter Custom SQL Query (Help Needed)

PDragon
Level 4

I need help writing a query that I assume needs a join or inner join that I cannot figure out, and my SQL knowledge is to be desired. So here is what I am trying to do...First the tables and columns.

Tables and Columns that have the data I need.
---------------------------------------------
domain_client
Column "Name"

domain_Policy
Column "name"

domain_JobArchive
Column "startTime" & "endTime" & "throughput" & "State" "statusCode"

nb_VirtualMachines
Column "operatingSystem" & "name"

What I am trying to do
---------------------------------------------
I need a SQL query that takes the "name" column value from the table "domain_client" and use it to select values in the "name" column in the table "nb_VirtualMachines" that contains the Operating System Value in column "operatingSystem" and combine its out put from "name" & "operatingSystem" with the select values from Column "name" in table "domain_Policy" and Column "startTime" & "endTime" & "throughput" & "State" from table "domain_JobArchive"

I then need to apply

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

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

for the two columns "startTime" & "endTime" from table domain_JobArchive.

And - and here is where I dont know if this is possible. I need to have the column "state" from Table "domain_JobArchive" if it has a value of 3 and a value of 0 or 1 in column "statusCode" in the same table it show a value of 0 or 1. 0 being = to Successful, and 1 being = to Partial.

My failed attempt at this was

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

The output would in the end would be something like

Policy Name | VM Name | OS Type | Job Start Time | Durration (Min) | Job End Eimte | Throughput | Size (GB)| Job Status
Some_Policy | Somehos  | RHEL6    | Oct 11 2142AM |                      30  | Oct 11 2142PM | 192MB/s       | 17             |Successful
Some_Policy | Somehos  | Win7       | Oct 11 2112AM |                        7  | Oct 11 2113PM  | 102MB/s       |   7             |Failed

Any help would be welcome. Attached is one of my failed SQL Query attempts

1 ACCEPTED SOLUTION

Accepted Solutions

areznik
Level 5

You're getting nonsense results because you didnt completely join all the tables, letting sybase figure it out. 

All of the tables mentioned in your from clause should appear in your where clause (since you're doing your joins that way)

So for example you should probably add - 

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

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

 

View solution in original post

8 REPLIES 8

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

How is this different from what we resolved here?

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

 

PDragon
Level 4

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 :)

RiaanBadenhorst
Moderator
Moderator
Partner    VIP    Accredited Certified

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

PDragon
Level 4

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

Policy

VM Guest

Job Start Time

Job End Time

Virtual OS

Throughput (KB/Sec)

Backup

Job Status (Complete/Partial)

- cypwz5001  Nov 5, 2015 7:09:27 PM  Nov 5, 2015 7:10:05 PM  windows7Server64Guest  1125 
- cypwz5001  Nov 5, 2015 7:09:27 PM  Nov 5, 2015 7:10:05 PM  windows7Server64Guest  1578 

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

 

areznik
Level 5

You're getting nonsense results because you didnt completely join all the tables, letting sybase figure it out. 

All of the tables mentioned in your from clause should appear in your where clause (since you're doing your joins that way)

So for example you should probably add - 

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

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

 

PDragon
Level 4

         

 
Thanks for the reply, that definitly helped out, also helped me to change up my CASE statement a bit.
 
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?

 

Policy

VM Guest

Job Start Time

Job End Time

Virtual OS

Throughput (KB/Sec)

Job Status

PRD_VMWare_RCH-LNX_DA rchfxpsat01 Nov 20, 2015 8:15:23 AM Nov 20, 2015 8:17:32 AM rhel6_64Guest 176439 Failed
<tr class="even_row" id="com.symantec.nbu.paf.dto.report.execute.ReportTableCell@3b22240b" rowselectable="false" '="" isselected="false"> PRD_VMWare_RCH-LNX_DA rchfxpsat01 Nov 20, 2015 8:15:23 AM Nov 20, 2015 8:17:32 AM rhel6_64Guest 176439 Failed

PDragon
Level 4

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

Capture.JPG

 

areznik
Level 5

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