How to grant access to SSIS

 https://dba.stackexchange.com/questions/77320/how-do-i-grant-the-privilege-of-connecting-to-ssis 

Databricks Audit

 

How to Install Oracle Client 19c

 How to Install Oracle Client 19c

https://www.youtube.com/watch?v=DuPFO6Eh0aM

PC>properties>advanced system settings>Environment Variables>New System Variables>ORACLE_HOME>paste directory

set path add oracle directory\bin

set TNS_ADMIN = ORACLE_HOME\network\admin

How to get when a user was added on an ad group

 $username    = "dexter.soreta"            

$userobj     = (Get-ADUser $username -Properties memberof).memberof

$dc          = (Get-ADDomainController).name   

$membershipreport = 

foreach($group in $userobj){

    Get-ADReplicationAttributeMetadata $group -Server $dc -ShowAllLinkedValues |             

    Where-Object {

        $_.AttributeName -eq 'member' -and             

        $_.AttributeValue -eq $userobj.DistinguishedName

    } |            

    Select-Object FirstOriginatingCreateTime, Object, AttributeValue   

}

$membershipreport |

Sort-Object FirstOriginatingCreateTime -Descending |

export-csv "MembershipReport.csv" -NoTypeInformation

Import-Module ActiveDirectory            

            

$username = "janitor"            

$userobj  = Get-ADUser $username            

            

Get-ADUser $userobj.DistinguishedName -Properties memberOf |            

 Select-Object -ExpandProperty memberOf |            

 ForEach-Object {            

    Get-ADReplicationAttributeMetadata $_ -Server localhost -ShowAllLinkedValues |             

      Where-Object {$_.AttributeName -eq 'member' -and             

      $_.AttributeValue -eq $userobj.DistinguishedName} |            

      Select-Object FirstOriginatingCreateTime, Object, AttributeValue            

    } | Sort-Object FirstOriginatingCreateTime -Descending | Out-GridView

source: https://community.spiceworks.com/t/how-to-check-timestamp-of-user-added-to-certain-ad-group/965173/3

Windows Application not showing up on search

 https://www.lifewire.com/fix-windows-10-search-4176188

Oracle Password Complexity function

 CREATE OR REPLACE function SYS.ora_dex_verify_function

 ( username     varchar2,

   password     varchar2,

   old_password varchar2)

 return boolean IS

   differ  integer;

   lang    varchar2(512);

   message varchar2(512);

   ret     number;

   last_change sys.user$.ptime%type; --DAV4635 minimum_age

   minimum_age number :=1;--DAV4635 minimum_age

begin

   -- Get the cur context lang and use utl_lms for messages- Bug 22730089

   lang := sys_context('userenv','lang');

   lang := substr(lang,1,instr(lang,'_')-1);

   if not ora_complexity_check(password, chars => 8, uppercase => 1,

                           lowercase => 1, digit => 1, special => 1) then

      return(false);

   end if;

   -- Check if the password differs from the previous password by at least

   -- 8 characters

   if old_password is not null then

      differ := ora_string_distance(old_password, password);

      if differ < 4 then

         ret := utl_lms.get_message(28211, 'RDBMS', 'ORA', lang, message);

         raise_application_error(-20000, utl_lms.format_message(message, 'four'));

      end if;

   --DAV4635 minimum_age

  select ptime into last_change from sys.user$ where name=username;

   if sysdate - last_change < minimum_age then

raise_application_error(-20010, 'Password changed too soon');

return(false);

   --DAV4635 minimum_age

    end if;

  end if;

   return(true);

end;

/

How to increase event log retention on windows

Configuring Security Event Log Size and Retention Settings

Security event log size and retention settings can be configured in each computer or configured via a GPO to all target computers.

Local Configuration

1. Open Run (Start -> Run), type eventvwr.msc
2. Right click "Security" log(Event Viewer -> Windows Logs -> Security log) and select "Properties"
3. Configure "Maximum log size" as defined below in the table
   
4. Configure "When maximum event log size is reached" retention method for security log to “Overwrite Events As Needed”
   

