I use the audit log query to gather data for quarterly reports. The query results display nicely in SP7 but when I export the results, much of it is unreadable and I spend a lot of time doing 40-50 find/replace operations to convert table numbers to the table names so that I can see what’s been done over the last 3 months. It would be more useful to export the table names. Likewise with the actions and field names (user-created caption vs, e.g., “text1”).
In the short-term, if Specify could provide a list of all tables and their corresponding numbers, It would make the process go faster since I could write an excel formula to update my query export to the readable table names.
Hi @Plarson,
I have added a feature request for this on our GitHub issue tracker:
If you would like a list of all tables and Table IDs, you can download the database schema for your database as a TSV file.
This is available in the User Tools menu in your Specify 7 instance.
Example: https://sp7demofish.specifycloud.org/specify/data-model/
Username:
sp7demofish
Password:sp7demofish
1 Like
Thanks this is a big help - and just a note: I downloaded it and only the header line was tab-separated. the rest is comma-separated.
Sure thing.
For the action column, here are the keys:
0: Create
1: Update
2: Delete
3: Tree Merge
4: Tree Move
5: Tree Synonymize
6: Tree Desynonymize
And table IDs:
Sorted by table name
{
"Accession": 7,
"AccessionAgent": 12,
"AccessionAttachment": 108,
"AccessionAuthorization": 13,
"AccessionCitation": 159,
"Address": 8,
"AddressOfRecord": 125,
"Agent": 5,
"AgentAttachment": 109,
"AgentGeography": 78,
"AgentIdentifier": 168,
"AgentSpecialty": 86,
"AgentVariant": 107,
"Appraisal": 67,
"Attachment": 41,
"AttachmentImageAttribute": 139,
"AttachmentMetadata": 42,
"AttachmentTag": 130,
"AttributeDef": 16,
"Author": 17,
"AutoNumberingScheme": 97,
"Borrow": 18,
"BorrowAgent": 19,
"BorrowAttachment": 145,
"BorrowMaterial": 20,
"BorrowReturnMaterial": 21,
"CollectingEvent": 10,
"CollectingEventAttachment": 110,
"CollectingEventAttr": 25,
"CollectingEventAttribute": 92,
"CollectingEventAuthorization": 152,
"CollectingTrip": 87,
"CollectingTripAttachment": 156,
"CollectingTripAttribute": 157,
"CollectingTripAuthorization": 158,
"Collection": 23,
"CollectionObject": 1,
"CollectionObjectAttachment": 111,
"CollectionObjectAttr": 28,
"CollectionObjectAttribute": 93,
"CollectionObjectCitation": 29,
"CollectionObjectProperty": 153,
"CollectionRelType": 98,
"CollectionRelationship": 99,
"Collector": 30,
"CommonNameTx": 106,
"CommonNameTxCitation": 134,
"ConservDescription": 103,
"ConservDescriptionAttachment": 112,
"ConservEvent": 73,
"ConservEventAttachment": 113,
"Container": 31,
"DNAPrimer": 150,
"DNASequence": 121,
"DNASequenceAttachment": 147,
"DNASequencingRun": 88,
"DNASequencingRunAttachment": 135,
"DNASequencingRunCitation": 105,
"DataType": 33,
"Deaccession": 163,
"DeaccessionAgent": 164,
"DeaccessionAttachment": 165,
"Determination": 9,
"DeterminationCitation": 38,
"Determiner": 167,
"Discipline": 26,
"Disposal": 34,
"DisposalAgent": 35,
"DisposalAttachment": 166,
"DisposalPreparation": 36,
"Division": 96,
"ExchangeIn": 39,
"ExchangeInAttachment": 169,
"ExchangeInPrep": 140,
"ExchangeOut": 40,
"ExchangeOutAttachment": 170,
"ExchangeOutPrep": 141,
"Exsiccata": 89,
"ExsiccataItem": 104,
"Extractor": 160,
"FieldNotebook": 83,
"FieldNotebookAttachment": 127,
"FieldNotebookPage": 85,
"FieldNotebookPageAttachment": 129,
"FieldNotebookPageSet": 84,
"FieldNotebookPageSetAttachment": 128,
"FundingAgent": 146,
"GeoCoordDetail": 123,
"Geography": 3,
"GeographyTreeDef": 44,
"GeographyTreeDefItem": 45,
"GeologicTimePeriod": 46,
"GeologicTimePeriodTreeDef": 47,
"GeologicTimePeriodTreeDefItem": 48,
"Gift": 131,
"GiftAgent": 133,
"GiftAttachment": 144,
"GiftPreparation": 132,
"GroupPerson": 49,
"InfoRequest": 50,
"Institution": 94,
"InstitutionNetwork": 142,
"Journal": 51,
"LatLonPolygon": 136,
"LatLonPolygonPnt": 137,
"LithoStrat": 100,
"LithoStratTreeDef": 101,
"LithoStratTreeDefItem": 102,
"Loan": 52,
"LoanAgent": 53,
"LoanAttachment": 114,
"LoanPreparation": 54,
"LoanReturnPreparation": 55,
"Locality": 2,
"LocalityAttachment": 115,
"LocalityCitation": 57,
"LocalityDetail": 124,
"LocalityNameAlias": 120,
"MaterialSample": 151,
"MorphBankView": 138,
"OtherIdentifier": 61,
"PaleoContext": 32,
"PcrPerson": 161,
"Permit": 6,
"PermitAttachment": 116,
"PickList": 500,
"PickListItem": 501,
"PrepType": 65,
"Preparation": 63,
"PreparationAttachment": 117,
"PreparationAttr": 64,
"PreparationAttribute": 91,
"PreparationProperty": 154,
"Project": 66,
"RecordSet": 68,
"RecordSetItem": 502,
"ReferenceWork": 69,
"ReferenceWorkAttachment": 143,
"RepositoryAgreement": 70,
"RepositoryAgreementAttachment": 118,
"Shipment": 71,
"SpAppResource": 514,
"SpAppResourceData": 515,
"SpAppResourceDir": 516,
"SpAuditLog": 530,
"SpAuditLogField": 531,
"SpExportSchema": 524,
"SpExportSchemaItem": 525,
"SpExportSchemaItemMapping": 527,
"SpExportSchemaMapping": 528,
"SpFieldValueDefault": 520,
"SpLocaleContainer": 503,
"SpLocaleContainerItem": 504,
"SpLocaleItemStr": 505,
"SpPermission": 521,
"SpPrincipal": 522,
"SpQuery": 517,
"SpQueryField": 518,
"SpReport": 519,
"SpSymbiotaInstance": 533,
"SpTaskSemaphore": 526,
"SpVersion": 529,
"SpViewSetObj": 513,
"SpVisualQuery": 532,
"SpecifyUser": 72,
"Storage": 58,
"StorageAttachment": 148,
"StorageTreeDef": 59,
"StorageTreeDefItem": 60,
"Taxon": 4,
"TaxonAttachment": 119,
"TaxonAttribute": 162,
"TaxonCitation": 75,
"TaxonTreeDef": 76,
"TaxonTreeDefItem": 77,
"TreatmentEvent": 122,
"TreatmentEventAttachment": 149,
"VoucherRelationship": 155,
"Workbench": 79,
"WorkbenchDataItem": 80,
"WorkbenchRow": 90,
"WorkbenchRowExportedRelationship": 126,
"WorkbenchRowImage": 95,
"WorkbenchTemplate": 81,
"WorkbenchTemplateMappingItem": 82
}
Sorted by table id
{
"CollectionObject": 1,
"Locality": 2,
"Geography": 3,
"Taxon": 4,
"Agent": 5,
"Permit": 6,
"Accession": 7,
"Address": 8,
"Determination": 9,
"CollectingEvent": 10,
"AccessionAgent": 12,
"AccessionAuthorization": 13,
"AttributeDef": 16,
"Author": 17,
"Borrow": 18,
"BorrowAgent": 19,
"BorrowMaterial": 20,
"BorrowReturnMaterial": 21,
"Collection": 23,
"CollectingEventAttr": 25,
"Discipline": 26,
"CollectionObjectAttr": 28,
"CollectionObjectCitation": 29,
"Collector": 30,
"Container": 31,
"PaleoContext": 32,
"DataType": 33,
"Disposal": 34,
"DisposalAgent": 35,
"DisposalPreparation": 36,
"DeterminationCitation": 38,
"ExchangeIn": 39,
"ExchangeOut": 40,
"Attachment": 41,
"AttachmentMetadata": 42,
"GeographyTreeDef": 44,
"GeographyTreeDefItem": 45,
"GeologicTimePeriod": 46,
"GeologicTimePeriodTreeDef": 47,
"GeologicTimePeriodTreeDefItem": 48,
"GroupPerson": 49,
"InfoRequest": 50,
"Journal": 51,
"Loan": 52,
"LoanAgent": 53,
"LoanPreparation": 54,
"LoanReturnPreparation": 55,
"LocalityCitation": 57,
"Storage": 58,
"StorageTreeDef": 59,
"StorageTreeDefItem": 60,
"OtherIdentifier": 61,
"Preparation": 63,
"PreparationAttr": 64,
"PrepType": 65,
"Project": 66,
"Appraisal": 67,
"RecordSet": 68,
"ReferenceWork": 69,
"RepositoryAgreement": 70,
"Shipment": 71,
"SpecifyUser": 72,
"ConservEvent": 73,
"TaxonCitation": 75,
"TaxonTreeDef": 76,
"TaxonTreeDefItem": 77,
"AgentGeography": 78,
"Workbench": 79,
"WorkbenchDataItem": 80,
"WorkbenchTemplate": 81,
"WorkbenchTemplateMappingItem": 82,
"FieldNotebook": 83,
"FieldNotebookPageSet": 84,
"FieldNotebookPage": 85,
"AgentSpecialty": 86,
"CollectingTrip": 87,
"DNASequencingRun": 88,
"Exsiccata": 89,
"WorkbenchRow": 90,
"PreparationAttribute": 91,
"CollectingEventAttribute": 92,
"CollectionObjectAttribute": 93,
"Institution": 94,
"WorkbenchRowImage": 95,
"Division": 96,
"AutoNumberingScheme": 97,
"CollectionRelType": 98,
"CollectionRelationship": 99,
"LithoStrat": 100,
"LithoStratTreeDef": 101,
"LithoStratTreeDefItem": 102,
"ConservDescription": 103,
"ExsiccataItem": 104,
"DNASequencingRunCitation": 105,
"CommonNameTx": 106,
"AgentVariant": 107,
"AccessionAttachment": 108,
"AgentAttachment": 109,
"CollectingEventAttachment": 110,
"CollectionObjectAttachment": 111,
"ConservDescriptionAttachment": 112,
"ConservEventAttachment": 113,
"LoanAttachment": 114,
"LocalityAttachment": 115,
"PermitAttachment": 116,
"PreparationAttachment": 117,
"RepositoryAgreementAttachment": 118,
"TaxonAttachment": 119,
"LocalityNameAlias": 120,
"DNASequence": 121,
"TreatmentEvent": 122,
"GeoCoordDetail": 123,
"LocalityDetail": 124,
"AddressOfRecord": 125,
"WorkbenchRowExportedRelationship": 126,
"FieldNotebookAttachment": 127,
"FieldNotebookPageSetAttachment": 128,
"FieldNotebookPageAttachment": 129,
"AttachmentTag": 130,
"Gift": 131,
"GiftPreparation": 132,
"GiftAgent": 133,
"CommonNameTxCitation": 134,
"DNASequencingRunAttachment": 135,
"LatLonPolygon": 136,
"LatLonPolygonPnt": 137,
"MorphBankView": 138,
"AttachmentImageAttribute": 139,
"ExchangeInPrep": 140,
"ExchangeOutPrep": 141,
"InstitutionNetwork": 142,
"ReferenceWorkAttachment": 143,
"GiftAttachment": 144,
"BorrowAttachment": 145,
"FundingAgent": 146,
"DNASequenceAttachment": 147,
"StorageAttachment": 148,
"TreatmentEventAttachment": 149,
"DNAPrimer": 150,
"MaterialSample": 151,
"CollectingEventAuthorization": 152,
"CollectionObjectProperty": 153,
"PreparationProperty": 154,
"VoucherRelationship": 155,
"CollectingTripAttachment": 156,
"CollectingTripAttribute": 157,
"CollectingTripAuthorization": 158,
"AccessionCitation": 159,
"Extractor": 160,
"PcrPerson": 161,
"TaxonAttribute": 162,
"Deaccession": 163,
"DeaccessionAgent": 164,
"DeaccessionAttachment": 165,
"DisposalAttachment": 166,
"Determiner": 167,
"AgentIdentifier": 168,
"ExchangeInAttachment": 169,
"ExchangeOutAttachment": 170,
"PickList": 500,
"PickListItem": 501,
"RecordSetItem": 502,
"SpLocaleContainer": 503,
"SpLocaleContainerItem": 504,
"SpLocaleItemStr": 505,
"SpViewSetObj": 513,
"SpAppResource": 514,
"SpAppResourceData": 515,
"SpAppResourceDir": 516,
"SpQuery": 517,
"SpQueryField": 518,
"SpReport": 519,
"SpFieldValueDefault": 520,
"SpPermission": 521,
"SpPrincipal": 522,
"SpExportSchema": 524,
"SpExportSchemaItem": 525,
"SpTaskSemaphore": 526,
"SpExportSchemaItemMapping": 527,
"SpExportSchemaMapping": 528,
"SpVersion": 529,
"SpAuditLog": 530,
"SpAuditLogField": 531,
"SpVisualQuery": 532,
"SpSymbiotaInstance": 533
}
Sorted by table id (using table labels instead of names)
{
"Collection Object": 1,
"Locality": 2,
"Geography": 3,
"Taxon": 4,
"Agent": 5,
"Permit": 6,
"Accession": 7,
"Address": 8,
"Determination": 9,
"Collecting Information": 10,
"Accession Agent": 12,
"Accession Authorization": 13,
"Attribute Definition": 16,
"Author": 17,
"Borrow": 18,
"Borrow Agent": 19,
"Borrow Material": 20,
"Borrow Return Material": 21,
"Collection": 23,
"Collecting Event Attribute": 92,
"Discipline": 26,
"Collection Object Attribute": 93,
"Collection Object Citation": 29,
"Collector": 30,
"Container": 31,
"Paleo Context": 32,
"Data Type": 33,
"Disposal": 34,
"Disposal Agent": 35,
"Disposal Preparation": 36,
"Determination Citation": 38,
"Exchange In": 39,
"Exchange Out": 40,
"Attachment": 41,
"Attachment Metadata": 42,
"Geography Tree Definition": 44,
"Geography Tree Definition Item": 45,
"Chronostratigraphy": 46,
"Geological Time Period Tree Definition": 47,
"Geological Time Period Tree Definition Item": 48,
"Group Person": 49,
"Information Request": 50,
"Journal": 51,
"Loan": 52,
"Loan Agent": 53,
"Loan Preparation": 54,
"Loan Return Preparation": 55,
"Locality Citation": 57,
"Storage": 58,
"Storage Tree Definition": 59,
"Storage Tree Definition Item": 60,
"Other Identifier": 61,
"Preparation": 63,
"Preparation Attribute": 91,
"Preparation Type": 65,
"Project": 66,
"Appraisal": 67,
"Record Set": 68,
"Reference Work": 69,
"Repository Agreement": 70,
"Shipment": 71,
"Specify User": 72,
"Conservator Event": 73,
"Taxon Citation": 75,
"Taxon Tree Definition": 76,
"Taxon Tree Definition Item": 77,
"Agent Geography": 78,
"WorkBench": 79,
"WorkBench Data Item": 80,
"WorkBench Template": 81,
"WorkBench Template Mapping Item": 82,
"Field Notebook": 83,
"Field Notebook Page Set": 84,
"Field Notebook Page": 85,
"Agent Specialty": 86,
"Collecting Trip": 87,
"DNA Sequencing Run": 88,
"Exsiccata": 89,
"Workbench Row": 90,
"Institution": 94,
"Workbench Row Image": 95,
"Division": 96,
"Auto Numbering Scheme": 97,
"Collection Relationship Type": 98,
"Collection Relationship": 99,
"Lithostratigraphy": 100,
"Lithostratigraphy Tree Definition": 101,
"Lithostratigraphy Tree Definition Item": 102,
"Conservator Description": 103,
"Exsiccata Item": 104,
"DNA Sequencing Run Citation": 105,
"Common Name": 106,
"Agent Variant": 107,
"Accession Attachment": 108,
"Agent Attachment": 109,
"Collecting Event Attachment": 110,
"Collection Object Attachment": 111,
"Conservator Description Attachment": 112,
"Conservator Event Attachment": 113,
"Loan Attachment": 114,
"Locality Attachment": 115,
"Permit Attachment": 116,
"Preparation Attachment": 117,
"Repository Agreement Attachment": 118,
"Taxon Attachment": 119,
"Locality Name Alias": 120,
"DNA Sequence": 121,
"Treatment Event": 122,
"Geo Coord Detail": 123,
"Locality Detail": 124,
"Address Of Record": 125,
"Workbench Row Exported Relationship": 126,
"Field Notebook Attachment": 127,
"Field Notebook Page Set Attachment": 128,
"Field Notebook Page Attachment": 129,
"Attachment Tag": 130,
"Gift": 131,
"Gift Preparation": 132,
"Gift Agent": 133,
"Common Name Tx Citation": 134,
"DNASequencing Run Attachment": 135,
"Lat Lon Polygon": 136,
"Lat Lon Polygon Pnt": 137,
"Morph Bank View": 138,
"Attachment Image Attribute": 139,
"Exchange In Prep": 140,
"Exchange Out Prep": 141,
"Institution Network": 142,
"Reference Work Attachment": 143,
"Gift Attachment": 144,
"Borrow Attachment": 145,
"Funding Agent": 146,
"DNA Sequence Attachment": 147,
"Storage Attachment": 148,
"Treatment Event Attachment": 149,
"DNA Primer": 150,
"Material Sample": 151,
"Collecting Event Authorization": 152,
"Collection Object Property": 153,
"Preparation Property": 154,
"Voucher Relationship": 155,
"Collecting Trip Attachment": 156,
"Collecting Trip Attribute": 157,
"Collecting Trip Authorization": 158,
"Accession Citations": 159,
"Extractor": 160,
"Pcr Persons": 161,
"Taxon Attribute": 162,
"Deaccession": 163,
"Deaccession Agent": 164,
"Deaccession Attachment": 165,
"Disposal Attachment": 166,
"Determiner": 167,
"Agent Identifier": 168,
"Exchange In Attachment": 169,
"Exchange Out Attachment": 170,
"Pick List": 500,
"Pick List Item": 501,
"Record Set Item": 502,
"Locale Container": 503,
"Locale Container Item": 504,
"Locale Item String": 505,
"View Set Object": 513,
"App Resource": 514,
"App Resource Data": 515,
"App Resource Default": 516,
"Sp Query": 517,
"Sp Query Field": 518,
"Sp Report": 519,
"Sp Field Value Default": 520,
"Sp Permission": 521,
"Sp Principal": 522,
"Sp Export Schema": 524,
"Sp Export Schema Item": 525,
"Sp Task Semaphore": 526,
"Sp Export Schema Item Mapping": 527,
"Sp Export Schema Mapping": 528,
"Sp Version": 529,
"Sp Audit Log": 530,
"Sp Audit Log Field": 531,
"Sp Visual Query": 532,
"Sp Symbiota Instance": 533
}
3 Likes
What kind of data are you sharing in your quarterly reports? Are there specific statistics you are interested in Specify preparing for you in a new interface?
We are interested in use cases for presenting statistics from the audit log in the new Statistics page!
1 Like
Over a 3-month period I usually report
-
Number of new records (collection object, collecting events, new taxa at any rank)
-
Number of updated records (georeferences, determinations, added attachments)
-
Number of loans with a brief bit about where they’re going and for what purpose.
I saw the call for statistics use cases but figured I was too late.
If you can configure a query to return the desired count, you can add it to the Statistics page easily once it is released! All queries can be added and visualized in this new interface.
Number of loans with a brief bit about where they’re going and for what purpose.
This point may be the only one we cannot accommodate as the Statistics page does not include support for any value outside of the count.
Are there queries that you cannot build using the query builder that would be helpful to add to the Statistics page?
1 Like
This would be a really helpful feature. When we use the audit log, we are usually looking for a history of changes to a particular collection object (what was changed, on what date), but figuring out which column represents what is always a bit of extra work.
For a TCN I need to report the number of records georeferenced within a certain time period - I don’t know if I can create a query based on a project number (this is a customized collection object field - last I checked, ‘projects’ weren’t fully operational in SP7) that also identifies a timestamp modified for the latitude/longitude.