Experiencing Alerting failure for Log Search Alerts – 05/19 – Resolved

by | May 19, 2021 | Technology

This article is contributed. See the original author and article here.

Final Update: Wednesday, 19 May 2021 15:58 UTC

We’ve confirmed that all systems are back to normal with no customer impact as of 05/19, 15:45 UTC. Our logs show the incident started on 05/19, 14:30 UTC and that during the 1 hour 15 minutes that it took to resolve the issue customers may have experienced issues with missed or delayed alerts in Fairfax.
  • Root Cause: Engineering team has determined that part of the backend workflow for processing Log Search Alerts became unhealthy after it had reached an operational threshold.
  • Incident Timeline: 1 Hour & 15 minutes – 05/19, 14:30 UTC through 05/19, 15:45 UTC

We understand that customers rely on Log Search Alerts as a critical service and apologize for any impact this incident caused.

-Vincent

Cisco Releases Security Updates for Multiple Products  

by | May 19, 2021 | Security, Technology

This article is contributed. See the original author and article here.

Cisco has released security updates to address vulnerabilities in multiple Cisco products. A remote attacker could exploit some of these vulnerabilities to take control of an affected system. For updates addressing lower severity vulnerabilities, see the Cisco Security Advisories page.

CISA encourages users and administrators to review the following Cisco advisories and apply the necessary updates:

Troubleshooting high lock wait time and lock time-out

Troubleshooting high lock wait time and lock time-out

by | May 19, 2021 | Technology

This article is contributed. See the original author and article here.



 


Locks are held in the database to ensure data consistency. If there are errors due to lock time-out or performance issues due to lock waits, the recommendation is to review the transactions that are involved in the locks to check if it is possible to change anything in their logic or isolation level to improve concurrency and avoid the blocks. Blocks can also be caused by transactions taking longer than expected and, in this case, it may require query tuning.


 


We can use T-SQL while the issue is happening to identify the queries involved in the block and the applications that are running them.


 


With the query below, we can see the sessions involved in the block, last query they ran, and which one is the head of the block. It might already give us enough information to identify where the issue is:


 









–Blocking tree


 


