-
Notifications
You must be signed in to change notification settings - Fork 106
/
CloudAzureSQLServerDataBaseReport.ps1
153 lines (126 loc) · 5.7 KB
/
CloudAzureSQLServerDataBaseReport.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
<#PSScriptInfo
.VERSION 1.0
.GUID 51d5feb8-47af-4e08-bdb6-f62a0e9e13f5
.AUTHOR Vikas Sukhija
.COMPANYNAME TechWizard.cloud
.COPYRIGHT Vikas Sukhija
.TAGS
.LICENSEURI https://techwizard.cloud/
.PROJECTURI https://techwizard.cloud/
.ICONURI
.EXTERNALMODULEDEPENDENCIES
.REQUIREDSCRIPTS
.EXTERNALSCRIPTDEPENDENCIES
.RELEASENOTES https://techwizard.cloud/
.PRIVATEDATA
===========================================================================
Created with: ISE
Created on: 8/24/2023 1:46 PM
Created by: Vikas Sukhija
Organization:
Filename: CloudAzureSQLServerDataBaseReport.ps1
===========================================================================
#>
<#
.DESCRIPTION
This will generate Azure SQL server database report across the organization
#>
param()
#################logs and variables##########################
$log = Write-Log -Name "CloudAzureSQLServerDataBaseReport" -folder "logs" -Ext "log"
$Report = Write-Log -Name "CloudAzureSQLServerDataBaseReport" -folder "Report" -Ext "csv"
$smtpserver = "smtpserver"
$from = "[email protected]"
$email1 = "[email protected]"
$erroremail = "[email protected]"
$logrecyclelimit = "60"
#################get-credentials##########################
if(Test-Path -Path ".\Password.xml"){
Write-Log -Message "Password file Exists" -path $log
}else{
Write-Log -Message "Generate password" -path $log
$Credential = Get-Credential
$Credential | Export-Clixml ".\Password.xml"
}
#############################################################
$Credential = $null
$Credential = Import-Clixml ".\Password.xml"
##################Connect to Azure####################
#######################################################################
try
{
Write-Log -message "Start ......... Script" -path $log
Connect-AzAccount -Credential $Credential
Write-Log -message "Loaded All Modules" -path $log
}
catch
{
$exception = $_.Exception.Message
Write-Log -message "exception $exception has occured loading Modules - CloudAzureSQLServerDataBaseReport" -path $log -Severity Error
Send-MailMessage -SmtpServer $smtpserver -From $from -To $erroremail -Subject "Error - CloudAzureSQLServerDataBaseReport" -Body $($_.Exception.Message)
break;
}
################################Query all SQL instances########################
try{
Write-Log -message "Query all SQL Servers Across all Subs" -path $log
$query = @"
Resources
| where type == "microsoft.sql/servers"
| project subscriptionId, resourceGroup, name
| order by subscriptionId asc
| summarize by subscriptionId
"@
$resourceData = Search-AzGraph -Query $query
}
catch
{
$exception = $_.Exception.Message
Write-Log -message "exception $exception has occured loading SQL Servers - CloudAzureSQLServerDataBaseReport" -path $log -Severity Error
Send-MailMessage -SmtpServer $smtpserver -From $from -To $erroremail -Subject "Error - CloudAzureSQLServerDataBaseReport" -Body $($_.Exception.Message)
break;
}
##########################Loop thru all subscripotions and get SQL Instances################
$collinventory = @()
foreach ($subscription in $resourceData) {
$sqlInstances=$null
Select-AzSubscription -SubscriptionId $subscription.subscriptionId
$sqlquery = @"
Resources
| where type == "microsoft.sql/servers"
| where subscriptionId == '$($subscription.subscriptionId)'
| project id,resourceGroup, name, type,location, tags
"@
Write-Log -message "Processing ........$subscription" -path $log
$sqlInstances = Search-AzGraph -Query $sqlquery
foreach($sqlinstance in $sqlInstances){
$mcoll = "" | Select ServerName,ResourceID,DomainName,Version,AdministratorName,DatabaseCount,provider,Location,PublicNetworkAccess,CreationDate,tags
$getsqlserver=$getsqldatabase=$null
$getsqlserver = Get-AzSqlServer -ResourceGroupName $sqlinstance.resourceGroup -ServerName $sqlinstance.name
$getsqldatabase = Get-AzSqlDatabase -ServerName $sqlinstance.name -ResourceGroupName $sqlinstance.resourceGroup
# Process $sqlInstances data for each subscription
$mcoll.ServerName = $sqlinstance.name
$mcoll.ResourceID = $sqlinstance.id
$mcoll.DomainName = $getsqlserver.FullyQualifiedDomainName
$mcoll.Version = $getsqlserver.ServerVersion
$mcoll.AdministratorName = $getsqlserver.SqlAdministratorLogin
$mcoll.DatabaseCount = $getsqldatabase.count
$mcoll.provider = $sqlinstance.type
$mcoll.Location = $sqlinstance.location
$mcoll.PublicNetworkAccess = $getsqlserver.PublicNetworkAccess
$mcoll.CreationDate = $getsqldatabase[0].CreationDate
$mcoll.tags = $sqlinstance.tags -join ","
$collinventory += $mcoll
}
}
if($error){
Write-Log -message "exception $errot has occured loading SQL Servers - CloudAzureSQLServerDataBaseReport" -path $log -Severity Error
Send-MailMessage -SmtpServer $smtpserver -From $from -To $erroremail -Subject "Error - CloudAzureSQLServerDataBaseReport" -Body $Error[0].ToString()
}
##########################Export to CSV################
$collinventory | Export-Csv $report -NoTypeInformation
Send-MailMessage -SmtpServer $smtpserver -From $from -To $email1 -bcc $erroremail -Subject "Report - Azure SQL Servers" -Attachments $Report
Disconnect-AzAccount
###############################Recycle logs ###############################################
Set-Recyclelogs -foldername "logs" -limit $logrecyclelimit -Confirm:$false
Write-Log -Message "Script Finished" -path $log
Send-MailMessage -SmtpServer $smtpserver -From $from -To $erroremail -Subject "Log - CloudAzureSQLServerDataBaseReport" -Attachments $log