A frustration in dealing with table schemas for Log Analytics and Azure Data Explorer is inacuracies with Microsoft's published documentation for the schema. Another surprise is that the schema returned by using a GET against the workspace (https://learn.microsoft.com/en-us/rest/api/loganalytics/schema/get?view=rest-loganalytics-2023-09-01&tabs=HTTP) has inaccuracies too!
I've previously written about retrieving Log Analytics schemas in the post: 'Creating ADX table Schemas for Defender, Entra and Microsoft Sentinel' although that approach only returns one table schema at a time.
These issues can be overcome by directly querying each table within Log Analytics with a GetSchema command.
$uri ="https://api.loganalytics.io/v1/workspaces/$($workspaceId)/query?query=$($Table)%20%7C%20getschema%20"This uses my own authentication modules for Microsoft services that is available here: https://github.com/LaurieRhodes/AZRest
You can use your own preferred authentication as long as you use the right scope for a token to run queries against Log Analytics:
'analytics'{$TokenEndpoint = "https://login.microsoftonline.com/$($tenant)/oauth2/v2.0/token"
$RequestScope = "https://api.loganalytics.io/.default"
$ResourceID = "https://api.loganalytics.io/"
} If you just want a copy of exported JSON, you can get a copy here:
https://laurierhodes.info/sites/default/files/LASchema/LASchema.json
$SchemaCollection = $(Get-Content -Path C:\temp\LASchema.json -Raw | convertfrom-json)
$SchemaCollection | Where-Object tablename -Contains "Syslog"Using the export you can programatically work with different tables knowing the schema is accurate.
The following script shows how to create the mass export of accurate schemas from Log Analytics. You will find that some tables wont exist in your targetted workspace. and you may choose to add any custom tables you have created to the list.
# The Workspace Id of the Log Analytics Workspace
$workspaceId ='XXXXXXXXX-XXXX-XXXX-XXXXXXXXXXXXXX'
$outputdir = "C:\temp"
Import-Module "C:\Temp\AZRest\AZRest.psm1"
$authHeader = Get-Header -scope analytics -Tenant "laurierhodes.info" -AppId "XXXXXXXX-XXXX-XXXX-XXXXXXX" `
-secret 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
$TableNamesList = @(
'AACAudit'
'AACHttpRequest'
'AADDomainServicesAccountLogon'
'AADDomainServicesAccountManagement'
'AADDomainServicesDirectoryServiceAccess'
'AADDomainServicesLogonLogoff'
'AADDomainServicesPolicyChange'
'AADDomainServicesPrivilegeUse'
'AADManagedIdentitySignInLogs'
'AADNonInteractiveUserSignInLogs'
'AADProvisioningLogs'
'AADRiskyUsers'
'AADServicePrincipalSignInLogs'
'AADUserRiskEvents'
'ABAPAuditLog'
'ABSBotRequests'
'ACSAuthIncomingOperations'
'ACSBillingUsage'
'ACSChatIncomingOperations'
'ACSSMSIncomingOperations'
'ADAssessmentRecommendation'
'AddonAzureBackupAlerts'
'AddonAzureBackupJobs'
'AddonAzureBackupPolicy'
'AddonAzureBackupProtectedInstance'
'AddonAzureBackupStorage'
'ADFActivityRun'
'ADFAirflowSchedulerLogs'
'ADFAirflowTaskLogs'
'ADFAirflowWebLogs'
'ADFAirflowWorkerLogs'
'ADFPipelineRun'
'ADFSandboxActivityRun'
'ADFSandboxPipelineRun'
'ADFSSignInLogs'
'ADFSSISIntegrationRuntimeLogs'
'ADFSSISPackageEventMessageContext'
'ADFSSISPackageEventMessages'
'ADFSSISPackageExecutableStatistics'
'ADFSSISPackageExecutionComponentPhases'
'ADFSSISPackageExecutionDataStatistics'
'ADFTriggerRun'
'ADPAudit'
'ADPDiagnostics'
'ADPRequests'
'ADReplicationResult'
'ADSecurityAssessmentRecommendation'
'ADTDigitalTwinsOperation'
'ADTModelsOperation'
'ADTQueryOperation'
'ADXCommand'
'ADXJournal'
'ADXQuery'
'ADXTableDetails'
'ADXTableUsageStatistics'
'AegDeliveryFailureLogs'
'AegPublishFailureLogs'
'AirflowDagProcessingLogs'
'Alert'
'AlertEvidence'
'AlertInfo'
'AmlComputeClusterEvent'
'AmlComputeCpuGpuUtilization'
'AmlComputeInstanceEvent'
'AmlComputeJobEvent'
'AmlDataLabelEvent'
'AmlDataSetEvent'
'AmlDataStoreEvent'
'AmlDeploymentEvent'
'AmlEnvironmentEvent'
'AmlInferencingEvent'
'AmlModelsEvent'
'AmlOnlineEndpointConsoleLog'
'AmlPipelineEvent'
'AmlRunEvent'
'AmlRunStatusChangedEvent'
'Anomalies'
'ApiManagementGatewayLogs'
'AppAvailabilityResults'
'AppBrowserTimings'
'AppCenterError'
'AppDependencies'
'AppEvents'
'AppExceptions'
'AppMetrics'
'AppPageViews'
'AppPerformanceCounters'
'AppPlatformIngressLogs'
'AppPlatformLogsforSpring'
'AppPlatformSystemLogs'
'AppRequests'
'AppServiceAntivirusScanAuditLogs'
'AppServiceAppLogs'
'AppServiceAuditLogs'
'AppServiceConsoleLogs'
'AppServiceEnvironmentPlatformLogs'
'AppServiceFileAuditLogs'
'AppServiceHTTPLogs'
'AppServiceIPSecAuditLogs'
'AppServicePlatformLogs'
'AppSystemEvents'
'AppTraces'
'ASimAuditEventLogs'
'ASimAuthenticationEventLogs'
'ASimDhcpEventLogs'
'ASimDnsActivityLogs'
'ASimDnsAuditLogs'
'ASimFileEventLogs'
'ASimNetworkSessionLogs'
'ASimProcessEventLogs'
'ASimRegistryEventLogs'
'ASimUserManagementActivityLogs'
'ASimWebSessionLogs'
'AuditLogs'
'AutoscaleEvaluationsLog'
'AutoscaleScaleActionsLog'
'AWSCloudTrail'
'AWSCloudWatch'
'AWSGuardDuty'
'AWSVPCFlow'
'AzureAssessmentRecommendation'
'AzureDevOpsAuditing'
'BehaviorAnalytics'
'BlockchainApplicationLog'
'BlockchainProxyLog'
'CDBCassandraRequests'
'CDBControlPlaneRequests'
'CDBDataPlaneRequests'
'CDBGremlinRequests'
'CDBMongoRequests'
'CDBPartitionKeyRUConsumption'
'CDBPartitionKeyStatistics'
'CDBQueryRuntimeStatistics'
'CloudAppEvents'
'CommonSecurityLog'
'ComputerGroup'
'ConfigurationChange'
'ConfigurationData'
'ContainerImageInventory'
'ContainerInventory'
'ContainerLog'
'ContainerLogV2'
'ContainerNodeInventory'
'ContainerRegistryLoginEvents'
'ContainerRegistryRepositoryEvents'
'ContainerServiceLog'
'CoreAzureBackup'
'DatabricksAccounts'
'DatabricksClusters'
'DatabricksDBFS'
'DatabricksFeatureStore'
'DatabricksGenie'
'DatabricksGlobalInitScripts'
'DatabricksInstancePools'
'DatabricksJobs'
'DatabricksMLflowAcledArtifact'
'DatabricksMLflowExperiment'
'DatabricksNotebook'
'DatabricksRemoteHistoryService'
'DatabricksSecrets'
'DatabricksSQLPermissions'
'DatabricksSSH'
'DatabricksWorkspace'
'DataverseActivity'
'DefenderForSqlAlerts'
'DefenderForSqlTelemetry'
'DeviceEvents'
'DeviceFileCertificateInfo'
'DeviceFileEvents'
'DeviceImageLoadEvents'
'DeviceInfo'
'DeviceLogonEvents'
'DeviceNetworkEvents'
'DeviceNetworkInfo'
'DeviceProcessEvents'
'DeviceRegistryEvents'
'DeviceTvmSecureConfigurationAssessment'
'DeviceTvmSecureConfigurationAssessmentKB'
'DeviceTvmSoftwareInventory'
'DeviceTvmSoftwareVulnerabilities'
'DeviceTvmSoftwareVulnerabilitiesKB'
'DnsEvents'
'DnsInventory'
'DummyHydrationFact'
'DynamicEventCollection'
'Dynamics365Activity'
'EmailAttachmentInfo'
'EmailEvents'
'EmailPostDeliveryEvents'
'EmailUrlInfo'
'Event'
'ExchangeAssessmentRecommendation'
'ExchangeOnlineAssessmentRecommendation'
'FailedIngestion'
'FunctionAppLogs'
'GCPAuditLogs'
'GoogleCloudSCC'
'HDInsightAmbariClusterAlerts'
'HDInsightAmbariSystemMetrics'
'HDInsightHadoopAndYarnLogs'
'HDInsightHadoopAndYarnMetrics'
'HDInsightHBaseLogs'
'HDInsightHBaseMetrics'
'HDInsightHiveAndLLAPLogs'
'HDInsightHiveAndLLAPMetrics'
'HDInsightHiveTezAppStats'
'HDInsightKafkaLogs'
'HDInsightKafkaMetrics'
'HDInsightOozieLogs'
'HDInsightSecurityLogs'
'HDInsightSparkApplicationEvents'
'HDInsightSparkBlockManagerEvents'
'HDInsightSparkEnvironmentEvents'
'HDInsightSparkExecutorEvents'
'HDInsightSparkJobEvents'
'HDInsightSparkLogs'
'HDInsightSparkSQLExecutionEvents'
'HDInsightSparkStageEvents'
'HDInsightSparkStageTaskAccumulables'
'HDInsightSparkTaskEvents'
'HealthStateChangeEvent'
'HuntingBookmark'
'IdentityDirectoryEvents'
'IdentityInfo'
'IdentityLogonEvents'
'IdentityQueryEvents'
'InsightsMetrics'
'IntuneAuditLogs'
'IntuneDevices'
'IntuneOperationalLogs'
'KubeEvents'
'KubeHealth'
'KubeMonAgentEvents'
'KubeNodeInventory'
'KubePodInventory'
'KubePVInventory'
'KubeServices'
'LAQueryLogs'
'LinuxAuditLog'
'McasShadowItReporting'
'MCCEventLogs'
'MicrosoftAzureBastionAuditLogs'
'MicrosoftDataShareReceivedSnapshotLog'
'MicrosoftDataShareSentSnapshotLog'
'MicrosoftGraphActivityLogs'
'MicrosoftHealthcareApisAuditLogs'
'MicrosoftPurviewInformationProtection'
'NetworkAccessTraffic'
'NetworkMonitoring'
'NTAIpDetails'
'NTANetAnalytics'
'NTATopologyDetails'
'NWConnectionMonitorPathResult'
'NWConnectionMonitorTestResult'
'OfficeActivity'
'Perf'
'PowerAppsActivity'
'PowerAutomateActivity'
'PowerBIActivity'
'PowerBIDatasetsWorkspace'
'PowerPlatformAdminActivity'
'PowerPlatformConnectorActivity'
'PowerPlatformDlpActivity'
'ProcessInvestigator'
'ProjectActivity'
'ProtectionStatus'
'PurviewScanStatusLogs'
'RomeDetectionEvent'
'SCCMAssessmentRecommendation'
'SCOMAssessmentRecommendation'
'SecureScoreControls'
'SecureScores'
'SecurityAlert'
'SecurityBaseline'
'SecurityBaselineSummary'
'SecurityDetection'
'SecurityEvent'
'SecurityIncident'
'SecurityIoTRawEvent'
'SecurityNestedRecommendation'
'SecurityRecommendation'
'SecurityRegulatoryCompliance'
'SentinelHealth'
'ServiceMap'
'SfBAssessmentRecommendation'
'SfBOnlineAssessmentRecommendation'
'SharePointOnlineAssessmentRecommendation'
'SignalRServiceDiagnosticLogs'
'SigninLogs'
'SPAssessmentRecommendation'
'SQLAssessmentRecommendation'
'SqlAtpStatus'
'SQLSecurityAuditEvents'
'SqlThreatProtectionLoginAudits'
'SqlVulnerabilityAssessmentResult'
'SqlVulnerabilityAssessmentScanStatus'
'StorageBlobLogs'
'StorageFileLogs'
'StorageInsightsAccountPropertiesDaily'
'StorageInsightsDailyMetrics'
'StorageInsightsHourlyMetrics'
'StorageInsightsMonthlyMetrics'
'StorageInsightsWeeklyMetrics'
'StorageQueueLogs'
'StorageTableLogs'
'SucceededIngestion'
'SynapseBigDataPoolApplicationsEnded'
'SynapseBuiltinSqlPoolRequestsEnded'
'SynapseDXFailedIngestion'
'SynapseDXSucceededIngestion'
'SynapseGatewayApiRequests'
'SynapseIntegrationActivityRuns'
'SynapseIntegrationPipelineRuns'
'SynapseIntegrationTriggerRuns'
'SynapseRbacOperations'
'SynapseSqlPoolDmsWorkers'
'SynapseSqlPoolExecRequests'
'SynapseSqlPoolRequestSteps'
'SynapseSqlPoolSqlRequests'
'SynapseSqlPoolWaits'
'Syslog'
'ThreatIntelligenceIndicator'
'TSIIngress'
'UCClient'
'UCClientReadinessStatus'
'UCClientUpdateStatus'
'UCDeviceAlert'
'UCDOAggregatedStatus'
'UCDOStatus'
'UCServiceUpdateStatus'
'UCUpdateAlert'
'Update'
'UpdateRunProgress'
'UpdateSummary'
'UrlClickEvents'
'W3CIISLog'
'WaaSDeploymentStatus'
'WaaSInsiderStatus'
'WaaSUpdateStatus'
'Watchlist'
'WebPubSubConnectivity'
'WebPubSubHttpRequest'
'WebPubSubMessaging'
'WindowsClientAssessmentRecommendation'
'WindowsEvent'
'WindowsFirewall'
'WindowsServerAssessmentRecommendation'
'WireData'
'WorkloadDiagnosticLogs'
'WUDOAggregatedStatus'
'WUDOStatus'
'WVDAgentHealthStatus'
'WVDCheckpoints'
'WVDConnectionNetworkData'
'WVDConnections'
'WVDErrors'
'WVDFeeds'
'WVDHostRegistrations'
'WVDManagement'
)
$OutputCollection = @()
# Create a class for the properties of the schema
class laField {
[String]$name
[String]$displayName
[String]$type
[String]$tablename
}
foreach ($Table in $TableNamesList){
$uri ="https://api.loganalytics.io/v1/workspaces/$($workspaceId)/query?query=$($Table)%20%7C%20getschema%20"
$response = $null
$response = Invoke-RestMethod -Uri $uri -Headers $authHeader -Method Get
foreach ($row in $response.tables[0].rows){
$tmpobject = [laField]::New()
$tmpobject.name = $row[0] # column name
$tmpobject.displayName = $row[0] # column name
$tmpobject.type = $row[3] # data type
$tmpobject.tablename = $Table
# Add the schema object to the collection
$OutputCollection += $tmpobject
}
}
Out-File -InputObject $(Convertto-json -inputobject $OutputCollection -Depth 50) -FilePath "$($outputdir)\LASchema.json" -Force- Log in to post comments