Monday 17 February 2014

How to query Active Directory from SQL server

SET UP a Linked server with name ADSI to make this work (shown below)

clip_image002

clip_image002[4]

clip_image002[6]

Also further on you'll see the use of the userAccountControl field, which is a flags field that stores user account details. The list of which flags are which can be found here...

http://msdn.microsoft.com/en-us/library/windows/desktop/ms680832(v=vs.85).aspx

 

use [utilities]
SET NOCOUNT ON;

-- Ensure database link to AD called ADSI exists...

IF object_id('[Utilities].[dbo].[AccountSyncCheck]') IS NOT NULL DROP TABLE [dbo].[AccountSyncCheck]
CREATE TABLE [dbo].[AccountSyncCheck](
      [LOGINID] [varchar](100) collate database_default NOT NULL,
      [EMAILADDRESS] [varchar](255) collate database_default NULL,
      [EMPLOYEEID] [varchar](10) collate database_default NULL,
      [FIRSTNAME] [varchar](255) collate database_default NULL,
      [LASTNAME] [varchar](255) collate database_default NULL,
      [DEPARTMENT] [varchar](255) collate database_default NULL,
      [OFFICENAME] [varchar](255) collate database_default NULL,
      [DISPLAYNAME] [varchar](255) collate database_default NULL,
      userAccountControl      int,
CONSTRAINT [PK_AccountSyncCheck_LOGINID] PRIMARY KEY CLUSTERED
(
      [LOGINID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

IF object_id('tempdb..#temp') IS NOT NULL  DROP TABLE #temp;
CREATE TABLE #Temp
(
    samaccountname             VARCHAR (100) ,
    givenname                  VARCHAR (255)  NULL,
    sn                         VARCHAR (255)  NULL,
    mail                       VARCHAR (255)  NULL,
    department                 VARCHAR (255)  NULL,
    physicalDeliveryOfficeName NVARCHAR (255) NULL,
    employeeid                 VARCHAR (15)   NULL,
    displayname                VARCHAR (255)  NULL,
    userAccountControl              int null
);


DECLARE @sql      AS VARCHAR (MAX)
DECLARE @i        AS INT = ASCII('A');

WHILE @i <= ASCII('Z')
    BEGIN
        SET @sql = 'SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname,userAccountControl
                                    FROM OPENQUERY( ADSI,
                        ''SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname,userAccountControl
                                          FROM ''''LDAP://dc=mydomain,dc=com'''' 
                            WHERE objectCategory = ''''Person'''' AND objectClass= ''''user'''' 
                            AND         userprincipalname = ''''*'''' AND mail = ''''*'''' AND SN = ''''*'''' 
                            AND         samaccountname >= '''' ' + char(@i) + '  ''''  and  samaccountname < '''' ' + char(@i + 1) + ' ''''  
                            '') ';
PRINT @SQL
        INSERT INTO #Temp
        EXECUTE (@sql);
        SET @i = @i + 1;
    END

-- deal with oddball characters
SET @sql = 'SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname,userAccountControl
                        FROM OPENQUERY( ADSI,
                        ''SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname ,userAccountControl
                        FROM ''''LDAP://dc=mydomain,dc=com'''' 
                        WHERE objectCategory = ''''Person'''' AND objectClass= ''''user'''' 
                        AND         userprincipalname = ''''*'''' AND mail = ''''*'''' AND SN = ''''*'''' 
                        AND         samaccountname < ''''A''''
                        '') ';

INSERT INTO #Temp
EXECUTE (@sql);

SET @sql = 'SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname,userAccountControl
                        FROM OPENQUERY( ADSI,
                        ''SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname,userAccountControl
                        FROM ''''LDAP://dc=mydomain,dc=com'''' 
                        WHERE objectCategory = ''''Person'''' AND objectClass= ''''user'''' 
                        AND         userprincipalname = ''''*'''' AND mail = ''''*'''' AND SN = ''''*'''' 
                        AND         samaccountname >= ''''ZZZZZZZ''''
                        '') ';

INSERT INTO #Temp
EXECUTE (@sql);

IF (SELECT COUNT(*)
    FROM   #Temp) > 0
    BEGIN
        truncate table AccountSyncCheck
        INSERT INTO AccountSyncCheck (LOGINID,EMAILADDRESS,EMPLOYEEID,FIRSTNAME,LASTNAME,DEPARTMENT,OFFICENAME,DISPLAYNAME, userAccountControl)
        SELECT   samaccountname,
                 mail,
                 LEFT(employeeid, 10),
                 givenname,
                 sn,
                 department,
                 physicalDeliveryOfficeName,
                 displayname,
                 userAccountControl
        FROM     #Temp
        ORDER BY samaccountname;
    END

SET NOCOUNT OFF;


--select top 10 * from beacon.dbo.rraperson
select *
from utilities.dbo.AccountSyncCheck

/*
SELECT     
'active RRA person(s) with invalid email address' AS     alert,
iv.PersonID, InternetAddress, Preferred, OfficeID, Loginname, Active, Responsibility, RetiredDate
from beacon.dbo.IV_PersonInternetAddressCompletePreferred iv
inner join beacon.dbo.rraperson r on r.personid = iv.personid
where ISNULL(iv.internetaddress, '') <> ''
--and iv.internetaddress not like '%@mydomain.com%' and iv.internetaddress not like '%@russreyn.com%'
and active = 1
and iv.personid not in (10000165,40002053,900008913,40001263,40001285)
*/

-- Active in Beacon disabled in AD
select *
from utilities.dbo.AccountSyncCheck

-- find active where they should be inactive
select *
from AccountSyncCheck b
inner join beacon.dbo.rrapersonsummary r on r.loginname = 'mydomain\' + b.loginid 
where r.Active = 1
and useraccountcontrol & 2 = 2


-- find beacon spelling mistakes in email addresses
select b.loginid, pcp.PersonID, ActiveDirectoryEmailAddress = b.emailaddress, BeaconEmailAddress = pcp.InternetAddress
from AccountSyncCheck b
inner join beacon.dbo.rrapersonsummary r on r.loginname = 'mydonamin\' + b.loginid 
inner join Beacon.dbo.IV_PersonInternetAddressCompletePreferred pcp on pcp.PersonID = r.id
where pcp.InternetAddress <> b.emailaddress
and r.Active = 1
and useraccountcontrol & 2 = 0

Friday 14 February 2014

Tuesday 4 February 2014

How to connect to wifi on the London Underground

From here...

http://help.ee.co.uk/system/selfservice.controller?CONFIGURATION=1016&PARTITION_ID=1&secureFlag=false&TIMEZONE_OFFSET=&CMD=VIEW_ARTICLE&USERTYPE=1&ARTICLE_ID=341401

It's easy to connect to WiFi on the London Underground.

If you're an active EE, T-Mobile or Orange mobile customer then just text EEWIFI to 9527 and we’ll send you a password to access the service when you next go on the Underground.

Once you’re underground in a participating station, just follow these simple steps to connect:

1. On your device, turn WiFi on
2. Select Virgin Media WiFi from the list of available networks
3. Open your web browser and follow the prompts on the Virgin Media portal
4. Select EE and register using your mobile phone number and the password that we sent to you by text

Once you’ve connected to the WiFi once, you’ll automatically connect every time you enter a WiFi enabled station (as long as WiFi is switched on on your device).

Monday 3 February 2014

How to fix a an old Sony Playstation 2

See this video on line for how to take it apart (no more difficult than dismantling a PC really)

https://www.youtube.com/watch?v=5GoJWS_1hmw

Then just use an old toothbrush to clean the chrome spiral that helps the laser up and down the runner, and use a cotton bud dipped in some ethanol (or any alcohol solution), to clean the laser head itself.

Blow out any dust you can see internally, and put it back together.

Did this with our decade old device last weekend, and now the machine is working better than it did when we originally bought it!

How to find the last interactive logons in Windows using PowerShell

Use the following powershell script to find the last users to login to a box since a given date, in this case the 21st April 2022 at 12pm un...