Audit Scripts: General Audit

Audit Scripts: General Audit

After completing a backup there are a number of things that are good to review prior to restoring into the destination system. 

The script below reviews several of the data points that should be looked at prior to restoring. 
This script is not exhaustive, but it is a good place to start when looking for anomalies. 
  1.   SELECT * FROM 
  2. (

  4. --Look for custom fields that aren't being used.
  5. SELECT
  6. 0 sortOrder,
  7. '__M_CustomField_Definitions' tableName,
  8. cfd.Final_Status,
  9. IIF(COALESCE(cfv.Final_CustomFieldDefinition_Id, '')='', 'Custom Field Not Used', 'Custom Field Used') auditDescription,
  10. COUNT(*) numRows,
  11.   (SELECT COUNT(*) FROM __M_CustomField_Definitions) totalRows
  12.   FROM
  13. __M_CustomField_Definitions cfd
  14. LEFT OUTER JOIN
  15. (SELECT Final_CustomFieldDefinition_Id FROM __M_CustomField_Values GROUP BY Final_CustomFieldDefinition_Id) cfv
  16. ON cfd.Id = cfv.Final_CustomFieldDefinition_Id
  17. GROUP BY
  18. cfd.Final_Status,
  19. IIF(COALESCE(cfv.Final_CustomFieldDefinition_Id, '')='', 'Custom Field Not Used', 'Custom Field Used')

  21. UNION ALL

  23. -- Look for used/unused practice areas.

  25. SELECT
  26.    1 AS sortOrder,
  27.     '__M_Matters_PracticeAreas' AS tableName,
  28.     pa.Final_Status,
  29.     IIF(COALESCE( m.Final_PracticeArea_Id, '') = '', 'Practice Area Not Used', 'Practice Area Used') auditDescription,
  30.     SUM(CASE WHEN m.Final_PracticeArea_Id IS NOT NULL THEN 1 ELSE 0 END) AS numRows,
  31.     (SELECT COUNT(*) FROM __M_Matters_PracticeAreas) AS totalRows
  32. FROM
  33.     __M_Matters_PracticeAreas pa
  34. LEFT JOIN
  35.     (SELECT Final_PracticeArea_Id FROM __M_Matters GROUP BY Final_PracticeArea_Id) m
  36. ON 
  37.     pa.Id = m.Final_PracticeArea_Id
  38. GROUP BY
  39.     pa.Final_Status,
  40. IIF(COALESCE( m.Final_PracticeArea_Id, '') = '', 'Practice Area Not Used', 'Practice Area Used')

  42. UNION ALL

  44. -- Look for contacts with no names
  45. SELECT
  46. 2 sortOrder,
  47. '__M_Contacts' tableName,
  48. Final_Status,
  49. IIF( Final_FirstName = '' AND Final_LastName = '' AND Final_FullName = '', 'No Name', 'Has Name') auditDescription,
  50. COUNT(*) numRows,
  51. (SELECT COUNT(*) FROM __M_Contacts) totalRows
  52. FROM
  53. __M_Contacts
  54. GROUP BY
  55. Final_Status,
  56. IIF( Final_FirstName = '' AND Final_LastName = '' AND Final_FullName = '', 'No Name', 'Has Name')

  58. UNION ALL

  60. -- Look for Matters missing descriptors
  61. SELECT
  62. 2.5 sortOrder,
  63. '__M_Matters' tableName,
  64. Final_Status,
  65. IIF( Final_Subject = '' AND Final_Description = '' AND Final_ReferenceCode = '', 'No Descriptors', 'Has Descriptors') auditDescription,
  66. COUNT(*)  numRows,
  67. (SELECT COUNT(*) FROM __M_Matters) totalRows
  68. FROM
  69. __M_Matters
  70. GROUP BY
  71. Final_Status,
  72. IIF( Final_Subject = '' AND Final_Description = '' AND Final_ReferenceCode = '', 'No Descriptors', 'Has Descriptors') 

  74. UNION ALL

  76. SELECT
  77. 2.61 sortOrder,
  78. '__M_Matters' tableName,
  79. 'All Statuses' Final_Status,
  80. CONCAT('Max Description Len - ', MAX(LEN(Final_Description))) auditDescription,
  81. COUNT(*)  numRows,
  82. (SELECT COUNT(*) FROM __M_Matters) totalRows
  83. FROM
  84. __M_Matters

  86. UNION ALL

  88. SELECT
  89. 2.6 sortOrder,
  90. '__M_Matters' tableName,
  91. 'All Statuses' Final_Status,
  92. CONCAT('Min Description Len - ', MIN(LEN(Final_Description))) auditDescription,
  93. COUNT(*)  numRows,
  94. (SELECT COUNT(*) FROM __M_Matters) totalRows
  95. FROM
  96. __M_Matters

  98. UNION ALL

  100. SELECT
  101. 2.62 sortOrder,
  102. '__M_Matters' tableName,
  103. 'All Statuses' Final_Status,
  104. CONCAT('Min ReferenceCode Len - ', MIN(LEN(Final_ReferenceCode))) auditDescription,
  105. COUNT(*)  numRows,
  106. (SELECT COUNT(*) FROM __M_Matters) totalRows
  107. FROM
  108. __M_Matters

  110. UNION ALL

  112. SELECT
  113. 2.63 sortOrder,
  114. '__M_Matters' tableName,
  115. 'All Statuses' Final_Status,
  116. CONCAT('Max ReferenceCode Len - ', MAX(LEN(Final_ReferenceCode))) auditDescription,
  117. COUNT(*)  numRows,
  118. (SELECT COUNT(*) FROM __M_Matters) totalRows
  119. FROM
  120. __M_Matters

  122. UNION ALL

  124. SELECT
  125. 2.64 sortOrder,
  126. '__M_Matters' tableName,
  127. 'All Statuses' Final_Status,
  128. CONCAT('Min Subject Len - ', MIN(LEN(Final_Subject))) auditDescription,
  129. COUNT(*)  numRows,
  130. (SELECT COUNT(*) FROM __M_Matters) totalRows
  131. FROM
  132. __M_Matters

  134. UNION ALL

  136. SELECT
  137. 2.65 sortOrder,
  138. '__M_Matters' tableName,
  139. 'All Statuses' Final_Status,
  140. CONCAT('Max Subject Len - ', Max(LEN(Final_Subject))) auditDescription,
  141. COUNT(*)  numRows,
  142. (SELECT COUNT(*) FROM __M_Matters) totalRows
  143. FROM
  144. __M_Matters

  146. UNION ALL

  148. -- Look for activities billed/unbilled status
  149. SELECT
  150. 3 sortOrder,
  151. '__M_Financial_Activities_Expenses' tableName,
  152. Final_Status,
  153. IIF( Final_ArInvoice_Id = '', 'Unbilled', 'Billed' ) auditDescription,
  154. COUNT(*) numRows,
  155. (SELECT COUNT(*) FROM __M_Financial_Activities_Expenses) totalRows
  156. FROM
  157. __M_Financial_Activities_Expenses
  158. GROUP BY
  159. Final_Status,
  160. IIF( Final_ArInvoice_Id = '', 'Unbilled', 'Billed' )

  162. UNION ALL

  164. SELECT
  165. 3.1 sortOrder,
  166. '__M_Financial_Activities_Fees' tableName,
  167. Final_Status,
  168. IIF( Final_ArInvoice_Id = '', 'Unbilled', 'Billed' ) auditDescription,
  169. COUNT(*) numRows,
  170. (SELECT COUNT(*) FROM __M_Financial_Activities_Fees) totalRows
  171. FROM
  172. __M_Financial_Activities_Fees
  173. GROUP BY
  174. Final_Status,
  175. IIF( Final_ArInvoice_Id = '', 'Unbilled', 'Billed' )

  177. UNION ALL

  179. SELECT
  180. 3.2 sortOrder,
  181. '__M_Financial_Activities_Expenses' tableName,
  182. Final_Status,
  183. IIF( Final_ArInvoice_Id = '', 'Unbilled', 'Billed' ) auditDescription,
  184. COUNT(*) numRows,
  185. (SELECT COUNT(*) FROM __M_Financial_Activities_Expenses) totalRows
  186. FROM
  187. __M_Financial_Activities_Expenses
  188. GROUP BY
  189. Final_Status,
  190. IIF( Final_ArInvoice_Id = '', 'Unbilled', 'Billed' )

  192. UNION ALL

  194. --Look at Invoice Outstanding/not Outstanding balance counts.
  195. SELECT
  196. 3.3 sortOrder,
  197. '__M_Financial_AR_Invoices' tableName,
  198. Final_Status,
  199. IIF( Final_Outstanding_Override > 0, 'Outstanding Balance', 'No Outstanding Balance') auditDescription,
  200. COUNT(*) numRows,
  201. (SELECT COUNT(*) FROM __M_Financial_AR_Invoices) totalRows
  202. FROM
  203. __M_Financial_AR_Invoices
  204. GROUP BY
  205. Final_Status,
  206. IIF( Final_Outstanding_Override > 0, 'Outstanding Balance', 'No Outstanding Balance')

  208. UNION ALL

  210. -- Look for matters missing a contact
  211. SELECT
  212. 4 sortOrder,
  213. '__M_Matters' tableName,
  214. m.Final_Status,
  215. IIF(COALESCE(c.Id, '')='', 'Contact Missing', 'Contact Eists') auditDescription,
  216. COUNT(*) numRows,
  217. (SELECT COUNT(*) FROM __M_Matters) totalRows
  218. FROM
  219. __M_Matters m
  220. LEFT OUTER JOIN
  221. __M_Contacts c
  222. ON m.Final_ClientContact_Id = c.Id
  223. GROUP BY
  224. m.Final_Status,
  225. IIF(COALESCE(c.Id, '')='', 'Contact Missing', 'Contact Eists')

  227. UNION ALL

  229. -- Look for documents linked to matters with a missing matter reference.
  230. SELECT
  231. 5 sortOrder,
  232. '__M_Documents_Digital_Files'  tableName,
  233. df.Final_Status,
  234. IIF(COALESCE(m.Id, '')='', 'Matter Missing', 'Matter Exists') auditDescription,
  235. COUNT(*) numRows,
  236. (SELECT COUNT(*) FROM __M_Documents_Digital_Files) totalRows
  237. FROM
  238. __M_Documents_Digital_Files df
  239. LEFT OUTER JOIN
  240. __M_Matters m
  241. ON df.Final_Parent_Id= m.Id
  242. WHERE 1=1
  243. AND df.Final_Parent_Type = '__M_Matters'
  244. GROUP BY
  245. df.Final_Status,
  246. IIF(COALESCE(m.Id, '')='', 'Matter Missing', 'Matter Exists')

  248. UNION ALL

  250. -- Look for documents linked to Contacts with a missing Contact reference.
  251. SELECT
  252. 5.1 sortOrder,
  253. '__M_Documents_Digital_Files' tableName,
  254. df.Final_Status,
  255. IIF(COALESCE(c.Id, '')='', 'Orphan Contacts', 'Contact Exists') auditDescription,
  256. COUNT(*) numRows,
  257. (SELECT COUNT(*) FROM __M_Documents_Digital_Files) totalRows
  258. FROM
  259. __M_Documents_Digital_Files df
  260. LEFT OUTER JOIN
  261. __M_Contacts c
  262. ON df.Final_Parent_Id= c.Id
  263. WHERE 1=1
  264. AND df.Final_Parent_Type = '__M_Contacts'
  265. GROUP BY
  266. df.Final_Status,
  267. IIF(COALESCE(c.Id, '')='', 'Orphan Contacts', 'Contact Exists')

  269. UNION ALL

  271. -- Look for Documents with/without content paths
  272. SELECT
  273. 5.2 sortOrder,
  274. '__M_Documents_Digital_Files' tableName,
  275. df.Final_Status,
  276. IIF(COALESCE(dfv.hasContentPath, 0)=0, 'No Content Path', 'Has Content Path') auditDescription,
  277. COUNT(*) numRows,
  278. (SELECT COUNT(*) FROM __M_Documents_Digital_Files) totalRows
  279. FROM
  280. __M_Documents_Digital_Files df
  281. LEFT OUTER JOIN
  282. (SELECT Final_DocumentFile_Id, SUM(IIF(Final_Content_FromPath='',0,1)) hasContentPath FROM __M_Documents_Digital_Files_Versions GROUP BY
  283. Final_DocumentFile_Id) dfv
  284. ON df.Id = dfv.Final_DocumentFile_Id
  285. GROUP BY
  286. df.Final_Status,
  287. IIF(COALESCE(dfv.hasContentPath, 0)=0, 'No Content Path', 'Has Content Path')
  288. ) T
  289. ORDER BY
  290. sortOrder, tableName, auditDescription