All Apps and Add-ons

"Inner Join" works in query, not DB Connect

timrich66
Communicator

Hi Knowledgeable People 😊

I have been provided a query which works successfully when run by the DBA on the server itself.  However, when run using DB Connect, 'no results' are found.  Please can someone advise why this may be?  Thanks

This is the query - 

SELECT ar.replica_server_name,
adc.database_name,
ag.name AS ag_name,
drs.is_local,
drs.is_primary_replica,
drs.synchronization_state_desc,
drs.is_commit_participant,
drs.synchronization_health_desc,
drs.recovery_lsn,
drs.truncation_lsn,
drs.last_sent_lsn,
drs.last_sent_time,
drs.last_received_lsn,
drs.last_received_time,
drs.last_hardened_lsn,
drs.last_hardened_time,
drs.last_redone_lsn,
drs.last_redone_time,
drs.log_send_queue_size,
drs.log_send_rate,
drs.redo_queue_size,
drs.redo_rate,
drs.filestream_send_rate,
drs.end_of_log_lsn,
drs.last_commit_lsn,
drs.last_commit_time
FROM master.sys.dm_hadr_database_replica_states AS drs
INNER JOIN master.sys.availability_databases_cluster AS adc
ON drs.group_id = adc.group_id
AND drs.group_database_id = adc.group_database_id
INNER JOIN master.sys.availability_groups AS ag
ON ag.group_id = drs.group_id
INNER JOIN master.sys.availability_replicas AS ar
ON drs.group_id = ar.group_id
AND drs.replica_id = ar.replica_id
ORDER BY ag.name, ar.replica_server_name, adc.database_name;

Labels (1)
Tags (1)
0 Karma

PickleRick
Ultra Champion

The most obvious hint that comes to mind is that you might be running the query from a user that doesn't have sufficient privileges on the DB server for necessary objects.

0 Karma

timrich66
Communicator

Thanks @PickleRick I have found that querying the master.sys.availability_groups used in the second INNER JOIN returns no results.  I have removed all the 'ag' references and the query runs ok.  

I've passed it back to the DBA to investigate.

0 Karma
Get Updates on the Splunk Community!

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...