widsnet.com
1Jul/110

Collection history

A report to show who created, modified or deleted a collection, and when.

SELECT SM.Time, SMIS.InsStrValue AS 'Collection',
CASE SM.MessageID
   WHEN 30015 THEN 'Created'
   WHEN 30016 THEN 'Modified'
   WHEN 30017 THEN 'Deleted'
END AS 'Message', SMA.AttributeValue AS 'By User', SM.MachineName AS 'From Computer'
FROM v_StatMsgInsStrings SMIS
LEFT JOIN v_StatMsgAttributes SMA on SMA.RecordID=SMIS.RecordID
LEFT JOIN v_StatusMessage SM on SM.RecordID=SMIS.RecordID
WHERE SMIS.RecordID in (SELECT RecordID FROM v_StatMsgAttributes WHERE AttributeValue=@CollectionID)
      AND SMA.AttributeID=403 AND SMIS.InsStrIndex > 1 AND SM.MessageID in (30015, 30016, 30017)
ORDER BY SM.Time desc

Prompt:

begin
   if (@__filterwildcard = '')
      SELECT DISTINCT CollectionID, Name FROM v_Collection ORDER BY Name
   else
      SELECT DISTINCT CollectionID, Name FROM v_Collection
      WHERE Name like @__filterwildcard
      ORDER BY Name
end
Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

No trackbacks yet.