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

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

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:

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

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.

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

Find Largest Element of an Array using Function in Python

# Find Largest Element of an Array using Function

# python function to find maximum
# in arr[] of size n
def largest(arr,n):

# Initialize maximum element
max = arr[0]

# Traverse array elements from second
# and compare every element with
# current max
for i in range(1, n):
if arr[i] > max:
max = arr[i]
return max
# Drive Code
arr =[i for i in  range(100)]
   
n = input(" Enter total number of elements(1 to 100):")
n=int(n);

for i in range(n):
    arr[i]=  input(" Enter Number for index {0} ".format(int(i)));


Ans = largest(arr,n)
print ("Largest in given array is",Ans)