powered by Jive Software

Creating Rosters

How can I create rosters to all my users via SQL?

Im using MS SQL Server and I’'ve already tryed to insert data in table “jiveRoster” but I had no success on that.

Can anyone explain me on how to create rosters via SQL?

Its urgent!

Thx

Hi Marcio,

Take a look at url=http://www.jivesoftware.org/community/thread.jspa?messageID=101908&#101908this post[/url] for details on how to manually create rosters via SQL. It’'s not that difficult but you do need to make changes to two different tables while Messenger is not running.

Hope that helps,

Ryan

I already did that but when I try to connect it returns an Internal Server Error

When I delete the rosters, everything works fine. But no rosters, of course.

Thanks

Hi Marcio,

Where are you seeing the “Internal Server Error” message? Are there any messages in the log files?

Thanks,

Ryan

PS - Could you post an example of one of your sql insert statements that you’'re using?

When I tryed to log on.

Now I cant even login as admin to see the log files.

I will delete all rosters and users again…

Sure… This one inserts the internal users for each outer user in the system.

declare @username nvarchar(32)

declare @roster nvarchar(32)

declare @rosterID int


Gets the ID

declare c_rosterID cursor

for

select id from jive.jiveId where idtype = 18

open c_rosterID

FETCH NEXT FROM c_rosterID

INTO @rosterID

CLOSE c_rosterID

DEALLOCATE c_rosterID


All agency users

declare c_roster cursor

for

select username from jive.jiveUser

where username not like ‘’%.its’’

OPEN c_roster

FETCH NEXT FROM c_roster

INTO @username

WHILE @@FETCH_STATUS = 0

BEGIN


All internal users

declare c_rosters_data cursor

for

select username from jive.jiveUser

where username like ‘’%.its’’

open c_rosters_data

fetch next from c_rosters_data

into @roster

WHILE @@FETCH_STATUS = 0

BEGIN

– Insere o roster

insert into jive.jiveRoster values

(

@rosterID,

@username,

      @roster + ''@its.tur.br'',

3,-1,-1, @roster)

set @rosterID = @rosterID + 1

UPDATE jive.jiveID SET id = @rosterID WHERE idType = 18

fetch next from c_rosters_data

into @roster

END

CLOSE c_rosters_data

DEALLOCATE c_rosters_data

FETCH NEXT FROM c_roster

INTO @username

end

CLOSE c_roster

DEALLOCATE c_roster

GO

Hi Marcio,

Your script looks ok, but it would be helpful if I could see the actual insert statements it is creating.

Are you trying to add all users to everyone else’'s rosters? If so you might want to look at “Shared Roster Groups” feature of Messenger by signing into the Admin Console and navigating Users/Groups -> Groups -> Create New Group. This feature allows you to place users in groups automatically.

Thanks,

Ryan

Even the users that I’'ve inserted through SQL?

Could you explain me more about the Shared Roster?

This what I want:

Internal user sees all users

External user sees only internal users.

I dont want external users to talk to each other.

How can Shared Roster help me with that???

thx!

Even the users that I’'ve inserted through SQL?

Maybe you could just post a sample of what the data looks like in the jiveUser and jiveRoster tables for a single user.

Could you explain me more about the Shared Roster?

This what I want:

Internal user sees all users

External user sees only internal users.

I dont want external users to talk to each other.

How can Shared Roster help me with that???

Hmm… the shared roster feature would only get you part of the way there. You could create an internal group and place all your internal users in it so they could see each other. Your requirement of only wanting external users to see internal users will require some database manipulation. It should be noted that out-of-the-box Messenger doesn’'t have a way to prevent users from adding other users to their rosters, although you could probably add that feature via a custom plugin.

Thanks,

Ryan

Maybe you could just post a sample of what the data looks like in the jiveUser and jiveRoster tables for a single user.

note that “.its” means an internal user. Any other name after the dot is outer user

jiveUser

username | password | name | email | dates…(doesnt matter, I insert 0 to get lesser possible)

marcio.its | @!#(crypted…) | Márcio | marcio.ti@its.tur.br | …

user.valtat | @!#(crypted…) | user | user@valtat.com.br | …

jiveRoster (Roster Id is ficticious)

rosterID | username | jid | sub | ask | recv | nick |

1234 | user.valtat | marcio.its@its.tur.br | 3 | -1 | -1 | marcio.ti

23465 | marcio.ti | user@valtat.com.br | 3 | -1 | -1 | user.valtat

Your requirement of only wanting external users to see internal users will require some database manipulation.

No problem, i just nee da little help here

It should be noted that out-of-the-box Messenger doesn’'t have a way to prevent users from adding other users to their rosters, although you could probably add that feature via a custom plugin.

No problem. I already did that. They will use JwChat and I delete the add user button :stuck_out_tongue:

I still dont know how to create shared roster.

thx

Maybe you could just post a sample of what the data

looks like in the jiveUser and jiveRoster tables for

a single user.

note that “.its” means an internal user. Any other

name after the dot is outer user

