VMware Cloud Community
varaway
Contributor
Contributor

Error when adding vCenter to chargeback

hy,

i have installed the chargeback on a Windows 2003 Server with SQL 2005 Express successfully.

When I log in into the webside, i want to add a vCenter Server. I get this error:

Invalid vCenter Server database. The vCenter Server database must have the following views: VPXV_HIST_STAT_DAILY, VPXV_HIST_STAT_WEEKLY, VPXV_HIST_STAT_MONTHLY and VPXV_HIST_STAT_YEARLY

On the chargeback database there are no such tables or views.

Have anyone an idea?

Thanks Andi

10 Replies
abhinavkatiyar
Enthusiast
Enthusiast

Hi Andi,

These views must be in vCenter sever database and not in vCenter Chargeback database. These VCDB views may have been deleted for some reason such as vCener Server Upgrade failure. In a clean vCenter server installation, you should have these views present. If you are not seeing views in VCDB, please recreate these views add the vCenter Server to Chargeback. You can run following queries in VCDB to recreate required VC DB views.

create view VPXV_HIST_STAT_DAILY as
select st.SAMPLE_TIME, st.SAMPLE_INTERVAL, sd.NAME as STAT_NAME,
sd.GROUP_NAME as STAT_GROUP,
case sd.ROLLUP_TYPE
when 0 then 'average'
when 1 then 'maximum'
when 2 then 'minimum'
when 3 then 'latest'
when 4 then 'summation'
when 5 then 'none'
end as STAT_ROLLUP_TYPE,
si.MOID AS ENTITY,
d.DEVICE_NAME, hs.STAT_VAL as STAT_VALUE
from VPX_SAMPLE_TIME1 st
, VPX_DEVICE d
, VPX_STAT_COUNTER sc
, VPX_STAT_DEF sd
, VPX_HIST_STAT1 hs
, VPX_STAT_ID si
where hs.counter_id=sc.COUNTER_ID
and sc.STAT_ID = sd.ID
and isnull(sc.DEVICE_ID,1)=isnull(d.DEVICE_ID,1)
and hs.TIME_ID = st.TIME_ID
and sc.ENTITY_ID=si.ID;

create view VPXV_HIST_STAT_WEEKLY as
select st.SAMPLE_TIME, st.SAMPLE_INTERVAL, sd.NAME as STAT_NAME,
sd.GROUP_NAME as STAT_GROUP,
case sd.ROLLUP_TYPE
when 0 then 'average'
when 1 then 'maximum'
when 2 then 'minimum'
when 3 then 'latest'
when 4 then 'summation'
when 5 then 'none'
end as STAT_ROLLUP_TYPE,
si.MOID AS ENTITY,
d.DEVICE_NAME, hs.STAT_VAL as STAT_VALUE
from VPX_SAMPLE_TIME2 st
, VPX_DEVICE d
, VPX_STAT_COUNTER sc
, VPX_STAT_DEF sd
, VPX_HIST_STAT2 hs
, VPX_STAT_ID si
where hs.counter_id=sc.COUNTER_ID
and sc.STAT_ID = sd.ID
and isnull(sc.DEVICE_ID,1)=isnull(d.DEVICE_ID,1)
and hs.TIME_ID = st.TIME_ID
and sc.ENTITY_ID=si.ID;

create view VPXV_HIST_STAT_MONTHLY as
select st.SAMPLE_TIME, st.SAMPLE_INTERVAL, sd.NAME as STAT_NAME,
sd.GROUP_NAME as STAT_GROUP,
case sd.ROLLUP_TYPE
when 0 then 'average'
when 1 then 'maximum'
when 2 then 'minimum'
when 3 then 'latest'
when 4 then 'summation'
when 5 then 'none'
end as STAT_ROLLUP_TYPE,
si.MOID AS ENTITY,
d.DEVICE_NAME, hs.STAT_VAL as STAT_VALUE
from VPX_SAMPLE_TIME3 st
, VPX_DEVICE d
, VPX_STAT_COUNTER sc
, VPX_STAT_DEF sd
, VPX_HIST_STAT3 hs
, VPX_STAT_ID si
where hs.counter_id=sc.COUNTER_ID
and sc.STAT_ID = sd.ID
and isnull(sc.DEVICE_ID,1)=isnull(d.DEVICE_ID,1)
and hs.TIME_ID = st.TIME_ID
and sc.ENTITY_ID=si.ID;

create view VPXV_HIST_STAT_YEARLY as
select st.SAMPLE_TIME, st.SAMPLE_INTERVAL, sd.NAME as STAT_NAME,
sd.GROUP_NAME as STAT_GROUP,
case sd.ROLLUP_TYPE
when 0 then 'average'
when 1 then 'maximum'
when 2 then 'minimum'
when 3 then 'latest'
when 4 then 'summation'
when 5 then 'none'
end as STAT_ROLLUP_TYPE,
si.MOID AS ENTITY,
d.DEVICE_NAME, hs.STAT_VAL as STAT_VALUE
from VPX_SAMPLE_TIME4 st
, VPX_DEVICE d
, VPX_STAT_COUNTER sc
, VPX_STAT_DEF sd
, VPX_HIST_STAT4 hs
, VPX_STAT_ID si
where hs.counter_id=sc.COUNTER_ID
and sc.STAT_ID = sd.ID
and isnull(sc.DEVICE_ID,1)=isnull(d.DEVICE_ID,1)
and hs.TIME_ID = st.TIME_ID
and sc.ENTITY_ID=si.ID;

There is a related post for recreating database views on http://communities.vmware.com/docs/DOC-3290.

Thanks,

Abhinav

Reply
0 Kudos
varaway
Contributor
Contributor

.

Reply
0 Kudos
varaway
Contributor
Contributor

hi,

thanks for the answer, but this view exists in the Virtual Server Database..........

After some time, I found the solution. TheUser must have the correct permissionfor the database. See in theattachment.

thanks Andi

sumedh_sakdeo
Contributor
Contributor

Hi Andi,

Yes you are right, it is a permission issue.

Chargeback requires only read permission for that user on vCenter Server DB.

thanks,

Sumedh

Reply
0 Kudos
lurims123
Enthusiast
Enthusiast

I got the same issue with chargeback 2.5.  Which user you are talkikng about?

Reply
0 Kudos
dprabhakaran
Enthusiast
Enthusiast

Hi lurims123,

   Please refer to "Add vCenter Server Information -> Prerequisites" section in Chargeback User Guide for details.

Thanks

Diwakar

Reply
0 Kudos
charvoworld
Enthusiast
Enthusiast

hi andi

i am getting a error

unable to connect to the vcenter Database server:jdbc:sqlserver://serverName=vcenter.example.local\\vim_sqlexp;databaseName=chargeback with the specified credential

created new database and db user getting same error

Reply
0 Kudos
PramodCV
Enthusiast
Enthusiast

Hi,

Are you able to connect to the Same DB with same creditials using SQL Management Studio. This isssue is not with CBM Data base its a issue while adding vCenter to CBM.

Thanks

Pramod.

Reply
0 Kudos
campellos
Contributor
Contributor

Awesome Andi!

I had exactly same issue. Solved!

Thanks,

Edgard

Reply
0 Kudos
Khadafee
Contributor
Contributor

I Try to add the view as per your suggest and got this error

Msg 208, Level 16, State 1, Procedure VPXV_HIST_STAT_DAILY, Line 14

Invalid object name 'VPX_SAMPLE_TIME1'.

and the table is already in my database view , ID have DBO access

Need help to solve the issue

Reply
0 Kudos