AL Snippets: Custom Snippets for AL Extension
interesting custom Snippets for AL Extension developed by Krzysztof Bialowas, I report the post and the installation link because I consider it interesting and useful.
16 snippets are available including those one for Events. The best is to go project wiki to check what they can: https://github.com/mynavblog/Snippets-for-AL/wiki
Marketplace Install link: https://marketplace.visualstudio.com/items?itemName=mynavblog-kbialowas.al-snippets
Snippets-for-AL: Currently there are below Snippets
Triggers:
ttriggerOnValidate, ttriggerOnLookup
Statements:
tifnot, tifnotelse, tifRecxRec, tifRecxRecempty, trepeatnext, tnotisemptyelse, tnotisempty
Table Fields:
tflowfield, tfieldcodetabrel
Page Fields:
tfieldpagetooltip
Post Source http://www.mynavblog.com/2018/03/16/navdevinfo-2-new-snippets-for-al/
Good !
NAV GDPR Tools In Action (@NAV 2018 CU4)
Hi Guys,
I have published several posts on the GDPR, we are currently implementing it from several customers, many people still ask me for information on the subject. I have already talked about what to do for the old versions of NAV (which are no longer under maintenance)
But yesterday … the CU4 of NAV 2018 was released (and the other CUs for NAV 2015. 2016, 2017) which includes the TOOLS useful for GDPR. The technical\application modifications have been introduced since CU3, from the CU4 onwards all the necessary tools will be inserted.
PS: Soon my article relational to the GDPR Topic will be published on NAVUG Magazine (article prepared before the CU4 of NAV 2018, therefore generic)
In this post (as promised yesterday in the post on the publication of the CU4 of NAV 2018) i will explain what has been done in CU 4 and how the GDPR tools work.
NAV 2018 CU4 GDPR Tools
If you set this profile you can access to “Data Privacy” menu
PROFILE -> Administration & Security
DATA CLASSIFICATIONS -> Data Classification Worksheet
From this it is possible to classify sensitive data (as described in the GDPR Whitepaper issued by Microsoft), field by field for standard and other custom tables.
It is also possible to set the sensitivity of the data in a massive way, Microsoft has set a standard (base) classification that must be verified and validated.
EXAMPLE
Set Data Privacy on Customer Table
SETUP DATA CLASSIFICATION (WIZARD)
It is possible to export and import from Excel, importing from Excel is useful if you have made a map of the data required for processing (sensible and personal date types).
WIZARD IN ACTION!
Choose the right option!
SAVE TO EXCEL
DATA PRIVACY UTILITY
With this button you can execute two functions:
Export data of the subject
Creation of a configuration package (yes, Microsoft has decided to use the old package introduced in NAV 2013 to export import data and manage the cancellation of data…)
Example Creation of a configuration Package for “Employee” table
SENSIBLE DATA DECTIONFILTEREXPORT
Now you can export sensitive data detected in the system using this Wizard, you can filter what you need.
TYPE OF DATA
FILTER ON DATA SENSIVITY: “ONLY SENSITIVE DATA” in this case
GENERATE PREVIEW
You can extract data in Preview Mode before exporting it, you can check it before exporting it to Excel
EXPORTING TO EXCEL FUNCTION
After exporting to Excel the system logs what has been done (AUDIT & LOGGING FEATURE)
CREATION OF NEW CONFIGURATION PACKAGE
it is possible to create a new configuration package from this wizard
And.. BINGO!!!
..THIS IS A PACKAGE WITH SENSITIVE DATE
Should I encrypt it? NO, it’s not clear, it’s already in binary code.
DATA PRIVACY ACTIVITY LOG
Each activity is tracked and written in the log (as required by the GDPR)
CHANGE LOG ENTRY
You can activate use the old Change Log Entry to track changes to the data (function existing from the first versions of NAV). Take a look at my old post if you do not use it.
My Post about “Track activities Change” in NAV https://robertostefanettinavblog.com/2015/06/09/nav-2015-tracking-sessions-users-activity-change-log/
STAY TUNED TO NEXT NAV 2018 CUs.. CU5 and CU6 .. and more ?
DYNAMICS NAV 2018 CU4 IS AVAILABLE
Cumulative Update 04 includes all application and platform hotfixes and regulatory features that have been released for Microsoft Dynamics NAV 2018.
The cumulative update includes hotfixes that apply to all countries and hotfixes specific to the following local versions:
Where to Find Cumulative Update
From Download Center https://www.microsoft.com/en-us/download/details.aspx?id=56822
SOURCE https://blogs.msdn.microsoft.com/nav/2018/04/12/cumulative-update-04-for-microsoft-dynamics-nav-2018-has-been-released/
GDPR Compliance (WAVE 2)
this CU4 should contain (as it is already for Business Central) updates for GDPR Compliance.
I will do some tests to see what has been done, you will find it in the next post !
Be patient and stay tuned!
In old NAV releases (between NAV 2013 and NAV 2016) is very difficult to show and resolve blocking issues.
IN NAV 2017
As a new feature in Dynamics NAV2017, you can configure that information about database deadlocks is written to the Windows Event Log. If database deadlock logging is enabled, Dynamics NAV2017 will log the following:
https://blogs.msdn.microsoft.com/nav/2016/11/24/database-deadlock-detection-monitoring-in-dynamics-nav-2017/
“HOW TO” FOR OLD NAV RELEASES
In NAV Classic Client
In classic client you could see the active database sessions and blocking information along with the ability to kill sessions if necessary. From NAV 2013 to NAV 2016 is not possible to detect locked By SessionUsers.
Solution #1 – Use SQL Server Monitoring features (SQL Profiler etc.)
Solution #2 – call sp_who2 stored procedure from SQL Command View
Solution #3 – link a SQL View to NAV Table
I will try to explain Solution #2 and Solution #3
Solution #2 : “exec sp_who2”
— SHOW SPID THAT BLOCK
This gives a dump of all current user activity. There is a column called BlkBy, which stands for Blocked By. That gives you the SPID (SQL Process ID) of the person blocking. Find that entry for the BlkBy SPID in this list, and now you know the user.
“HOW TO” TEST – CREATE DEADLOCKS IN NAV
To create deadlock we need two codeunits: example cdu 50002 e 50003 (identical, created by SAVEAS) running in two NAV sessions.
DETECTING DEADLOCKS ON SQL
Call Stored Procedure: exec sp_who2
RESULTS – SPID 74 is DEADLOCKED By SPID 80 (in this case the same user login but from another session
Other Nice Scripts (for Command View or from Powershell)
— LOCKING DETAILS
SELECT *
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
— LOCKING DETAILS SUMMARY
SELECT session_id, wait_duration_ms, wait_type, blocking_session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id <> 0
Solution #3: link a SQL View to NAV Table
THE SOLUTION: USE THE TANDARD SYSTEM SQL VIEW USUFUL TO SHOW LOCKS&DEADLOCKS, YOU CAN USE AS LINKED VIEW IN NAV, TAKE A LOOK BELOW FOR IMPLEMENTATION
“HOW TO” STEPS
OBJECT ARE LINKED AND ON MY GITHUB PAGE
LOCKSBOBV_Deadlocks_
https://github.com/rstefanetti/NAV_SQL
You are Welcome!
GDPR, NAV Data discovering from SQL Server, Part I
Hi people, The GDPR deadline is approaching!
…for users who use older versions of NAV (and who do not want to upgrade NAV or purchase third-party tools) it may be useful to have useful functions to search for data (in NAV SQL Server Database). Having a tool that allows raw data to be extracted (data hypothesized as sensitive) is already something.
I show this simple solution for searching the data in NAV reading from SQL Server.
Elements:
SQL Server Stored Procedure: “SP_SearchNAVData2018”
USE [Demo Database NAV ITA (11-0)] GO
/****** Object: StoredProcedure [dbo].[SP_SearchNAVData2018] Script Date: 03/04/2018 16:19:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
ALTER PROCEDURE [dbo].[SP_SearchNAVData2018] ( @SearchStr nvarchar(100) )
AS BEGIN CREATE TABLE #Columns (ColumnID integer not null , ColumnName nvarchar(370)); CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)); SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) DECLARE @ColumnID integer ; DECLARE @sql_string nvarchar(256); DECLARE @Text AS VARCHAR(100) DECLARE @Cmd AS VARCHAR(100) DECLARE @CategoryID integer; SET @ColumnID = 1 ; SET @TableName = ”; SET @SearchStr2 = QUOTENAME(‘%’ + @SearchStr + ‘%’,””); SET @ColumnName = ” SET @sql_string = ” SET @CategoryID = 0;
–DELETE LAST SEARCH DATA DELETE [dbo].[CRONUS Italia S_p_A_$Privacy Data Search Output]
–TABLE CURSOR DECLARE db_cursor CURSOR FOR SELECT [Table] FROM [CRONUS Italia S_p_A_$Privacy Tables Configurator]
OPEN db_cursor FETCH NEXT FROM db_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0 BEGIN DECLARE fields_cursor CURSOR FOR SELECT (QUOTENAME(COLUMN_NAME)) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = PARSENAME(@TableName, 1) AND DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’, ‘nvarchar’, ‘int’, ‘decimal’)
–FIELD CURSOR OPEN fields_cursor FETCH NEXT FROM fields_cursor INTO @ColumnName PRINT @SearchStr + ‘ Foud IN: ‘ — TARGET SEARCH
WHILE @@FETCH_STATUS = 0 BEGIN IF @ColumnName IS NOT NULL BEGIN
–SQL PREPARE SET @sql_string = ”; SET @sql_string = ‘IF EXISTS (SELECT * FROM ‘ + @TableName + ‘ WHERE ‘ + @ColumnName + ‘ LIKE ”%’ + @SearchStr + ‘%”) PRINT ”’ + @TableName + ‘, ‘ + @ColumnName + ””
— DATA PRINT RESULTS EXECUTE(@sql_string)
— DATA WRITE IN TABLE DECLARE @DynamicSQl varchar(250); DECLARE @ColumnLocal varchar(50); SET @DynamicSQl = ‘SELECT * FROM ‘ + @TableName + ‘ WHERE ‘ + @ColumnName + ‘ LIKE ”%’ + @SearchStr + ‘%”’; EXEC (@DynamicSQl)
If @@RowCount > 0 BEGIN PRINT ‘Entrato’ — EXEC(@SomeOtherSQLStatement) INSERT INTO [dbo].[CRONUS Italia S_p_A_$Privacy Data Search Output] ([Table] ,[Field] ,[Search Results]) VALUES (@TableName ,@ColumnName ,@SearchStr) END END
FETCH NEXT FROM fields_cursor INTO @ColumnName END — END Fields Cursor
CLOSE fields_cursor DEALLOCATE fields_cursor
FETCH NEXT FROM db_cursor INTO @Tablename END –END db Cursor
CLOSE db_cursor DEALLOCATE db_cursor RETURN END
NAV OBJECTS “GDPRBOB” TAG
TABLES ON SQL
“HOW-TO RUN” FROM SQL SERVER
#1 – By SQL Query Window, Example:
— RUN DATA SEARCH
EXEC [dbo].[SP_SearchNAVData2018] ‘Computer’; EXEC [dbo].[SP_SearchNAVData2018] ‘Roberto’
— TABLES TO CHEK
SELECT [Table] FROM [CRONUS Italia S_p_A_$Privacy Tables Configurator]
RESULTS
“HOT-TO RUN” FROM NAV
#2, From NAV with ADO.NET
Page Privacy Data Search Output
You can define the GDPR Tables (tables with sensible data)
How to Search DATA: Put the string to search in “Search Field”
The page launch the Codeunit 50001 Privacy Data Management, this codeunit call the store procedure ‘SP_SearchNAVData2018’
Function SearchDataNAV2018(parString : Text[50])
IF ISCLEAR(ADOConnection) THEN
CREATE(ADOConnection,FALSE,TRUE);
ADOConnection.ConnectionString:= ‘Driver={SQL Server};’ + ‘Server=’+’XXX1\NAVDEMO’+’;’+ ‘Database=’+’Demo Database NAV ITA (11-0)’+’;’+ ‘Uid=’+’XX’+’;’+ ‘Pwd=’+XXXX’+’;’;
ADOConnection.Open;
IF ISCLEAR(ADOCommand) THEN
CREATE(ADOCommand,FALSE,TRUE);
VarActiveConnection := ADOConnection;
ADOCommand.ActiveConnection := VarActiveConnection;
ADOCommand.CommandText := ‘SP_SearchNAVData2018’;
ADOCommand.CommandType := 4;
ADOCommand.CommandTimeout := 0;
ADOParameter:=ADOCommand.CreateParameter(‘@SearchStr’, 200,1,50, parString);
ADOCommand.Parameters.Append(ADOParameter);
ADOCommand.Execute;
SLEEP(10000);
COMMIT;
ADOConnection.Close;
CLEAR(ADOConnection);
MESSAGE(‘GDPR Data Extracted’);
SELECTLATESTVERSION;
If you are interested in this solution, I can supply the objects! Stay tuned for Part II (Data Mascherade & Data Encryption)