jiveUser

username | password | name | email | dates…(doesnt

matter, I insert 0 to get lesser possible)

marcio.its | @!#(crypted…) | Márcio |

marcio.ti@its.tur.br | …

user.valtat | @!#(crypted…) | user |

user@valtat.com.br | …

jiveRoster (Roster Id is ficticious)

rosterID | username | jid | sub | ask | recv | nick

|

1234 | user.valtat | marcio.its@its.tur.br | 3 | -1 |

-1 | marcio.ti

23465 | marcio.ti | user@valtat.com.br | 3 | -1 | -1

| user.valtat

Are both your external and internal users connecting to the same Messenger installation? If so, their jid’'s should look like @[example.com].

Your requirement of only wanting external users to

see internal users will require some database

manipulation.

No problem, i just nee da little help here

Happy to help.

It should be noted that out-of-the-box Messenger

doesn’'t have a way to prevent users from adding other

users to their rosters, although you could probably

add that feature via a custom plugin.

No problem. I already did that. They will use JwChat

and I delete the add user button :stuck_out_tongue:

Clever.

I still dont know how to create shared roster.

Go to the group section in the Admin Console, create a new group, add your internal users to it, and then select the “Show group in group members’’ rosters” radio button. Now all users in that group will be able to see everyone else in that same group.

Thanks,

Ryan

Great Ryan, 50% done!

Help me just a little bit.

After I create the internal users group, I try to create the outer one.

I create the group with the users and select the Internal Group to be the only group that can see this one, got it? And I add all the users at the same time.

Then my users cant login anymore. It keeps giving me “Not Implemented” error at JwChat.

And after that I still need to share the Internal group with, both, Internal and External group

I dont know how, dont ask me, but i manage to make this thing work once, but I, accidentaly, celaned the DB up

ok, I tryed another things…

1- I Created Internal users group. with the users and checked “Show group in group members’’ rosters”, I also did inform the users

2- I created the external group like the internal.

3- I changed the shared roster of Internal to: “Show group to members’’ rosters of these groups:” And informed both groups.

4- I changed the external roster to be seen only by the internal.

Problems:

  • External still can see each other

  • It got extremely[/b] slow, cuz I got +1200 users. (But by this morning it was fast…)

How do I solve the first problem?

To the second one, I wanted to know if its possible to just inform online (and visible) users.

thx!!!

Hi Marcio,

Problems:

  • External still can see each other

I don’‘t think shared groups will help you solve the first problem. Out-of-the-box there really isn’‘t a good way to configure Messenger so users can be placed in a group and not see each other, so I think you’'re back to having to go back to using SQL.

To the second one, I wanted to know if its possible

to just inform online (and visible) users.

I’‘m sorry, I don’'t know what you mean by “inform”.

Thanks,

Ryan

I don’‘t think shared groups will help you solve the first problem. Out-of-the-box > there really isn’‘t a good way to configure Messenger so users can be placed in a > group and not see each other, so I think you’'re back to having to go back to > using SQL.

What if: I create the group, then I set it shared to all groups, less itself. Does it works?

Btw, how do I manage to do it with SQL?

I’‘m sorry, I don’'t know what you mean by “inform”.

Let me try to clear things out. I want jive to retorn just the online users of the roster. Theres too much offline sow it takes time to load everything.

got it?

thx!!

Hi Marcio,

This is turning into an interesting request.

Playing around with the (shared) groups and sql some more what you want to do is not possible without writing some of your own code to control the shared groups or possibly to intercept and alter roster packets that are sent to specified users or groups. The only way I could configure Messenger to do what you want is to add each external user to their own group and then select the internal group in the “Show group to members’’ rosters of these groups” option of the shared group. Unfortunately, with the number of users you’'re talking about the number of groups would quickly get out of hand and become difficult to manage.

Let me try to clear things out. I want jive to retorn just the online users of the roster.

Theres too much offline sow it takes time to load everything.

got it?

Got it. This would also require you to write some custom code, you’‘d probably have to replace the RosterItemProvider your own version. Unfortunately, the RosterItemProvider isn’'t as easy to swap out as some other classes like you can with the GroupProvider, although there has been url=http://www.jivesoftware.org/community/thread.jspa?threadID=16091a request[/url] to make such a change easier in the future.

Regards,

Ryan

Well Im running out of chances here

If I do what I said, it takes hours to load all the rosters (cuz of the offline ones).

And If I do it differently, the users of the internal group cant connect anymore!!

It may be interesting ( :stuck_out_tongue: ), but Im desperate here!

Regards!

I’‘m sorry, I don’‘t know what else to tell you. What you’'re looking to do is going to require some custom development work, I just see anyway else it can be done.

Regards,

Ryan

Ok ryang,

I managed to get a way so users in the same group can see each other.

But now I’‘ve got a problem. I’'ve got almost 450 groups, and just the first page works.

I mean, I can only see the first 15 groups. If I click on page 2(or other) it doesn’'t work!!!

How can I fix this issue?

Regards!