T-SQL Query to Get Computer Name that has not Made a Query to Database in 60 Days

Are you tired of dealing with inactive computers that are no longer querying your database? Do you want to identify and track computer names that have not made a query to your database in the last 60 days? Look no further! In this article, we’ll show you how to write a T-SQL query that will help you achieve just that.

Why is it Important to Track Inactive Computers?

Inactive computers can pose a significant security risk to your database and overall network. They can be vulnerable to attacks, and if compromised, can provide an entry point for malicious actors. Moreover, inactive computers can also consume valuable resources, such as licenses and system resources, that could be better allocated to active users.

By tracking inactive computers, you can:

• Improve database security by identifying and revoking access to inactive devices
• Optimize system resources by reallocating licenses and resources to active users
• Enhance compliance and auditing by tracking device activity and usage

Understanding the T-SQL Query

The T-SQL query we’ll be using is based on the `sys.dm_exec_sessions` and `sys.dm_exec_connections` system views. These views provide information about active and inactive sessions, as well as the computer names associated with these sessions.

The query will use the following criteria to identify computer names that have not made a query to the database in the last 60 days:

• The `sys.dm_exec_sessions` view will be used to get the most recent session ID for each computer name
• The `sys.dm_exec_connections` view will be used to get the most recent connection time for each session ID
• The `DATEDIFF` function will be used to calculate the difference between the current date and the most recent connection time for each computer name
• The `WHERE` clause will filter out computer names that have made a query to the database in the last 60 days

The T-SQL Query

WITH RecentSessions AS (
  SELECT 
    s.session_id,
    c.client_net_address,
    c.host_name,
    MAX(c.connect_time) AS LastConnectionTime
  FROM 
    sys.dm_exec_sessions s
  INNER JOIN 
    sys.dm_exec_connections c ON s.session_id = c.session_id
  GROUP BY 
    s.session_id,
    c.client_net_address,
    c.host_name
)
SELECT 
  host_name
FROM 
  RecentSessions
WHERE 
  DATEDIFF(DAY, LastConnectionTime, GETDATE()) > 60;

Breaking Down the Query

Let’s break down the query step by step to understand how it works:

CTE (Common Table Expression)

The query uses a CTE to simplify the query and improve performance. The CTE is named `RecentSessions` and it selects the following columns:

• `session_id`: The unique ID for each session
• `client_net_address`: The client’s network address
• `host_name`: The computer name
• `LastConnectionTime`: The most recent connection time for each session ID

The CTE uses the `MAX` aggregate function to get the most recent connection time for each session ID, grouping by `session_id`, `client_net_address`, and `host_name`.

Main Query

The main query selects the `host_name` column from the `RecentSessions` CTE. The `WHERE` clause filters out computer names that have made a query to the database in the last 60 days using the `DATEDIFF` function.

The `DATEDIFF` function calculates the difference between the current date (`GETDATE()`) and the `LastConnectionTime` for each computer name. The `WHERE` clause filters out computer names where the difference is less than or equal to 60 days.

Executing the Query

To execute the query, simply copy and paste it into your SQL Server Management Studio (SSMS) or your favorite SQL client tool.

Makes sure to:

• Replace the `sys.dm_exec_sessions` and `sys.dm_exec_connections` views with the correct database and schema names for your environment
• Adjust the `DATEDIFF` function to calculate the difference in days, hours, or minutes based on your specific requirements

Query Results

The query will return a list of computer names that have not made a query to the database in the last 60 days.

Here’s an example output:

host_name
PC-123456
LAPTOP-ABC
SERVER-XYZ

Conclusion

In this article, we’ve shown you how to write a T-SQL query to get computer names that have not made a query to the database in the last 60 days. By using the `sys.dm_exec_sessions` and `sys.dm_exec_connections` system views, you can identify and track inactive computers that may pose a security risk to your database.

Remember to adjust the query to fit your specific requirements and environment. With this query, you can improve database security, optimize system resources, and enhance compliance and auditing.

Happy querying!

1. Microsoft. (n.d.). sys.dm_exec_sessions (Transact-SQL). Retrieved from https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sessions-transact-sql?view=sql-server-ver15
2. Microsoft. (n.d.). sys.dm_exec_connections (Transact-SQL). Retrieved from https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-connections-transact-sql?view=sql-server-ver15

Frequently Asked Question

If you’re struggling to keep track of which computers have gone dark on your database, we’ve got you covered!