11-20-2015 06:19 AM
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
Solved! Go to Solution.
12-31-2015 01:45 PM
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
11-22-2015 12:31 AM
How is this different from what we resolved here?
https://www-secure.symantec.com/connect/forums/opscenter-77-vmware-report-clientos
11-30-2015 06:49 AM
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 :)
11-30-2015 07:41 AM
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
12-29-2015 04:23 AM
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 | 0 | 0 |
- | cypwz5001 | Nov 5, 2015 7:09:27 PM | Nov 5, 2015 7:10:05 PM | windows7Server64Guest | 1578 | 0 | 0 |
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
12-31-2015 01:45 PM
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
01-05-2016 10:34 AM
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 |
01-05-2016 11:23 AM
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
01-05-2016 12:22 PM
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