Tuesday, 31 December 2019

SQL Query Table Name with Rows Counts


SELECT
    TableName = t.NAME,
    TableSchema = s.Name,
    RowCounts = p.rows
FROM
    sys.tables t
INNER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN     
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
WHERE
    t.is_ms_shipped = 0

GROUP BY
    t.NAME, s.Name, p.Rows
ORDER BY
    s.Name, t.Name 

Monday, 16 December 2019

SQL Performance Report Queries

--Query 1: logical reads

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads

--Query 2: logical writes

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_writes DESC -- logical writes

--Query 3: CPU time
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC -- CPU time

--Query 4: Missing Index

SELECT  
dm_mid.database_id AS DatabaseID, 
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact, 
dm_migs.last_user_seek AS Last_User_Seek, 
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName], 
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_' 
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','')  
+ CASE 
WHEN dm_mid.equality_columns IS NOT NULL 
AND dm_mid.inequality_columns IS NOT NULL THEN '_' 
ELSE '' 
END 
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','') 
+ ']' 
+ ' ON ' + dm_mid.statement 
+ ' (' + ISNULL (dm_mid.equality_columns,'') 
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns  
IS NOT NULL THEN ',' ELSE 
'' END 
+ ISNULL (dm_mid.inequality_columns, '') 
+ ')' 
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement 
FROM sys.dm_db_missing_index_groups dm_mig 
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs 
ON dm_migs.group_handle = dm_mig.index_group_handle 
INNER JOIN sys.dm_db_missing_index_details dm_mid 
ON dm_mig.index_handle = dm_mid.index_handle 
WHERE dm_mid.database_ID = DB_ID() 
ORDER BY Avg_Estimated_Impact DESC 
GO
--Query 5: Query Time Execution
SELECT   
    qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds, 
    qs.total_elapsed_time / 1000000.0 AS total_seconds, 
    qs.execution_count, 
    SUBSTRING (qt.text,qs.statement_start_offset/2,  
         (CASE WHEN qs.statement_end_offset = -1  
            THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2  
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query, 
    o.name AS object_name, 
    DB_NAME(qt.dbid) AS database_name 
FROM sys.dm_exec_query_stats qs 
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt 
    LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id 
WHERE qt.dbid = DB_ID() 
ORDER BY average_seconds DESC;

----------Query 6: AvgIo
SELECT 
creation_time
, last_execution_time
, total_logical_reads AS [LogicalReads] , total_logical_writes AS [LogicalWrites] , execution_count
, total_logical_reads+total_logical_writes AS [AggIO] , (total_logical_reads+total_logical_writes)/(execution_count+0.0) AS [AvgIO] , st.TEXT
, DB_NAME(st.dbid) AS database_name
, st.objectid AS OBJECT_ID
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE total_logical_reads+total_logical_writes > 0
AND sql_handle IS NOT NULL
ORDER BY [AggIO] DESC

Friday, 19 July 2019

Bulk SMS Sender ID Codes


Bulk SMS Sender ID Codes



Following table is of Telecom Operator Code.
Provider Code
Airtel A
BSNL B
Datacom Solutions C
Aircel D
Reliance Telecom E
HFCL Infotel H
Idea Cellular I
BPL Mobile/Loop Telecom L
MTNL M
Spice Telecom P
Reliance Communications R
S tel S
Tata Teleservices T
Unitech Group U
Vodafone Group V
Swan Telecom W
Shyam Telecom Y
Following is the table of Regional Code.
Region Code
Andhra Pradesh A
Bihar B
Delhi D
UP-East E
Gujarat G
Haryana H
Himachal Pradesh I
Jammu & Kashmir J
Kolkata K
Kerala L
Mumbai M
North East N
Orissa O
Punjab P
Rajasthan R
Assam S
Tamil Nadu T
West Bengal V
UP-West W
Karnataka X
Madhya Pradesh Y
Maharashtra Z

Tuesday, 18 June 2019

Excel read, Two days different in Python

Program:

import pandas as pd
df = pd.read_excel('CompleteData2.xlsx', sheet_name='Test')
df.DOA = pd.to_datetime(df['DOA']).dt.normalize()
df.DOD = pd.to_datetime(df['DOD']).dt.normalize()
df['Length_of_stay'] =  ((df['DOD'] - df['DOA'])).dt.days
f = df.loc[:,['Length_of_stay']] 
mode = f.mode(axis=0)
print(mode, 'Mode of Length_of_stay')
mean = f.mean(axis=0)
print(mean, 'Mean of Length_of_stay')

output:


Saturday, 1 June 2019

View a List of Databases on an Instance of SQL Server

Query 1:


SELECT name, database_id, create_date  FROM sys.databases ;


If u need only the User-defined databases;



Query 1: select * from sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');


Query 2: SELECT name,filename ,sid FROM dbo.sysdatabases  WHERE dbid > 4

Thursday, 16 May 2019

Top 10 Web Application Security Risks

Injection

Injection flaws, such as SQL injection, LDAP injection, and CRLF injection, occur when an attacker sends untrusted data to an interpreter that is executed as a command without proper authorization.

* Application security testing can easily detect injection flaws. Developers should use parameterized queries when coding to prevent injection flaws.

Broken Authentication and Session Management

Incorrectly configured user and session authentication could allow attackers to compromise passwords, keys, or session tokens, or take control of users’ accounts to assume their identities.

* Multi-factor authentication, such as FIDO or dedicated apps, reduce the risk of compromised accounts.

Sensitive Data Exposure

Applications and APIs that don’t properly protect sensitive data such as financial data, usernames and passwords, or health information, could enable attackers to access such information to commit fraud or steal identities.

* Encryption of data at rest and in transit can help you comply with data protection regulations.

XML External Entity

Poorly configured XML processors evaluate external entity references within XML documents. Attackers can use external entities for attacks including remote code execution, and to disclose internal files and SMB file shares.

Broken Access Control

Improperly configured or missing restrictions on authenticated users allow them to access unauthorized functionality or data, such as accessing other users’ accounts, viewing sensitive documents, and modifying data and access rights.

Security Misconfiguration

This risk refers to improper implementation of controls intended to keep application data safe, such as misconfiguration of security headers, error messages containing sensitive information (information leakage), and not patching or upgrading systems, frameworks, and components.

Cross-Site Scripting

Cross-site scripting (XSS) flaws give attackers the capability to inject client-side scripts into the application, for example, to redirect users to malicious websites.

Insecure deserialization

Insecure deserialization flaws can enable an attacker to execute code in the application remotely, tamper or delete serialized (written to disk) objects, conduct injection attacks, and elevate privileges.

Using Components with Known Vulnerabilities

Developers frequently don’t know which open source and third-party components are in their applications, making it difficult to update components when new vulnerabilities are discovered. Attackers can exploit an insecure component to take over the server or steal sensitive data.

Insufficient Logging and Monitoring

The time to detect a breach is frequently measured in weeks or months. Insufficient logging and ineffective integration with security incident response systems allow attackers to pivot to other systems and maintain persistent threats.

Sunday, 6 January 2019

Getting Available Server Disk Space (Total Size / Free Space) SQL Query

SELECT distinct(volume_mount_point),
total_bytes/1048576 as Size_in_MB,
total_bytes/1048576/1024 as Size_in_GB,
available_bytes/1048576 as Free_in_MB,
available_bytes/1048576/1024 as Free_in_GB,
(select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as FreePercentage
FROM sys.master_files AS f CROSS APPLY
sys.dm_os_volume_stats(f.database_id, f.file_id)
group by volume_mount_point, total_bytes/1048576,
available_bytes/1048576 order by 1


Types of SQL Server Backups

Overview SQL Server offers many options for  creating backups.  In a previous topic, Recovery Models, we discussed what types of backups c...