SET NOCOUNT ON
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ‘ ‘), CHAR (13), ‘ ‘ ) AS BATCH
INTO #T
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE (‘0’, 4–LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N’    ‘ + REPLICATE (N’|         ‘, LEN (LEVEL)/4 – 1) +
CASE WHEN (LEN(LEVEL)/4 – 1) = 0
THEN ‘HEAD –  ‘
ELSE ‘|——  ‘ END
+ CAST (SPID AS NVARCHAR (10)) + N’ ‘ + BATCH AS BLOCKING_TREE
FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO

 


If the information provided by the last query is not enough and we have to look deeper into the sessions that are part of the block, we can run the query below:


 









–Details about the sessions that are blocking and being blocked:


 


SELECT current_timestamp as [CURRENT_TIMESTAMP]


       , DB_NAME(dtl.resource_database_id) AS database_name


       , req.session_id AS blocked_sessionID


       , ses.program_name blocked_programName


       , ses.host_name blocked_hostname


       , ses.login_name blocked_login


       , CASE ses.transaction_isolation_level


              WHEN 1 THEN ‘ReadUncomitted’


              WHEN 2 THEN ‘ReadCommitted’


              WHEN 3 THEN ‘Repeatable’


              WHEN 4 THEN ‘Serializable’


              WHEN 5 THEN ‘Snapshot’


       END blocked_isolation_level


       , REPLACE(REPLACE(sqltext.TEXT, CHAR(13), ‘ ‘), CHAR(10), ‘ ‘) AS blocked_last_query


       , req.status AS [blocked_status]


       , req.command AS blocked_command


       , req.cpu_time AS blocked_cpuTime


       , req.total_elapsed_time AS blocked_totalElapsedTime


       , blocked_tran.transaction_id blocked_transaction_id


       , osw.blocking_session_id AS blocker_SessionID


       , blocker_ses.program_name blocker_programName


       , blocker_ses.host_name blocker_hostName


       , blocker_ses.login_name blocker_login


       , CASE blocker_ses.transaction_isolation_level


              WHEN 1 THEN ‘ReadUncomitted’


              WHEN 2 THEN ‘ReadCommitted’


              WHEN 3 THEN ‘Repeatable’


              WHEN 4 THEN ‘Serializable’


              WHEN 5 THEN ‘Snapshot’


       END blocker_isolation_level


       , REPLACE(REPLACE(iif(blocker_sqltext.TEXT is NULL,blocker_sqltext2.event_info,blocker_sqltext.TEXT), CHAR(13), ‘ ‘), CHAR(10), ‘ ‘) AS blocker_last_query


       , blocker_req.status AS [blocker_status]


       , blocker_req.command AS blocker_command


       , blocker_req.cpu_time AS blocker_cpuTime


       , blocker_req.total_elapsed_time AS blocker_totalElapsedTime


       , blocker_proc.lastwaittype blocker_last_waittype


       , blocker_proc.last_batch blocker_last_batch


       , blocker_proc.open_tran blocker_open_tran


       , blocker_tran.transaction_id blocker_transaction_id


       , blocker_proc.cmd blocker_command


       , dtl.request_mode AS lockRequestMode


       , dtl.resource_type AS lockResourceType


       , dtl.resource_subtype AS lockResourceSubType


       , osw.wait_type AS taskWaitType


       , osw.resource_description AS taskResourceDescription


       , osw.wait_duration_ms


FROM sys.dm_exec_requests req


INNER JOIN sys.dm_exec_sessions ses on ses.session_id = req.session_id


CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS sqltext


INNER JOIN sys.dm_tran_locks dtl on dtl.request_session_id = req.session_id


INNER JOIN sys.dm_os_waiting_tasks osw on osw.session_id = req.session_id


LEFT JOIN sys.dm_tran_session_transactions blocked_tran on blocked_tran.session_id =req.session_id


INNER JOIN dbo.sysprocesses blocker_proc on osw.blocking_session_id = blocker_proc.spid


LEFT JOIN sys.dm_exec_requests blocker_req on blocker_req.session_id = osw.blocking_session_id


LEFT JOIN sys.dm_exec_sessions blocker_ses on blocker_ses.session_id = osw.blocking_session_id


LEFT JOIN sys.dm_tran_session_transactions blocker_tran on blocker_tran.session_id =osw.blocking_session_id


OUTER APPLY sys.dm_exec_sql_text(blocker_req.sql_handle) AS blocker_sqltext


OUTER APPLY sys.dm_exec_input_buffer(osw.blocking_session_id,0) as blocker_sqltext2;


 

 


This query will provide information about the last queries on both sides, the isolation level of each and if they are inside a transaction.


 


Locks are held for the duration of the transaction (between commits/rollbacks), so the lock might be being held due to a previous query of the current transaction. Said that, there may be cases in which the block does not make sense when looking at both sessions last query, but it does when we look at the whole transaction. However, we cannot see the previous queries the transaction ran using T-SQL.


 


In the cases where the blocks are happening due to a lock acquired for a previous statement, we can use the query below to see which locks are being held by the sessions involved in the block. It won’t give us the previous queries in the transactions, but it might help us understand why the block is happening and assist in the investigation.


 









 


–Details about the locks that are being held by the sessions that are blocking and being blocked:


 


select DB_NAME(locks.resource_database_id) AS database_name


 , locks.request_session_id


 , locks.resource_type, locks.resource_subtype


 , locks.resource_description


 , locks.resource_associated_entity_id


 , locks.resource_lock_partition


 , locks.request_mode


 , locks.request_type


 , locks.request_status


 , locks.request_reference_count


 , locks.request_lifetime


 , locks.request_exec_context_id


 , locks.request_request_id


 , locks.request_owner_type


FROM sys.dm_exec_requests req


INNER JOIN sys.dm_os_waiting_tasks osw on osw.session_id = req.session_id


INNER JOIN sys.dm_tran_locks locks on osw.blocking_session_id = locks.request_session_id or (osw.session_id = locks.request_session_id and osw.blocking_session_id is not null)


order by locks.request_session_id;


 

 


For Azure SQL Database, if the database has auditing enabled in the Portal, we can find all the queries that were run by the transaction in the audit logs by filtering by session ID and transaction ID provided in the second query.


 


For both Azure SQL Database and Azure SQL Managed Instance, we can create an XEvents session to see which queries are being run by the applications and filter by the transaction ID and session returned by the second query.


 


Both XEvents and Auditing will only assist in the investigation of locks that happened after they were enabled/started.


 


Examples:


 


1. Using Extended Events:


 


For this example, I created an XEvents session in a Managed Instance for the events sql_statement_starting and sql_statement_completed filtering by the database name and adding the global fields sessionID and transaction_id. You may also want to include other global fields to help you in the investigation, such as client_app_name, client_hostname and username.


 


Note: For more information about XEvents, please refer to the extended events documentation.


 


After the Extended Events is active, we have a block in our database with the following blocking tree:


 


Thamires_Lemes_1-1621439559912.png


 


As you can see, the head (session 98) last statement was an alter in the table Person and It is blocking the session 97 (insert in the table Person), which we can understand just looking at the queries.


 


However, the session 97 last statement was an insert into the table Person and It is blocking the session 115, that is trying to insert into the table Person2. If it is a different table, how can they this block happen?


 


We can understand when we look at the information collected by the XEvents:


 


Thamires_Lemes_2-1621439585985.png


 


We see the session 97 ran 2 queries in this same transaction (same transaction_id) and the first one was an alter in the table Person2.


 


It may be difficult to find the information right away if there is high activity on the database, so you can filter by the session_id and transaction_id, provided by the second query in this article to find all the statements that were executed within the same transaction.


 


2.  Using Azure SQL DB Auditing


 


If you have auditing enabled for your Azure SQL DB server/database, you can use the audit log to see the statements that were run by the transaction(s).


 


If we have the same scenario as the previous example, but now in an Azure SQL DB with auditing enabled:


 


Thamires_Lemes_3-1621439607209.png


 


As you can see above, the head (session 102) last statement was an alter in the table Person and It is locking the session 104 (insert in the table Person), which we can understand just looking at the queries.


 


However, the session 104 which last statement was the insert into Person is blocking the session 100 that is trying to insert into the table Person2. If it is a different table, how can they this block happen?


 


If we open the audit log and add the columns session_id and transaction_id, we can see the session 104 ran in this same transaction (same transaction_id) an alter in the table Person2:


 


Thamires_Lemes_4-1621439622694.png


 


We cannot see in the audit log the statements that are blocked, because it only shows the statements that have been completed. We can use the session_id and transaction_id returned by the second query of this article to filter the audit information and see the previous queries ran by the transaction(s).


 


References:


https://blog.sqlauthority.com/2015/07/07/sql-server-identifying-blocking-chain-using-sql-scripts/


Hang up on auto warranty robocalls

Hang up on auto warranty robocalls

by | May 19, 2021 | Security

This article was originally posted by the FTC. See the original article here.

Have you gotten a recorded phone message from “Susie” with the “Vehicle Service Department” calling about your vehicle warranty? That’s, like, so retro. But fanny packs, scrunchies, and tie dye are back — and so are vehicle warranty robocalls.

It starts when you pick up a call and hear a recorded message saying something about your vehicle’s manufacturer’s warranty. It might say that they’ve sent you several notices in the mail. They say you haven’t extended your warranty past the factory cut off, and they’re going to close your file soon if you don’t respond. And, you should press a number if you’re interested in renewing your warranty, or press a different number if you want to be removed from the list.

Slam on the brakes. This is an illegal robocall and likely a scam. The companies behind this type of robocall are not with your car dealer or manufacturer, and the “extended warranty” they’re trying to sell you is actually a service contract that often sells for hundreds or thousands of dollars. If you buy it, you may find that the contract doesn’t actually cover any problems you have with your car because of the restrictions in the fine print.

If you get one of these calls:

  • Hang up. Don’t engage with a company that reaches you with an illegal call.
  • Consider using an app or device to block unwanted calls. Learn more about how to do that.
  • Report the robocall at DoNotCall.gov.

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Conditional Access GPS-based named locations now in public preview

Conditional Access GPS-based named locations now in public preview

by | May 19, 2021 | Technology

This article is contributed. See the original author and article here.

Today, I am excited to share how you can improve your Conditional Access policies and ensure compliance with data regulations thanks to the public preview of GPS-based named locations. This feature helps admins strengthen their security and compliance posture and allows them to restrict access to sensitive apps based on the GPS location of their users.


 


I have asked Olena Huang, a PM on the Identity team, to tell you more. Let us know what you think!


 


Alex Weinert


 


————————————-


 


Hello,


 


With the public preview of GPS-based named locations, admins can refine their Conditional Access policies by determining a user’s location with even more precision.  GPS-based named locations allow you to restrict access to certain resources to the boundaries of a specific country. Due to VPNs and other factors, determining a user’s location from their IP address is not always accurate or reliable. Leveraging GPS signals enables admins to determine a user’s location with higher confidence. This is especially helpful if you have strict compliance regulations that limit where specific data can be accessed.


 


When the feature is enabled, users will be prompted to share their GPS location via the Microsoft Authenticator app during sign-in.


 


 


Create a policy to allow or restrict access based off a user’s GPS location


There are two simple steps:



  1. Create a GPS-based named location.

  2. Create or configure Conditional Access with this named location.


You’ll first need to create a countries named location and select the countries where you want the policy to apply. Configure the named location to determine the location by GPS coordinates instead of by IP address.


 


Named Locations.png


 


 


Next, create a Conditional Access policy to restrict access to selected applications for sign-ins within the boundaries of the named location.


 


New.png


 


 


For more information, check out our admin documentation  or our Graph API documentation.


 


 


Test out the location-sharing experience


First, make sure you have the Microsoft Authenticator app installed and set up with your test account.


 


Next, try to access the files or data restricted by the Conditional Access policy.  You’ll be prompted to share your geolocation from the Authenticator app.


 


Contoso.png


 


The first time you encounter this prompt, you will need to grant location permissions to the Authenticator app.


 


 


iOS


IOS.png


 


Android


Android.png


 


For the next 24 hours, your location will be shared silently once per hour from that device, so you won’t keep getting notifications.


 


After 24 hours, you will be re-prompted when trying to access the same resource. However, you will not need to grant permissions again (unless you’ve disabled them).


 


Authenticator.png


 


 


If you have questions, check out our FAQ page.


 


We’d love to hear from you! Feel free to leave comments below or reach out to us on Twitter.


 


 


 


Learn more about Microsoft identity: