SELECT
TOP 10 -- only give the first 10 rows. But notice the ORDER by ID DESC means the 1st 10 are the last ten time wise.
-- comments in SQL start with two dashes (--) outside a string and then the rest of the line is a comment
 
-- Now we define which columns of data should be returned for each audit record
DATEADD(hour, -4, AuditServerUTC) AS EDT -- This tells us when it happend (see full featured example for other timezones)
,LaborID -- who made the change
 
-- the JSON column contains the 'new' data that was sent to the server to update. So if you are looking for when/who a value
-- was changed to, this is the 1st place to look.
-- ,LEFT(JSON, 2000) AS NewData -- be careful removing this limit, pictures can be megabytes and cause server freezing if you return too many rows!
-- ,LEFT(OldJSON, 2000) AS PreviousData -- be careful removing this limit, as above
,TargetKey -- the Asset key being changed, it's also in the JSON but this is more convenient for searching
 
FROM dbo.apsAuditLog -- the table the data is in
 
WHERE -- defining WHICH rows to return
module = 'ASSET'  -- just rows in the asset module
 
AND isCommitted = 1000 -- only show successful rows, if you want failed attempts set it to != 1000
 
-- put your search string between the %%s, this lets you filter which rows to return
-- remember to remove the dashes (--) at the end of the next line if you do want to filter based on the JSON content.
--AND JSON like '%%'
 
ORDER BY ID DESC -- This puts newest first
 

SELECT
TOP 10
ID
,AuditServerUTC
-- usually you will want to see datetimes in one or more of:
-- - The USER who entered the data's timezone "when did you..."
-- - The SERVER timezone (which is often the user timezone), 'because of some auto-running process'
-- - And less often, YOUR timezone, unless yours is one of the above
-- delete the datetimes you don't care about or add others
-- DT times for places that still change time twice a year like EDT, Summer, start March 8-14 in Northern Hemisphere
-- ST times for places that still change time twice a year like EDT, and MST, Winter, start November 1-7 in Northern Hemisphere
-- The commas at the beginning of the line make it easier to delete without causing syntax errors
,DATEADD(hour, -4, AuditServerUTC) AS EDT
,DATEADD(hour, -5, AuditServerUTC) AS CDT_EST_Panama
,DATEADD(hour, -6, AuditServerUTC) AS ABT_CST_CostaRica -- I haven't found the official abbreviation for "Alberta time" yet
,DATEADD(hour, -7, AuditServerUTC) AS BCT -- I haven't found the official abbreviation for BC time yet
 
,SessionID
,LaborID
-- ,Module -- Most of the time not needed because you typically filter by Module
-- ,LEFT(JSON, 2000) AS NewData -- be careful removing this limit, pictures can be megabytes and cause server freezing if you return too many rows!
-- ,LEFT(OldJSON, 2000) As OldData -- be careful removing this limit, as above
,TargetKey
,TargetSyncGuid
,BatchID -- useful to see what other tables were updated as part of this 'batch'
FROM dbo.apsAuditLog
-- sample join, to be able to change a PK into a human readable value
-- INNER JOIN Location ON apsAuditLog.TargetKey = Location.LocationPK
WHERE
module = 'ICLite' -- put in the module you want, many are below
-- note there are 3 that are removed (below), so if you want any of
-- them, you'll need to delete or comment them out
 
 
-- 'AccessGroup' 'Account' 'Asset' 'Category' 'Classification' 'Company'
-- 'Contact' 'Contract' 'Craft' 'Customer' 'DataHub' 'Department' 'Document' 'Event' 'FailureAnalysis' 'Holiday'
-- 'ICLite' 'LoginUser' 'Part' 'Preference' 'PreferenceImport'
-- 'Preferences' 'Problem' 'Procedure' 'Project' 'PurchaseOrder' 'QueryEngine' 'RepairCenter'
-- 'Report' 'ServiceRequest' 'SchemaSP' 'SchemaTable' 'Shift' 'Shop' 'Specification'
-- 'SQLTool' 'Stockroom' 'Task' 'Tool' 'Toolroom' 'Training' 'User' 'WorkOrder'
--  'WorkOrderTimer' 'Zone'
 
-- you will almost never want these next 3 as audit records are
-- created for them while running this tool
-- so in the times where you don't know/don't want to specify
-- the module, you will likely still want to remove these
 
AND JSON not like '%system-sql-tool%'  -- eliminate rows caused by SQL tool
AND Module != 'SQLTool'
AND isCommitted = 1000 -- only show successful rows, if you want failed attempts set it to != 1000
-- For performance you almost always want to have it for a date range, the problem is with the order by being ID, this isn't efficient.
AND auditserverutc > '2024-10-07T00:00:00' -- ISO 8601 format for date
--AND auditserverutc < '2024-10-07T00:00:00'
--AND LaborID IN ('x','y','z')
--AND -- other conditions you want, such as Location.LocationID IN ('X', 'Y', 'Z')
--AND JSON like '%%' -- put your search string between the %s
ORDER BY ID DESC -- This puts newest first
 

SELECT
AuditServerUTC
-- comment out the timezones you don't care about
,DATEADD(hour, -4, AuditServerUTC) AS EDT
,DATEADD(hour, -5, AuditServerUTC) AS CDT_EST_Panama
,DATEADD(hour, -6, AuditServerUTC) AS ABT_CST_CostaRica -- I haven't found the official abbreviation for "Alberta time" yet
,DATEADD(hour, -7, AuditServerUTC) AS BCT -- I haven't found the official abbreviation for BC time yet
, AuditClientUTC
, ValueJSON
, oldValueJSON
, LaborID
, operation
, isCommitted
-- ,SessionID
-- ,Module
-- , BatchID
--, TargetKey
--, TargetSyncGuid
--, Application
--, ErrorData
--
FROM apsTransactionAuditLog
WHERE BatchID = 'a716036a-dc97-4946-9490-36599d305d0e'

SELECT
    LaborName
    , FirstName
    , MiddleName
    , LastName
    , Initials
    , MemberID
    , User_Guid
FROM labor
WHERE LaborID='9999'