-
Notifications
You must be signed in to change notification settings - Fork 1
/
CountApps-AssociatedReports.vbs
346 lines (287 loc) · 14.5 KB
/
CountApps-AssociatedReports.vbs
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
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
Dim adoconn
Dim rs
Dim str
Dim i 'Counter
dim outputl 'Email body
dim CountName 'Count for each app
set filesys=CreateObject("Scripting.FileSystemObject")
Dim strCurDir
strCurDir = filesys.GetParentFolderName(Wscript.ScriptFullName)
'Gather variables from smapp.ini
If filesys.FileExists(strCurDir & "\smapp.ini") then
'Database
DBLocation = ReadIni(strCurDir & "\smapp.ini", "Database", "DBLocation" )
DBUser = ReadIni(strCurDir & "\smapp.ini", "Database", "DBUser" )
DBPass = ReadIni(strCurDir & "\smapp.ini", "Database", "DBPass" )
'Email - Defaults to anonymous login
RptToEmail = ReadIni(strCurDir & "\smapp.ini", "Email", "RptToEmail" )
RptFromEmail = ReadIni(strCurDir & "\smapp.ini", "Email", "RptFromEmail" )
EmailSvr = ReadIni(strCurDir & "\smapp.ini", "Email", "EmailSvr" )
EmailPort = ReadIni(strCurDir & "\smapp.ini", "Email", "EmailPort" )
EmailAuthType = ReadIni(strCurDir & "\smapp.ini", "Email", "EmailAuthType" )
EmailUserName = ReadIni(strCurDir & "\smapp.ini", "Email", "EmailUserName" )
EmailPassword = ReadIni(strCurDir & "\smapp.ini", "Email", "EmailPassword" )
'Additional email settings found in smapp.ini
'WebGUI
BaseURL = ReadIni(strCurDir & "\smapp.ini", "WebGUI", "BaseURL" )
else
msgbox "INI file not found at: " & strCurDir & "\smapp.ini" & vbCrlf & "Please run IngestCSV.vbs first before running this file."
end if
outputl = ""
Set adoconn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
adoconn.Open "Driver={MySQL ODBC 8.4 ANSI Driver};Server=" & DBLocation & ";" & _
"Database=software_matrix; User=" & DBUser & "; Password=" & DBPass & ";"
CountApps 'Count apps and update Computers column on discoveredapplications table
CheckLicenses 'Check licensed apps vs. actual installed count
if outputl <> "" then
outputl = "<html><head> <style>BODY{font-family: Arial; font-size: 10pt;}TABLE{border: 1px solid black; border-collapse: collapse;}TH{border: 1px solid black; background: #dddddd; padding: 5px; }TD{border: 1px solid black; padding: 5px; }</style> </head><body>" & vbcrlf & outputl
SendMail RptToEmail, "Software Matrix: Licensing Report"
outputl = ""
end if
CountHighRiskApps 'Report on top 10 high risk apps
if outputl <> "" then
outputl = "<html><head> <style>BODY{font-family: Arial; font-size: 10pt;}TABLE{border: 1px solid black; border-collapse: collapse;}TH{border: 1px solid black; background: #dddddd; padding: 5px; }TD{border: 1px solid black; padding: 5px; }</style> </head><body>" & vbcrlf & outputl
SendMail RptToEmail, "Software Matrix: High Risk Software Report"
outputl = ""
end if
Set adoconn = Nothing
Set rs = Nothing
Function CountApps()
str = "Select * from discoveredapplications where LastDiscovered IS NOT NULL and LastDiscovered > '" & format(date() - 7, "YYYY-MM-DD") & "' order by Name;"
rs.Open str, adoconn, 3, 3 'OpenType, LockType
do while not rs.eof
str = "select count(*) from applicationsdump where Name = '" & replace(rs("Name"),"'","''") & "';"
CountName = adoconn.Execute(str) 'Kind of a hack way of doing this, results in an array with 0 being the count
rs("Computers") = CountName(0)
'msgbox rs("Name") & vbCrlf & CountName(0)
'Fix discovered apps that have no instances of the Version_Newest
str = "select count(*) from applicationsdump where Name = '" & replace(rs("Name"),"'","''") & "' and Version = '" & rs("Version_Newest") & "';"
CountName = adoconn.Execute(str) 'Kind of a hack way of doing this, results in an array with 0 being the count
if CountName(0) = "0" then rs("Version_Newest") = rs("Version_Oldest")
'if CountName(0) = "0" then msgbox rs("Name")
rs.update
rs.movenext
loop
rs.close
End Function
Function CheckLicenses()
str = "SELECT L.Name, L.Publisher, L.Amount, D.Computers, D.ID FROM software_matrix.licensedapps as L inner join software_matrix.discoveredapplications as D on L.Name = D.Name and D.Computers > L.Amount order by L.Name;"
rs.Open str, adoconn, 2, 1 'OpenType, LockType
if not rs.eof then
'Header Info
outputl = outputl & "<p><b>Software Licensing Report:</b></p>" & vbcrlf
outputl = outputl & "<table>" & vbcrlf
outputl = outputl & "<tr>" & vbcrlf
outputl = outputl & " <th>Name</th>" & vbcrlf
outputl = outputl & " <th>Publisher</th>" & vbcrlf
outputl = outputl & " <th>Licensed Amount</th>" & vbcrlf
outputl = outputl & " <th>Installed Amount</th>" & vbcrlf
outputl = outputl & "</tr>" & vbcrlf
rs.MoveFirst
end if
do while not rs.eof
outputl = outputl & "<tr>" & vbcrlf
outputl = outputl & " <td><a href=""" & BaseURL & "/edit-appinfo.php?id=" & rs("ID") & """>" & rs("Name") & "</a></td>" & vbcrlf
outputl = outputl & " <td>" & rs("Publisher") & "</td>" & vbcrlf
outputl = outputl & " <td>" & rs("Amount") & "</td>" & vbcrlf
outputl = outputl & " <td bgcolor=yellow>" & rs("Computers") & "</td>" & vbcrlf
outputl = outputl & "</tr>" & vbcrlf
rs.movenext
if rs.eof then outputl = outputl & "</table>" & vbcrlf
loop
rs.close
End Function
Function CountHighRiskApps()
str = "select Name, count(Name), max(DateAdded), (select Computers from software_matrix.discoveredapplications where discoveredapplications.Name = highriskapps.Name) Computers from highriskapps where DateAdded > '" & format(date() - 365, "YYYY-MM-DD") & "' group by Name order by count(Name) DESC, max(DateAdded) DESC, Computers DESC;"
rs.Open str, adoconn, 2, 1 'OpenType, LockType
if not rs.eof then
'Header Info
outputl = outputl & "<p><b>High Risk Application Report (top 10):</b></p>" & vbcrlf
outputl = outputl & "<table>" & vbcrlf
outputl = outputl & "<tr>" & vbcrlf
outputl = outputl & " <th>Name</th>" & vbcrlf
outputl = outputl & " <th>Vulnerabilities prior year</th>" & vbcrlf
outputl = outputl & " <th>Date of Last Vulnerability</th>" & vbcrlf
outputl = outputl & " <th>Installed Amount</th>" & vbcrlf
outputl = outputl & "</tr>" & vbcrlf
rs.MoveFirst
end if
i = 1
do while not rs.eof and not i > 10
outputl = outputl & "<tr>" & vbcrlf
outputl = outputl & " <td>" & rs("Name") & "</td>" & vbcrlf
outputl = outputl & " <td>" & rs("count(Name)") & "</td>" & vbcrlf
outputl = outputl & " <td>" & rs("max(DateAdded)") & "</td>" & vbcrlf
outputl = outputl & " <td>" & rs("Computers") & "</td>" & vbcrlf
outputl = outputl & "</tr>" & vbcrlf
i = i + 1
rs.movenext
if rs.eof or i > 10 then outputl = outputl & "</table>" & vbcrlf
loop
rs.close
End Function
Function SendMail(TextRcv,TextSubject)
Const cdoSendUsingPickup = 1 'Send message using the local SMTP service pickup directory.
Const cdoSendUsingPort = 2 'Send the message using the network (SMTP over the network).
Const cdoAnonymous = 0 'Do not authenticate
Const cdoBasic = 1 'basic (clear-text) authentication
Const cdoNTLM = 2 'NTLM
Set objMessage = CreateObject("CDO.Message")
objMessage.Subject = TextSubject
objMessage.From = RptFromEmail
objMessage.To = TextRcv
objMessage.HTMLBody = outputl
'==This section provides the configuration information for the remote SMTP server.
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = EmailSvr
'Type of authentication, NONE, Basic (Base64 encoded), NTLM
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = EmailAuthType
if EmailAuthType > 0 then
'Your UserID on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = EmailUserName
'Your password on the SMTP server
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = EmailPassword
end if
'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = EmailPort
'Use SSL for the connection (False or True)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
'Connection Timeout in seconds (the maximum time CDO will try to establish a connection to the SMTP server)
objMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
objMessage.Configuration.Fields.Update
'==End remote SMTP server configuration section==
objMessage.Send
End Function
Function Format(vExpression, sFormat)
Dim nExpression
nExpression = sFormat
if isnull(vExpression) = False then
if instr(1,sFormat,"Y") > 0 or instr(1,sFormat,"M") > 0 or instr(1,sFormat,"D") > 0 or instr(1,sFormat,"H") > 0 or instr(1,sFormat,"S") > 0 then 'Time/Date Format
vExpression = cdate(vExpression)
if instr(1,sFormat,"AM/PM") > 0 and int(hour(vExpression)) > 12 then
nExpression = replace(nExpression,"HH",right("00" & hour(vExpression)-12,2)) '2 character hour
nExpression = replace(nExpression,"H",hour(vExpression)-12) '1 character hour
nExpression = replace(nExpression,"AM/PM","PM") 'If if its afternoon, its PM
else
nExpression = replace(nExpression,"HH",right("00" & hour(vExpression),2)) '2 character hour
nExpression = replace(nExpression,"H",hour(vExpression)) '1 character hour
if int(hour(vExpression)) = 12 then nExpression = replace(nExpression,"AM/PM","PM") '12 noon is PM while anything else in this section is AM (fixed 04/19/2019 thanks to our HR Dept.)
nExpression = replace(nExpression,"AM/PM","AM") 'If its not PM, its AM
end if
nExpression = replace(nExpression,":MM",":" & right("00" & minute(vExpression),2)) '2 character minute
nExpression = replace(nExpression,"SS",right("00" & second(vExpression),2)) '2 character second
nExpression = replace(nExpression,"YYYY",year(vExpression)) '4 character year
nExpression = replace(nExpression,"YY",right(year(vExpression),2)) '2 character year
nExpression = replace(nExpression,"DD",right("00" & day(vExpression),2)) '2 character day
nExpression = replace(nExpression,"D",day(vExpression)) '(N)N format day
nExpression = replace(nExpression,"MMM",left(MonthName(month(vExpression)),3)) '3 character month name
if instr(1,sFormat,"MM") > 0 then
nExpression = replace(nExpression,"MM",right("00" & month(vExpression),2)) '2 character month
else
nExpression = replace(nExpression,"M",month(vExpression)) '(N)N format month
end if
elseif instr(1,sFormat,"N") > 0 then 'Number format
nExpression = vExpression
if instr(1,sFormat,".") > 0 then 'Decimal format
if instr(1,nExpression,".") > 0 then 'Both have decimals
do while instr(1,sFormat,".") > instr(1,nExpression,".")
nExpression = "0" & nExpression
loop
if len(nExpression)-instr(1,nExpression,".") >= len(sFormat)-instr(1,sFormat,".") then
nExpression = left(nExpression,instr(1,nExpression,".")+len(sFormat)-instr(1,sFormat,"."))
else
do while len(nExpression)-instr(1,nExpression,".") < len(sFormat)-instr(1,sFormat,".")
nExpression = nExpression & "0"
loop
end if
else
nExpression = nExpression & "."
do while len(nExpression) < len(sFormat)
nExpression = nExpression & "0"
loop
end if
else
do while len(nExpression) < sFormat
nExpression = "0" and nExpression
loop
end if
else
msgbox "Formating issue on page. Unrecognized format: " & sFormat
end if
Format = nExpression
end if
End Function
Function ReadIni( myFilePath, mySection, myKey ) 'Thanks to http://www.robvanderwoude.com
' This function returns a value read from an INI file
'
' Arguments:
' myFilePath [string] the (path and) file name of the INI file
' mySection [string] the section in the INI file to be searched
' myKey [string] the key whose value is to be returned
'
' Returns:
' the [string] value for the specified key in the specified section
'
' CAVEAT: Will return a space if key exists but value is blank
'
' Written by Keith Lacelle
' Modified by Denis St-Pierre and Rob van der Woude
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Dim intEqualPos
Dim objFSO, objIniFile
Dim strFilePath, strKey, strLeftString, strLine, strSection
Set objFSO = CreateObject( "Scripting.FileSystemObject" )
ReadIni = ""
strFilePath = Trim( myFilePath )
strSection = Trim( mySection )
strKey = Trim( myKey )
If objFSO.FileExists( strFilePath ) Then
Set objIniFile = objFSO.OpenTextFile( strFilePath, ForReading, False )
Do While objIniFile.AtEndOfStream = False
strLine = Trim( objIniFile.ReadLine )
' Check if section is found in the current line
If LCase( strLine ) = "[" & LCase( strSection ) & "]" Then
strLine = Trim( objIniFile.ReadLine )
' Parse lines until the next section is reached
Do While Left( strLine, 1 ) <> "["
' Find position of equal sign in the line
intEqualPos = InStr( 1, strLine, "=", 1 )
If intEqualPos > 0 Then
strLeftString = Trim( Left( strLine, intEqualPos - 1 ) )
' Check if item is found in the current line
If LCase( strLeftString ) = LCase( strKey ) Then
ReadIni = Trim( Mid( strLine, intEqualPos + 1 ) )
' In case the item exists but value is blank
If ReadIni = "" Then
ReadIni = " "
End If
' Abort loop when item is found
Exit Do
End If
End If
' Abort if the end of the INI file is reached
If objIniFile.AtEndOfStream Then Exit Do
' Continue with next line
strLine = Trim( objIniFile.ReadLine )
Loop
Exit Do
End If
Loop
objIniFile.Close
Else
WScript.Echo strFilePath & " doesn't exists. Exiting..."
Wscript.Quit 1
End If
End Function