Forum Discussion

PDragon's avatar
PDragon
Level 4
10 years ago

OpsCenter Custom SQL Query (Help Needed)

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

  • 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

     

  • 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

    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

     

  • 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

     

  •          

     
    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
  • 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

     

  • 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