GPO Configuration

1. Open GPMC
2. Edit the corresponding GPO (FIM on DomainControllers, FIM on Member Servers)
3. Navigate to Computer Configuration → Policies →  Windows Settings → Security Settings →  Event Log
4. Configure "Maximum security log" size as defined below
5. Configure "Retention method for security log" to “Overwrite Events As Needed”
   

Recommended Security Log Size

Role	OS of the target computer	Log size(MB)
Domain Controller	Windows Server 2003	307
Domain Controller	Windows Server 2008 and above	1048
File Server	Windows Server 2003	307
File Server	Windows Server 2008 and above	4194
Member Server	Windows Server 2003	307
Member Server	Windows Server 2008 and above	1048
Workstation	Window XP	307
Workstation	Windows Vista and above	1048

How to list drop schema in databricks

 %sql

SELECT event_time, user_identity.email, action_name, request_params

FROM system.access.audit

where action_name='deleteSchema'

AND event_date = current_date()

ORDER BY event_time DESC

LIMIT 100;

Azure: How to view size of storage account

 Monitor>insights >storage account

SQL SERVER CANNOT LOGIN

  SELECT sp.[name],sp.type_desc

FROM sys.server_principals sp

INNER JOIN sys.server_permissions PERM ON sp.principal_id = PERM.grantee_principal_id

WHERE PERM.state_desc = 'DENY'

SQL SERVER list Masked columns details

 select  princ.name

,       princ.type_desc

,       perm.permission_name

,       perm.class_desc

,       object_name(perm.major_id)

,       m.name as "COLUMN NAME"

,       schem.name as "SCHEMA"

from    sys.database_principals princ

left join

        sys.database_permissions perm

on      perm.grantee_principal_id = princ.principal_id

left join sys.masked_columns m

on   perm.major_id=m.object_id

left join sys.schemas schem

on perm.major_id=schem.schema_id

where perm.permission_name='UNMASK';

How to Restore a Delta Table in Databricks

Scenario.

We will make some updates and inserts on a table. After that, we restore it to its original form.

SELECT * FROM airline_passenger_csv

WHERE Age > 80

%sql

UPDATE airline_passenger_csv

SET Class="Business"

WHERE Age > 80

%sql

INSERT INTO airline_passenger_csv

VALUES(1234567,'Female','Loyal Customer',88,"Personal Travel","Eco",300,0,0, "satisfied")

%sql

DELETE FROM airline_passenger_csv

WHERE _c0=7547

So far we have updated the Class to business class. Added a user and deleted id 7547

to restore we will check the version from the DESC HISTORY airline_passenger_csv.

Below are a few examples on how to flashback queries DELTA TABLES

%sql

SELECT* FROM airline_passenger_csv

TIMESTAMP AS OF '2024-04-12T01:06:51.000+00:00'

WHERE AGE > 80

%sql

SELECT * FROM airline_passenger_csv@v2

WHERE Age > 80

Now let's say we need to restore to v2 of the table

we type

%sql

RESTORE airline_passenger_csv TO VERSION AS OF 2

How to Network Trace

 Network Trace

Disconnect the dedicated pool.

- Start CMD (Run as Admin)

- Start the process: 

netsh trace start capture=yes packettruncatebytes=512 tracefile=%temp%\%computername%_nettrace.etl maxsize=2048 filemode=circular overwrite=yes report=no

- Reproduce the issue.(Try connecting to dedicated pool through SSMS again)

- Stop the process: netsh trace stop

Oracle Restore Single table to another name

 Note:  It does not work in moving it on another schema

impdp dav4635/Wolver1n323$    tables=PPM.EP_PPM_PROJECT_ALLOCATIONS remap_table=PPM.EP_PPM_PROJECT_ALLOCATIONS:MYTABLE1 dumpfile=16restore.dmp logfile=dx.log

