January 31, 2012 at 1:58 pm
I have hopefully a fairly easy question which I think I am just missing something.
We have a security application that needs to track when a user's access badge has had access removed or added. Now the 3rd party app (which I cannot change their code) handles this from a screen. You can revoke and add access to certain areas of the building in one go and when you hit update the code deletes all of the access and then readds it with the changes.
IE:) I have a badge that has access to 3 areas, I add 2 areas to the badge, the app will perform 3 delete statements and then 5 insert statements... 3 inserts for the original access and 2 inserts for the new access.
What I have done is created 2 triggers and 2 tables.
Tables:
BADGELINK_ACCESS_ADDED
BADGELINK_ACCESS_REMOVED
The delete trigger inserts into the BADGELINK_ACCESS_REMOVED table and the insert trigger inserts into the BADGELINK_ACCESS_ADDED table.
What I need is a query that will give me just the new access based on the info in those 2 tables.
Both tables have:
BADGEKEY -- BADGE NUMBER
ACCLVLID -- ACCESS AREA ID (Server Room, Cafeteria, etc)
AUDITDATETIME -- THE DATETIME THE ACCESS WAS GRANTED OR REVOKED
I have test data in the table with 2 badges, one had 4 areas added and one had one area added.
BADGELINK_ACCESS_REMOVED
BADGEKEY ACCLVLID AUDITDATETIME
10695 10 2012-01-31 11:04:22
10695 12 2012-01-31 11:04:22
1356 10 2012-01-31 13:11:09
1356 17 2012-01-31 13:11:09
1356 19 2012-01-31 13:11:09
1356 22 2012-01-31 13:11:09
1356 34 2012-01-31 13:11:09
BADGELINK_ACCESS_ADDED
BADGEKEY ACCLVLID AUDITDATETIME
10695 10 2012-01-31 11:04:22
10695 12 2012-01-31 11:04:22
10695 17 2012-01-31 11:04:22
10695 19 2012-01-31 11:04:22
1356 10 2012-01-31 13:11:09
1356 17 2012-01-31 13:11:09
1356 19 2012-01-31 13:11:09
1356 22 2012-01-31 13:11:09
1356 34 2012-01-31 13:11:09
1356 47 2012-01-31 13:11:09
Notice access for badge 10695 areas 17 and 19 added..
and access for badge 1356 area 47 was added...
So what I would like to see is 3 rows returned from the BADGELINK_ACCESS_ADDED that do not exist in the REMOVED table...
I am running:
SELECT AA.* FROM BADGELINK_ACCESS_ADDED AA
LEFT JOIN BADGELINK_ACCESS_REMOVED AR
ON (AA.ACCLVLID = AR.ACCLVLID)
WHERE AR.BADGEKEY IS NULL
But I only get the one row from badge 1356 that had one access area added... The other 2 for badge 10695 don't show up.
Anybody can help me in figuring out what I might be doing wrong? Or if you can come up with a better way of grabbing when access has been added or removed from a certain badge that would be great too.
Thanks
January 31, 2012 at 2:09 pm
I think I may have just figured it out...
I need to add the badgekey to the join clause:
SELECT AA.* FROM BADGELINK_ACCESS_ADDED AA
LEFT JOIN BADGELINK_ACCESS_REMOVED AR
ON (AA.ACCLVLID = AR.ACCLVLID
AND AA.BADGEKEY = AR.BADGEKEY)
WHERE AR.BADGEKEY IS NULL
I am going to play around with it... I may need to add the AUDITDATETIME field in at some point and then reverse the query so I can do the same when an access area has been removed from a badge but this is a good start.
February 1, 2012 at 1:03 am
jamie_collins (1/31/2012)
I am going to play around with it... I may need to add the AUDITDATETIME field in at some point and then reverse the query so I can do the same when an access area has been removed from a badge but this is a good start.
Yep, you got it, but you'll want to add in AuditDateTime too to avoid errors when the same user is in the table twice because of further modifications. You could also inverse the query and union it to itself to show removals.
Btw, if you take a look at the first link in my signature, it'll help you setup your schema/data for next time and more people will be able to help you faster. Remember we're volunteers, the easier you make it on us, the easier it is for us to help. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 1, 2012 at 8:43 am
Take a look at EXCEPT which performs a set difference, which is what you are looking for. It makes it much clearer that you are doing a set difference and is much cleaner as the number of fields that your comparing grows larger.
SELECT AA.ACCLVLID, AA.BADGEKEY, AA.AUDITDATETIME
FROM BADGELINK_ACCESS_ADDED AA
EXCEPT
SELECT AR.ACCLVLID, AR.BADGEKEY, AR.AUDITDATETIME
FROM BADGELINK_ACCESS_REMOVED AR
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 1, 2012 at 10:35 am
drew.allen (2/1/2012)
Take a look at EXCEPT which performs a set difference, which is what you are looking for. It makes it much clearer that you are doing a set difference and is much cleaner as the number of fields that your comparing grows larger.
SELECT AA.ACCLVLID, AA.BADGEKEY, AA.AUDITDATETIME
FROM BADGELINK_ACCESS_ADDED AA
EXCEPT
SELECT AR.ACCLVLID, AR.BADGEKEY, AR.AUDITDATETIME
FROM BADGELINK_ACCESS_REMOVED AR
Drew
As long as you have an exact match for the columns being returned from the statement I'll agree with this, otherwise you have to get into subquerying and it's not as optimal.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply