October 13, 2015 at 7:07 am
Hi guys,
I know posting this question might seem better suited for the vendor forum of the product I am using. But my question is more SQL then product. So I thought I would ask people who use SQL daily.
I need help writing a query that I assume needs a join or inner join, 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)"
The output would in the end would be something like
Policy Name | VM Name | Operating System | Job Start Time | Job End Eimte | Throughput | Job Status
Some_Policy | Somehos | SomeOperatingSys | Oct 11 2142AM | Oct 11 2142PM | 192MB/s | Successful
Some_Policy | Somehos | SomeOperatingSys | Oct 11 2112AM | Oct 11 2113PM | 102MB/s | Partial
---------------------------------------------
I know what I want to do, but my SQL knowledge is rusty and weak, if just non existent. Also if I am asking stupid questions please feel free to point that out. I am by no means a SQL guy, I know enough to fake it at best as a basic user.
So please be honest but gentle 🙂
October 13, 2015 at 7:28 am
As long as all the tables have a joining field it shouldnt be a problem, just a simple join between them.
For the status 0 1 issue, have a look at the CASE statement eg CASE WHEN 0 THEN 'Success' WHEN 1 THEN 'Partial' END
If you need help could you post the definitions of the tables in question and any relationships between them so we can create the tables and create some test scripts.
October 15, 2015 at 6:46 am
Thanks for the response
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)",
(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
order by startTime desc
Above is the query I have so far. I was not able to get your example working yet, but that is probably my mistake. I have also linked a PDF for the DB Schema
https://hp.symantec.com/asset/download/220380e55817ac071e571d48afb12374
October 15, 2015 at 7:04 am
Thanks, that PDF is the "Whats New" not the DB schema.
Never seen "START AT 1" in a TOP clause before.
October 15, 2015 at 7:35 am
https://hp.symantec.com/asset/download/c13872ec1c1701c81d680dc2cc111f13
Sorry about that, the links were right by each other. Try this.
October 15, 2015 at 8:01 am
Peter Dragon (10/15/2015)
https://hp.symantec.com/asset/download/c13872ec1c1701c81d680dc2cc111f13Sorry about that, the links were right by each other. Try this.
Thanks, are you running this directly in the OpsCenter console or are you using SQL Server Management Studio?
I'm going to go out on a whim and say its using it's only style of T-SQL via the console as everything I'm seeing like adjust_timestamp and utcbiginttonomtime on the OpsCenter documentation doesn't look like calls to standard T-SQL functions
October 15, 2015 at 9:10 am
I am using it via the console, yes. I have yet to setup the ODBC drivers on RHEL6 to allow for outside connections to the DB.
October 15, 2015 at 12:18 pm
Ok, this is starting to make a bit more sense now the more I lookup OpCentre. It has its own database engine and its own T-SQL dialect which is why it can run on Windows and Linix systems.
Doesn't look to dissimilar to MSSQL T-SQL due to ANSII standards but has its own tweaks.
The schema will get so far into getting a query written but the things like adjust_timestamp I can't help you with as that's not MSSQL T-SQL, so might be best on the Symantec forums for how to work those bits out.
When I get time to look over the scheme tomorrow I'll post back a sample query that will need the Symantec tweaks adding on top.
October 26, 2015 at 8:44 am
Thanks! Any help you can give is welcome 🙂
October 27, 2015 at 9:39 am
Probably best posting this on the NetBackup forums instead.
There are more tables involved than originally thought and someone on the NetBackup site may have already got a script for what you want to do.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply