We have a XML file which contain the server access logs, we need to identify the users who connected to the SFTP server on a certain date.
The format of the xml file is as below:
Create a table dbo.tablexml which contains an xml datatype column
Import the XML file into xmlcl using the openrowset function as shown below.
INSERT INTO dbo.tablexml(xmlcl) SELECT * FROM OPENROWSET (BULK 'I:\xml\FileDrop\dailylogfile_export.xml', SINGLE_BLOB) AS x;
Note: The SINGLE_BLOB keyword imports the entire XML file to the XML datatype column.
You can then query the xmlcl as the regular column.
Select * from dbo.tablexml
Now let us query the XML data from the table to produce a transact SQL like result set. Execute the below XQuery as shown below.
SELECT pref.value('(log_time/text())', 'varchar(20)') as logtime ,pref.value('(description/text())', 'varchar(250)') as description ,pref.value('(service/text())', 'varchar(25)') as service ,pref.value('(sessionid/text())', 'varchar(25)') as sessionid ,pref.value('(type/text())', 'varchar(10)') as type ,pref.value('(severity/text())', 'varchar(2)') as severity ,pref.value('(user/text())', 'varchar(100)') as 'user' ,pref.value('(host/text())', 'varchar(20)') as host ,pref.value('(lstnconnaddr/text())', 'varchar(50)') as lstnconnaddr ,pref.value('(cliconnaddr/text())', 'varchar(50)') as cliconnaddr ,pref.value('(sguid/text())', 'varchar(100)') as sguid FROM tablexml cross apply xmlcl.nodes('/log/entry') logdata(pref)
The XML data type in SQL Server defines a number of methods that can be called on it. One of these is “nodes” – and what this does is basically select a list of XML nodes that match an XQuery expression. Knowing this, look at above statement:
This is using the path “/log/entry’” to ensure that all “entry” nodes that exist under the “log” are selected. The result is aliased as a table named logdata, and each resulting XML node will be output as a separate row, in the “pref” column.
The value() function to extract a specific value from the XML, and convert it to a SQL Server data type;
pref.value('(user/text())', 'varchar(100)') as 'user'
The “text()” function here retrieves the inner text from within the XML “Name” node.
The “” suffix acts as an indexer, and fetches the first result matched. In our case, there’s only one name node per entry, but we still need to make sure SQL Server knows this too.
Finally, the second argument is the data type it should be converted to.
Active Directory Service Interfaces, also known as ADSI, is a set of COM interfaces used to access the directory services to retrieve data. Though it is predominantly used by network administrators and system administrators, there are situations where SQL Server Database administrators or the application that uses SQL Server as the backend needs to get data from ADSI.
This article illustrates how to use SQL Server to access and retrieve data from Active Directory, although we can import active directory data through SSIS, there are many limitation.
1. Limitation in datatype (ntext) and length;
2. Some fields(eg: GUID and Description) are brought in as System.Object.
The easiest way for importing from AD is still trough the T-sql although it is not easy to handle the date fields.
Sample T-Sql Script:
–Enable distributed queries
EXEC sp_configure ‘show advanced options’, 1
EXEC sp_configure ‘ad hoc distributed queries’, 1
–Create linked server
if exists(select srvname from master..sysservers where srvname=‘ADSI’) begin
EXEC sp_droplinkedsrvlogin ‘ADSI’,null
EXEC sp_dropserver ‘ADSI’
EXEC sp_addlinkedserver ‘ADSI’, ‘Active Directory Service Interfaces’, ‘ADSDSOObject’, ‘adsdatasource’
EXEC sp_addlinkedsrvlogin ‘ADSI’,true
SELECT CAST(objectGUID as Uniqueidentifier) [GUID]
, samaccountname as Login
, UserPrincipalName as PrincipalName
, givenname as FirstName
, sn as LastName
, mail as Email
, cast(uSNChanged as bigint) as ChangeSequenceNumber
, cast(uSNCreated as bigint) as CreateSequenceNumber
, l as City
, st as Province
, c as Country
, TelephoneNumber as Phone
, facsimileTelephoneNumber as Fax
–Query AD from OpenRowSet
FROM ”LDAP://Market /DC=market,DC=financial,DC=local”
WHERE objectCategory = ”person” AND objectClass = ”user” and UserAccountControl = ”512”’)
where sn is not null and givenname is not null and mail is not null
select * from #ADSI
Indicates that a contact is not a domain user
Normally this is the same value as the SamAccountName, but could be different if you wished. Needed for mail enabled contacts.
@ e-mail address. Note that SMTP is case sensitive. All capitals means the default address.
Here is where you set the MailStore
Home Folder : connect. Tricky to configure
Useful in some cultures.
This is a mandatory property, SamAccountName = guyt. The old NT 4.0 logon name, must be unique in the domain.
Used to disable an account.
• 512 – Enable Account
• 514 – Disable account
• 544 – Account Enabled – Require user to change password at first logon
• 546 – Account Enabled – Require user to change password at first logon
• 4096 – Workstation/server
• 66048 – Enabled, password never expires
• 66050 – Disabled, password never expires
• 66080 – Enables, password never expires, password not required.
• 66082 – Disabled, password never expires, password not required.
• 532480 – Domain controller
• 590336 – Enabled, User Cannot Change Password, Password Never Expires
Often abbreviated to UPN, and looks like an email address. Very useful for logging on especially in a large Forest. Note UPN must be unique in the forest.
User’s home page.
The property value is represented using the ADSI IADsLargeInteger. The IADsLargeInteger from the ADSI call represents the number of 100 nanosecond intervals since Jan 1, 1601 (UTC) and “value of 0 or 0x7FFFFFFFFFFFFFFF (9223372036854775807) indicates that the account never expires”.
You can do that using replace and len. Count number of x characters in str: eg: select Data, LEN(Data)-LEN(REPLACE(Data,'"','')) As NumOfQuotes, LEN(Data)-LEN(REPLACE(Data,',','')) As NumOfComas from Staging.SourceADData Reference Link: http://stackoverflow.com/questions/9789225/number-of-times-a-particular-character-appears-in-a-string
Note: Enter or Change Line character is CR(Carriage return), the Dec# is 13.
Extended ASCII Codes
In T-SQL, we can always use function ASCII(SpecialCharacter) to get the ASCII dec values of the special characters, and use Char(ASCIICode) to get the characters.
set @Column=’abcd’+CHAR(13)+’erwehjk$^& \ |’
set @Column=replace(REPLACE(@Column,CHAR(13),’@’ ),CHAR(9),’–>’)
IF OBJECT_ID(‘tempdb..#tmpTable1’) IS NOT NULL
DROP TABLE #tmpTable1
We sometimes want to get a query result of a dataset and a few values as well, we can use a Stored Procedure with multiple output parameters.
Create PROCEDURE [dbo].[GetQueryWithMultipleOutputs]
@minFY varchar(16) output,
@maxFY varchar(16) output,
@pp varchar(2) output,
@pEnddt varchar(20) output
select * from dbo.prog
select @minFY=min(FiscalYear),@maxFY=max(FiscalYear),@pp=max(FiscalPeriod), @pEnddt =convert(varchar(20),max(FiscalPeriodEndDate),107) from #FactData
SET NOCOUNT OFF
But if you execute the SP as below, it only shows the result set.
exec [dbo].[GetQueryWithMultipleOutputs] ‘2011-04-01′,’2013-03-31’, output, output, output, output
To display both query result, you need to pass the output parameters to local variables:
@a varchar(16) ,
@b varchar(16) ,
@c varchar(2) ,
exec [dbo].[GetQueryWithMultipleOutputs] ‘2011-04-01′,’2013-03-31’, @a output, @b output, @c output, @d output
select @a, @b, @c, @d
Note: this kind of Stored Procedures cannot be used in SSRS.
Put a semi-colon infront of the first CTE, and a comma infront of following CTES.
with CTETest2(Col11, Col12, Col13, Col14)
with CTETest3(Col31, Col32, Col33)