Troubleshoot SQL Server Email

 EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'CVTMSsQLT02 Mailer'

   ,@recipients = 'dexter.velasco@aei.com'

   ,@subject = 'Email from SQL Server'

   ,@body = 'This is my First Email sent from SQL Server :)'

   ,@importance ='HIGH'

GO

SELECT * FROM msdb.dbo.sysmail_unsentitems

SELECT * FROM msdb.dbo.sysmail_sentitems

SELECT * FROM msdb.dbo.sysmail_faileditems

EXEC msdb.dbo.sysmail_help_profile_sp;

select

 err.[description] ,

 fail.*

FROM [msdb].[dbo].[sysmail_event_log] err

 inner join [msdb].dbo.sysmail_faileditems fail

 On err.mailitem_id = fail.mailitem_id;

sysmail_help_profile_sp

ListTablesWithColumnLike

 SELECT      COLUMN_NAME AS 'ColumnName'

            ,TABLE_NAME AS  'TableName'

FROM        INFORMATION_SCHEMA.COLUMNS

WHERE       COLUMN_NAME LIKE '%whatever%'

ORDER BY    TableName

            ,ColumnName;

DOCKER TUTORIAL :Build an image from an existing application

 This is a docker tutorial for beginners and is part of a series.

In this module, we will tackle the following:

1. Download a sample application from github.

2. Create a docker image of the sample application

3. Run the application from your laptop.

You will need a github account and desktop docker installed on your machine.

Steps

1. Go to https://github.com/nigelpoulton/gsd

2. Open either a command prompt or a powershell from your machine.

3. Create a directory D:\dxdockerlabs and clone the source code from github to this directory

4. git clone https://github.com/nigelpoulton/gsd.git

D:\>mkdir dxdockerlabs

D:\>cd dxdockerlabs

D:\dxdockerlabs>dir

 Volume in drive D is DATA

 Volume Serial Number is 94A8-535C

 Directory of D:\dxdockerlabs

08/25/2023  10:32 AM    <DIR>          .

08/25/2023  10:32 AM    <DIR>          ..

               0 File(s)              0 bytes

               2 Dir(s)  53,273,477,120 bytes free

D:\dxdockerlabs>git clone https://github.com/nigelpoulton/gsd.git

Cloning into 'gsd'...

remote: Enumerating objects: 125, done.

remote: Counting objects: 100% (125/125), done.

remote: Compressing objects: 100% (89/89), done.

remote: Total 125 (delta 47), reused 88 (delta 33), pack-reused 0

Receiving objects: 100% (125/125), 310.31 KiB | 2.67 MiB/s, done.

Resolving deltas: 100% (47/47), done.

D:\dxdockerlabs>dir

 Volume in drive D is DATA

 Volume Serial Number is 94A8-535C

 Directory of D:\dxdockerlabs

08/25/2023  10:32 AM    <DIR>          .

08/25/2023  10:32 AM    <DIR>          ..

08/25/2023  10:32 AM    <DIR>          gsd

               0 File(s)              0 bytes

               3 Dir(s)  53,272,608,768 bytes free

D:\dxdockerlabs>

5. The gsd folder you just downloaded contains the source code of the application. This can be any other source code of any application. The only difference is this folder contains a 'dockerfile' that tells what docker has to do. The creation of the dockerfile will not be discussed in this series since this is for beginners

6.  Create the docker image for this application. Go to the directory where the dockerfile is located.  Use

docker image build -t dextervelascoaei/gsd:first-ctr . 

where: 

docker account: dextervelascoaei

directory:gsd

container name: first-ctr 

D:\dxdockerlabs>cd gsd

D:\dxdockerlabs\gsd>dir

 Volume in drive D is DATA

 Volume Serial Number is 94A8-535C

 Directory of D:\dxdockerlabs\gsd

08/25/2023  10:32 AM    <DIR>          .

08/25/2023  10:32 AM    <DIR>          ..

08/25/2023  10:32 AM    <DIR>          compose

08/25/2023  10:32 AM    <DIR>          container

08/25/2023  10:32 AM    <DIR>          first-container

08/25/2023  10:32 AM    <DIR>          multi-container

