microsoft.public.sqlserver.notificationsvcs
[Top] [All Lists]

Re: Looking to subscribe "roles" rather than "users"

Subject: Re: Looking to subscribe "roles" rather than "users"
From: Joe Webb
Date: Sat, 11 Mar 2006 07:38:31 -0600
Newsgroups: microsoft.public.sqlserver.notificationsvcs
Going back to one of your original ideas, you could also create a
dummy subscription for each role and a custom delivery protocol that
uses SMTP. Then the custom delivery protocol could query the database
for role membership.

I think that maybe a bit easier to implement. Plus all notification
logic remains in the SQLNS side of things. 

My $0.02 worth...

Joe


-- 
Joe Webb
SQL Server MVP
http://www.sqlns.com


~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 

I support PASS, the Professional Association for SQL Server.
(www.sqlpass.org)


On 10 Mar 2006 09:01:05 -0800, "CoreyB" <[email protected]> wrote:

>Or slight modification - if you have all the users in one column
>(separated by commas, as you mentioned) - you might have to use..
>
>event.UserIDs LIKE '%' + subscriptions.UserID + '%'
>
>right??  This will match any try to match your comma-separated list
>with a specific subscription userid.  If you comma-separated values are
>all in one column called userids, then you'll have to use LIKE to match
>on those substrings.
>
>matt roberts wrote:
>> Hello Tony,
>>
>> Thanks for the suggestions.
>>
>> I understand the second option you suggested - about setting up subscriptions
>> that represent the role memberships. Like you said there's overhead involved
>> in maintaining this but its possible.
>>
>> I've got an idea that is based on your first suggestion, although doesn't
>> involve sending one event for each member.
>> My idea is this (and I've not yet prototyped it, so I can't guarantee I 
>> haven't
>> missed anything);
>>
>> 1) All users in my application subscribe to recieve notifications about new
>> records, for their userID.
>> 2) When a new record is added, an event is created that includes the 
>> recordID,
>> and a comma-seperated list of users who should get the notification.
>> 3) The subscription matches on subscriptions.UserID IN event.UserIDs
>>
>> And thats it!
>>
>> I *think* its going to work, if you can spot any holes in this then feel
>> free to flame me ;)
>>
>> Once more, thanks to everyone for the advice - its very appreciated :)
>>
>>
>>
>> > Another option would be to use the data you know about  the users and
>> > the roles to resolve the role to each of the current members in that
>> > role.  For each of the members generate events for each of the
>> > individual subscribers which are already known to NS.  This may be a
>> > large number of events in some cases depending on your role
>> > membership, but the control stays within your application and no
>> > changes required to what is carried with NS.
>> >
>> > Another option still is to set up a number of subscriptions which
>> > would represent the role memberships for the users - this would
>> > involve a greater amount of subscription management as users changes
>> > the roles they play, but could be work as well.  It would drop the
>> > number of events coming through to one containing say the RoleID and
>> > all subscrbers to a role with id = # would then recieve it.  There may
>> > be limitations here to how customized the mail itself could be based
>> > on receipient.  Did something quite similar to this with account data
>> > where an event would come through for the account and all subscribers
>> > to events for that account would get a copy of the mail (same mail to
>> > each subscriber).
>> >
>> > HTH
>> > --Tony
>> > "matt roberts" wrote:
>> >
>> >> Hello Joe & CoreyB,
>> >>
>> >> Thanks for the reply.
>> >>
>> >> I like the simplicity of the exhange idea, but........unfourtunately
>> >> creating exchange distribution lists won't work for this app. The
>> >> reason is that its a web app that we provide to many customers, who
>> >> install it onn their intranet. We have no control over these
>> >> intranets, and cannot setup distribution lists on any of them. Also,
>> >> the "roles" change quite often, as people come and go in the
>> >> application, so it create a big overhead to sync the exhchange
>> >> distributions with them.
>> >>
>> >> Another idea I am considering is to get my web app to pass all the
>> >> emails
>> >> in the event when it passes one to SSNS. That way, I could allow all
>> >> users
>> >> to subscribe to the notification, and then only send out
>> >> notifications when:
>> >> "subsriber.emailaddress IN event.allemailaddresses"....
>> >> Does that sound feasible?
>> >> Thanks!
>> >> Matt.
>> >>> Hi Matt -
>> >>>
>> >>> Thanks for the kind words regarding a previous post of mine!   :)
>> >>>
>> >>> CoreyB's suggestion of creating an Exchange distribution list for
>> >>> your roles would be the easiest solution to implement. That way the
>> >>> subscriber and associated subscriptions would accurately reflect
>> >>> what you want to have happen.
>> >>>
>> >>> Will that work for you?
>> >>>
>> >>> HTH...
>> >>>
>> >>> ~~~
>> >>> Get up to speed quickly with SQLNS
>> >>> http://www.amazon.com/exec/obidos/tg/detail/-/0972688811
>> >>> I support PASS, the Professional Association for SQL Server.
>> >>> (www.sqlpass.org)
>> >>> On 8 Mar 2006 08:53:41 -0800, "CoreyB" <[email protected]> wrote:
>> >>>
>> >>>> My suggestion would be to tack on something in your mail system
>> >>>> (exchange, groupwise, etc. whatever you're using) to have these
>> >>>> "roles" existing as email lists.  User1, User2, User3 could be
>> >>>> grouped in EmailList1 in exchange.  Then in notification services,
>> >>>> the subscriber device would be the email address for EmailList1.
>> >>>> This would be easier than doing a custom delivery protocol.
>> >>>>
>> >>>> Or maybe if you don't have exchange or similar, you can setup
>> >>>> forwarders on your email server to forward all messages to
>> >>>> EmailList1 to User1, User2, User3.
>> >>>>
>> >>>> matt roberts wrote:
>> >>>>
>> >>>>> Hi all,
>> >>>>>
>> >>>>> I'm starting designing a SSNS application to support our own web
>> >>>>> based app. I've just got the Shyam book for SSNS 2005, which looks
>> >>>>> pretty good so far. Anyway, I have a specific requirement that is
>> >>>>> making me nervous, and I would appreciate any responses:
>> >>>>>
>> >>>>> In our web app, we have users and roles. A role is a group of
>> >>>>> users, for example "Data Inputters", or "Auditors". As part of the
>> >>>>> SSNS solution, I need to be able to "subscribe" roles as well as
>> >>>>> users. So for example all "Auditors" want to be notified when a
>> >>>>> record appears in table X.
>> >>>>>
>> >>>>> I've not seen any examples of this in the book or in the books
>> >>>>> online. In mulling over how this could be done, so far I can only
>> >>>>> think of the following solution:
>> >>>>>
>> >>>>> 1) Subscribe a "dummy" user, (my role)
>> >>>>> 2) Create a custom delivery protocol, that takes notifications
>> >>>>> destined for
>> >>>>> my role subscriber, and instead passes the notification on to all
>> >>>>> users who
>> >>>>> are members of that role.
>> >>>>> But this solution "feels" wrong to me, quite complex to develop
>> >>>>> and
>> >>>>> maybe even not possible.
>> >>>>> I've looked through the existing posts here, and a few posts seem
>> >>>>> to be quite similar but don't seem to provide a way forward. A
>> >>>>> very helpful chap called Joe Webb wrote this in response to a
>> >>>>> similar posting:
>> >>>>>
>> >>>>>> That's true. SQLNS is a develoment framework so you cannot really
>> >>>>>> pick
>> >>>>>>
>> >>>>> and choose what portions you'd like to implement.
>> >>>>>
>> >>>>>> However, in many (or most) cases you can pretty easily integrate
>> >>>>>> SQLNS
>> >>>>>>
>> >>>>> into your existing process with little duplication.
>> >>>>>
>> >>>>>> For instance, since you already have your data in a specific
>> >>>>>> table, you
>> >>>>>>
>> >>>>> can use that information as the basis for your events.
>> >>>>>
>> >>>>> So I'm wondering if this means that since I already have and
>> >>>>> maintain a table
>> >>>>> of users, and details about which users live in which roles, then
>> >>>>> somehow
>> >>>>> I can tell SSNS to use this data....or perhaps I got that wrong.
>> >>>>> Any thoughts/opinions appreciated!
>> >>>>> Matt Roberts
>> >>>>> http://www.geekswithblogs.net/mattrobertsblog

<Prev in Thread] Current Thread [Next in Thread>