08/25/2023  10:32 AM             1,077 README.md

08/25/2023  10:32 AM    <DIR>          swarm

08/25/2023  10:32 AM    <DIR>          swarm-stack

               1 File(s)          1,077 bytes

               8 Dir(s)  53,272,608,768 bytes free

D:\dxdockerlabs\gsd>cd first-container

D:\dxdockerlabs\gsd\first-container>dir

 Volume in drive D is DATA

 Volume Serial Number is 94A8-535C

 Directory of D:\dxdockerlabs\gsd\first-container

08/25/2023  10:32 AM    <DIR>          .

08/25/2023  10:32 AM    <DIR>          ..

08/25/2023  10:32 AM               678 app.js

08/25/2023  10:32 AM               675 Dockerfile

08/25/2023  10:32 AM               361 package.json

08/25/2023  10:32 AM                62 readme.md

08/25/2023  10:32 AM    <DIR>          static

08/25/2023  10:32 AM    <DIR>          views

               4 File(s)          1,776 bytes

               4 Dir(s)  53,272,608,768 bytes free

[+] Building 3.4s (11/11) FINISHED

 => [internal] load .dockerignore                                                                               0.0s

 => => transferring context: 2B                                                                                 0.0s

 => [internal] load build definition from Dockerfile                                                            0.0s

 => => transferring dockerfile: 714B                                                                            0.0s

 => [internal] load metadata for docker.io/library/node:current-alpine                                          3.2s

 => [auth] library/node:pull token for registry-1.docker.io                                                     0.0s

 => [1/5] FROM docker.io/library/node:current-alpine@sha256:f62abc08fe1004555c4f28b6793af8345a76230b21d2d24997  0.0s

 => [internal] load build context                                                                               0.0s

 => => transferring context: 106.31kB                                                                           0.0s

 => CACHED [2/5] RUN mkdir -p /usr/src/app                                                                      0.0s

 => CACHED [3/5] COPY . /usr/src/app                                                                            0.0s

 => CACHED [4/5] WORKDIR /usr/src/app                                                                           0.0s

 => CACHED [5/5] RUN npm install                                                                                0.0s

 => exporting to image                                                                                          0.0s

 => => exporting layers                                                                                         0.0s

 => => writing image sha256:602dc8e5704e61c5c69716a600377a3c5e5ad1650b93da1bb7eb5d7151443d46                    0.0s

 => => naming to docker.io/dextervelascoaei/gsd:first-ctr                                                       0.0s

D:\dxdockerlabs\gsd\first-container>

7. Now we can run the container with 

'docker container run -d --name ironman -p 8000:8080 dexteravelascoaei/gsd:first-ctr '

D:\dxdockerlabs\gsd\first-container>docker container run -d --name ironman -p 8000:8080 dexteravelascoaei/gsd:first-ctr

Unable to find image 'dexteravelascoaei/gsd:first-ctr' locally

first-ctr: Pulling from dexteravelascoaei/gsd

8921db27df28: Pull complete

361849a0fe60: Pull complete

4769d5fe84bb: Pull complete

1a33b52da743: Pull complete

5a34b913c9b2: Pull complete

1f6ea1c63c6e: Pull complete

142e7c5f35df: Pull complete

Digest: sha256:e6fa6e4beffb094719b24bfd48f38059f62d6d65f699504955a3f618d1159384

Status: Downloaded newer image for dexteravelascoaei/gsd:first-ctr

352c22010c8402dee23e2cf76af7f2978dce170081c8f1917508b3afaa3f5bad

8. go to localhost:8000

9. Finished

Docker for Beginners

 This tutorial is docker for beginners.

Outline

1. Environment set up
1. Install Docker for Desktop
2. Install Git
2. Build an image from an existing application
3. Push an image to a repository
4. Start a container with the image
5. Stop the container
6. Delete the image
7. test 

List installed application of a server

 PS C:\Users\DEXTER.VELASCO-A> wmic product get name

Display Shared Folders

 C:\Users\dexter.velasco-a>net share