'Kaseya Event Log Exporter
'January  19th, 2015
'Property of OmegaSecure
'DO NOT DISTRIBUTE.
if (WScript.Arguments.Count = 3) OR (Wscript.Arguments.Count = 4) then

	dim objFSO
	dim connstrfile
	dim connstr 
	dim kfolder
	dim connstringfile
	dim sqlquery
	dim linetowrite
	dim dbqcnt
	dim sqlcount
	Dim SQLStatement
	Dim wShell
	Dim kfolderkey
	Dim connrun
	Dim curr
	Dim agentGuid
	Dim machName
	Dim groupName
	Dim logType
	Dim eventType
	Dim eventTime
	Dim ApplicationName
	Dim EventCategory
	Dim eventId
	Dim username
	Dim computerName
	Dim EventMessage
	Dim partitionId
	Dim date
	Dim eventlogdate
	Dim startdate
	Dim enddate
	Dim dbq
	Dim dbq2
	Dim dbq3
	Dim dbq4
	Dim dbq5
	Dim curr4
	Dim orgId
	Dim curr3
	Dim curr2
	Dim orgIdName
	Dim EventLogTypeId
	Dim eventLogType
	Dim eventTypeName
	Dim csvfilesize
	Dim checkfolder
	Dim displayName
	Dim message
	Dim source
	Dim category
	Dim numdays
	Dim stdout
	Dim totalexports
	Dim starttime
	Dim endtime
	Dim customerid
	Dim recordnumber
	Dim sqlquerydelete
	Dim targetFolder
	Dim retain
	
	
	targetFolder = Wscript.Arguments(0) 
	customerid = WScript.Arguments(1)	
	numdays = WScript.Arguments(2)
	if inStr(targetFolder,"\") then		
		If (IsNumeric(numdays)) Then		
		
		    If (Wscript.Arguments.Count = 3) Then
			retain="false"
			Wscript.echo "Proceeding with export to " & targetFolder
			Wscript.echo ""
			Wscript.echo "For customer ID " & customerid
			Wscript.echo ""
			Wscript.echo "Exporting then deleting logs older than " & numdays & " days"		
		    else		    
			retain = WScript.Arguments(3)
			    If (retain = "retain") Then
				retain = "true"
				Wscript.echo "Proceeding with export to " & targetFolder
				Wscript.echo ""
				Wscript.echo "For customer ID " & customerid
				Wscript.echo ""
				Wscript.echo "Exporting but retaining logs older than " & numdays & " days"
			    else
				Wscript.echo "Invalid input - you entered an invalid fourth argument. To retain logs, just enter 'retain'"				
				retain = "off"
			    end If		    
		    End If
		Else
			numdays = 9999
			Wscript.Echo "Error - your third argument must be the number of days to retain logs."
		End If

	else
		Wscript.echo "Invalid input. You need to enter a target export folder as your first argument. "
	end if


	if ((numdays > -1) and (numdays < 1000)) then
		if  ((retain="true") OR (retain="false")) then			
			source = Array()
			SQLStatement = Array()
			displayName = Array()
			agentGuid = Array()
			machName = Array()
			groupName = Array()
			logType = Array()
			eventType = Array()
			eventTime = Array()
			ApplicationName = Array()
			EventCategory = Array()
			eventId = Array()
			username = Array()
			computerName = Array()
			message = Array()
			partitionId = Array()
			eventlogdate = Array()
			orgId = Array()
			orgIdName = Array()
			EventLogTypeId = Array()
			category = Array()	
			recordnumber = Array()

			starttime = FormatDateTime(Now) 
			Set objFSO = CreateObject("Scripting.FileSystemObject")
			Set objFSO2 = CreateObject("Scripting.FileSystemObject")
			Set wShell = CreateObject("WScript.Shell")
			'Detect the path to the Kaseya folder. This method is pretty reliable. 
			kfolderkey = wShell.RegRead("HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\Kserver.exe\ImagePath")
			'The result returns an actual file name for the Kaseya server service. Strip it out to just get the install folder.
			kfolder = Left(kfolderkey,Len(kfolderkey) -19 )
			'Get the SQL database connection string.
			connrun = wShell.Run(kfolder & "webpages\install\iisinit.exe " & kfolder & "webpages\install\connstr.txt vsaConnStr",0,true)
			Set connstrfile = objFSO.OpenTextFile(kfolder & "webpages\install\connstr.txt")
			connstr = connstrfile.ReadAll
			connstrfile.close
			objFSO.DeleteFile (kfolder & "webpages\install\connstr.txt")
			set dbcn = Wscript.CreateObject("ADODB.Connection")
			dbcn.ConnectionString = connstr
			dbcn.Open

			'Detect if we are able to connect to the database.
			If dbcn.State=1 Then
				Wscript.echo " *** Connected to Kaseya database ***"
				sqlquery = "SET NOCOUNT ON	" &_
				"	DECLARE " &_
				"		@QueryStart DATETIME, " &_
				"		@QueryEnd DATETIME, " &_
				"		@EventLogTableName NVARCHAR(30),	" &_
				"		@selectedDate NVARCHAR(30),	" &_
				"		@collectedDate NVARCHAR(30),	" &_
				"		@selectionLimit INT, " &_
				"		@CurrentDateCount INT, " &_
				"		@EndRange NVARCHAR(40), " &_
				"		@StartRange NVARCHAR(40),	" &_	
				"		@finished bit,	" &_	
				"		@dt date,	" &_
				"		@numDays int	" &_
				"		SET @finished = 0	" &_
				"		set @dt = Convert(DATETIME,0)		" &_
				"		declare @temp table (eventlogdate int) " &_				
				"	set @numDays = " & numdays &_
				"	SET @QueryEnd = DateAdd(Day, - @numDays, GetUTCDate()) " &_
				"	SET @QueryStart = @dt " &_
				"	DECLARE @loopEndRange DateTime, @loopStartRange DateTime " &_
				"	SET @loopEndRange = @QueryEnd " &_
				"	SET @loopStartRange = @QueryStart " &_
				"	WHILE @loopEndRange >= @loopStartRange " &_
				"	BEGIN	" &_
				"		SET @EventLogTableName = 'NTEventLog' + CONVERT(CHAR(8), @loopEndRange, 112) " &_
				"		SET @collectedDate = CONVERT(CHAR(8), @loopEndRange, 112) " &_
				"		IF @CurrentDateCount >= @SelectionLimit " &_
				"			goto ExitLabel		" &_
				"		IF (EXISTS (select * from sysobjects WITH(NOLOCK) where id = object_id(@EventLogTableName) and OBJECTPROPERTY(id, 'IsUserTable') = 1)) " &_
				"		BEGIN " &_
				"			insert into @temp (eventlogdate) values (@collectedDate) " &_
				"			SET @CurrentDateCount = @CurrentDateCount + @@ROWCOUNT " &_
				"		END		" &_
				"		IF @finished = 1 " &_
				"			goto ExitLabel " &_
				"		SET @loopEndRange = DATEADD(day, -1, @loopEndRange) " &_
				"	END " &_
				"	IF @finished = 1 " &_
				"	BEGIN " &_
				"		goto ExitLabel " &_
				"	END " &_
				"ExitLabel: " &_
				"select eventlogdate from @temp order by eventlogdate asc "
				Set dbq=CreateObject("ADODB.Recordset")
				Set dbq=dbcn.Execute(sqlquery)	
				set stdout = wscript.stdout 
				curr = -1
				totalexports =-1
				'Create the folders for Queries and NonQueries
				If Not objFSO.FolderExists(targetFolder) Then
					objFSO.CreateFolder (targetFolder)
				end If
				If Not dbq.EOF then
					dbq.MoveFirst
						Do Until dbq.eof				
							curr = curr + 1			
							ReDim Preserve eventlogdate(curr)
							eventlogdate(curr)  = dbq.Fields("eventlogdate")
							Wscript.echo ""
							Wscript.echo "******************************"
							Wscript.echo "*** Begin Exporting Event Logs For Date " & eventlogdate(curr) & " ***"
							Wscript.echo "******************************"
							curr2 = -1
							If IsEmpty(customerid) Then
								sqlquery = "select id as orgId, ref as orgIdName from kasadmin.org"	
							else

								sqlquery = "select id as orgId, ref as orgIdName from kasadmin.org where ref = " & chr(39) & customerid & chr(39)						
							end if					
							Set dbq2=CreateObject("ADODB.Recordset")
							Set dbq2=dbcn.Execute(sqlquery)					
							If Not dbq2.EOF then
								dbq2.MoveFirst
									Do Until dbq2.eof							
										curr2 = curr2 + 1
										ReDim Preserve orgId(curr2)
										ReDim Preserve orgIdName(curr2)							
										orgId(curr2) = dbq2.Fields("orgId")
										orgIdName(curr2) = dbq2.Fields("orgIdName")
										If Not objFSO.FolderExists(targetFolder & "\" & orgIdName(curr2)) Then
											objFSO.CreateFolder (targetFolder & "\" & orgIdName(curr2))
										end If
										Wscript.echo ""
										Wscript.echo "******************************"
										Wscript.echo "****	Begin exporting logs for org ID " & orgIdName(curr2) &"	****"
										Wscript.echo "******************************"
										curr3 = -1
										sqlquery = "select EventLogTypeId from eventlogtype where EventLogTypeId = 796450521 or EventLogTypeId = 1380569194 or EventLogTypeId = 1664713117"											
										Set dbq3=CreateObject("ADODB.Recordset")
										Set dbq3=dbcn.Execute(sqlquery)
										If Not dbq3.EOF then
											dbq3.MoveFirst
												Do Until dbq3.eof
													curr3 = curr3 + 1
													ReDim Preserve EventLogTypeId(curr3)
													EventLogTypeId(curr3) = dbq3.Fields("EventLogTypeId")											
													If EventLogTypeId(curr3)=796450521 then
														EventLogType = "APL"
													else 
														If EventLogTypeId(curr3)=1380569194 then
															EventLogType = "SYS"
														else 
															If EventLogTypeId(curr3)=1664713117 then
																EventLogType = "SEC"
															else 
																EventLogType = "IGNORED"
															End If
														End If

													End If
													If EventLogType <> "IGNORED" then
													Wscript.echo ""
													Wscript.echo "******************************"
													Wscript.echo "Begin Exporting  " & EventLogType & " logs for " & orgIdName(curr2) & " from " & eventlogdate(curr)
													Wscript.echo "******************************"
													Wscript.echo "."
														Set csvresult = objfso.OpenTextFile(targetFolder & "\" & orgIdName(curr2) & "\" & EventLogType & "-" & eventlogdate(curr) & ".csv", 8, True)
														curr4 = -1
														sqlquery = "select N.recordNumber, m1.groupName, m1.machName, m1.displayName, N.agentGuid, logType, message, eventTime, source, category,eventId, username,computerName,eventType FROM ntEventLog" &  eventlogdate(curr) & " N WITH(NOLOCK) join USERS U WITH (NOLOCK) ON U.AgentGuid = N.AgentGuid inner join machnametab m1 on m1.agentGuid=N.AgentGuid inner join machgroup m2 on m1.machGroupGuid=m2.machGroupGuid  where m2.orgFK = " & orgId(curr2) & " AND logType = " & EventLogTypeId(curr3) & " ORDER BY eventTime asc"	
														Set dbq4=CreateObject("ADODB.Recordset")
														Set dbq4=dbcn.Execute(sqlquery)
														If Not dbq4.EOF then
															dbq4.MoveFirst
																Do Until dbq4.eof
																	if (curr4 = -1) then
																	linetowrite = "machineName,groupName,MachineID_GroupName,LocalComputerName,LocalUsername,OrgID,EventLogCategory,EventLogType,EventSource,EventID,EventTime,EventContent"
																	csvresult.WriteLine linetowrite
																	end if
																	totalexports = totalexports +1
																	curr4 = curr4 + 1
																	ReDim Preserve recordNumber(curr4)
																	ReDim Preserve eventTime(curr4)
																	ReDim Preserve groupName(curr4)
																	ReDim Preserve machName(curr4)
																	ReDim Preserve displayName(curr4)
																	ReDim Preserve computerName(curr4)
																	ReDim Preserve logType(curr4)														
																	ReDim Preserve message(curr4)
																	ReDim Preserve eventType(curr4)
																	ReDim Preserve source(curr4)
																	ReDim Preserve category(curr4)
																	ReDim Preserve eventId(curr4)
																	ReDim Preserve username(curr4)	
																	ReDim Preserve eventTime(curr4)	

																	eventTime(curr4) = dbq4.Fields("eventTime")
																	groupName(curr4) = dbq4.Fields("groupName")
																	machName(curr4) = dbq4.Fields("machName")
																	displayName(curr4) = dbq4.Fields("displayName")
																	computerName(curr4) = dbq4.Fields("computerName")
																	logType(curr4) = dbq4.Fields("logType")														
																	message(curr4) = dbq4.Fields("message")
																	eventType(curr4) = dbq4.Fields("eventType")
																	source(curr4) = dbq4.Fields("source")
																	category(curr4) = dbq4.Fields("category")
																	eventId(curr4) = dbq4.Fields("eventId")
																	username(curr4) = dbq4.Fields("username")
																	eventTime(curr4) = dbq4.Fields("eventTime")
																	recordNumber(curr4) = dbq4.Fields("recordNumber")

																	If eventType(curr4)=1 then
																		eventTypeName = "ERROR"
																	else 
																		If eventType(curr4)=2 then
																			eventTypeName = "Warning"
																		else 
																			If eventType(curr4)=4 then
																				eventTypeName = "Information"
																			else 
																				If eventType(curr4)=8 then
																					eventTypeName = "Success"
																				else 
																					If eventType(curr4)=16 then
																						eventTypeName = "Failure Audit"
																					else 
																						If eventType(curr4)=256 then
																							eventTypeName = "Critical"
																						else 
																							If eventType(curr4)=512 then
																								eventTypeName = "Verbose"
																							else 
																								eventTypeName = "Unknown"
																							End If
																						End If
																					End If
																				End If
																			End If
																		End If
																	End If													
																	linetowrite = machName(curr4) & "," & groupName(curr4) & "," & displayName(curr4) & "," & computerName(curr4) & "," & username(curr4) & "," & orgIdName(curr2) & "," & EventLogType & "," & eventTypeName & "," & source(curr4) & "," & eventId(curr4) & "," & eventTime(curr4) & "," & chr(34) & message(curr4) & chr(34)
																	csvresult.WriteLine linetowrite		
																	if (retain = "false") then
																		Set dbq5=CreateObject("ADODB.Recordset")
																		sqlquerydelete = "delete from ntEventLog" &  eventlogdate(curr) & " where recordNumber = " & recordNumber(curr4)
																		Set dbq5=dbcn.Execute(sqlquerydelete)
																	end if																	
																	stdout.write "."
																dbq4.MoveNext
															loop
														End If													
														Set dbq5 = Nothing
														Set dbq4 = Nothing																										
														csvresult.Close
														Set csvfilesize = objFSO.GetFile(targetFolder & "\" & orgIdName(curr2) & "\" & EventLogType & "-" & eventlogdate(curr) & ".csv")
														if csvfilesize.Size = 0 then
														   objfso.DeleteFile(targetFolder & "\" & orgIdName(curr2) & "\" & EventLogType & "-" & eventlogdate(curr) & ".csv") 
														end if     
													End If

												dbq3.MoveNext
											loop
										End If
										Set dbq3 = Nothing									
									dbq2.MoveNext
									checkfolder = targetFolder & "\" & orgIdName(curr2)
									If objFSO.FolderExists(checkfolder) Then							
										Set objFolder = objFSO.GetFolder(checkfolder)							
										If objFolder.Files.Count = 0 And objFolder.SubFolders.Count = 0 Then
											 objFSO.DeleteFolder checkfolder,True
										End If								
									End If
									Set checkfolder = Nothing
								loop								
							End If
							Set dbq2 = Nothing

						dbq.MoveNext
					loop
				End If
			Set dbq = Nothing
			Set objfso = Nothing
			dbcn.Close
			Set dbcn = Nothing
			if (totalexports > 0) then 
				If IsEmpty(numdays) then
					Wscript.echo "******************************"
					Wscript.echo "Complete! Exported logs for a total of " & curr & " days between " & startdate & " and " & enddate
					Wscript.echo ""
					Wscript.echo "Total logs exported " & totalexports 				
					If IsEmpty(customerid) then
					else
						Wscript.echo "Export done for specific customer:"
						Wscript.echo ""
						Wscript.echo "* " & customerid & " *"
						Wscript.echo ""
					end If
					Wscript.echo "******************************"
				else 
					Wscript.echo ""
					Wscript.echo "******************************"
					Wscript.echo "Complete! Exported logs older than " & numdays & " days."
					Wscript.echo ""
					Wscript.echo "Total logs exported: " & totalexports
					If IsEmpty(customerid) then
					else
						Wscript.echo "Export done for specific customer:"
						Wscript.echo ""
						Wscript.echo "* " & customerid & " *"
						Wscript.echo ""
					end If
					Wscript.echo "******************************"
				end if
				endtime = FormatDateTime(Now) 
				Wscript.echo ""
				Wscript.echo "Resulting CSV is stored in :"
				Wscript.echo  ""
				Wscript.echo  targetFolder & "\"
				Wscript.echo  ""
				Wscript.echo  "Start Time: " & starttime
				Wscript.echo ""
				Wscript.echo "End Time: " & endtime
				Wscript.echo "******************************"
			else 

				Wscript.echo ""
				Wscript.echo "******************************"
				Wscript.echo ""
				Wscript.echo "No Logs Found for your selection :("
				Wscript.echo ""
				Wscript.echo "******************************"
				Wscript.echo ""
				If IsEmpty(numdays) then				
					Wscript.echo "******************************"
					Wscript.echo ""
					Wscript.echo "Attemped export of logs between " & startdate & " and " & enddate
					Wscript.echo ""
					If IsEmpty(customerid) then
					else
						Wscript.echo "Export done for specific customer:"
						Wscript.echo ""
						Wscript.echo "* " & customerid & " *"
						Wscript.echo ""
						if (curr2 = -1) then 
							Wscript.echo "Customer could not be found."
						end if
					end If
					Wscript.echo "******************************"
				else 
					Wscript.echo "******************************"
					Wscript.echo ""
					Wscript.echo "Attempted export of logs older than " & numdays & " days."
					Wscript.echo ""
					If IsEmpty(customerid) then
					else
						Wscript.echo "Export done for specific customer:"
						Wscript.echo ""
						Wscript.echo "* " & customerid & " *"
						Wscript.echo ""
						if (curr2 = -1) then 
							Wscript.echo "Customer could not be found."
						end if
					end If
					Wscript.echo "******************************"
				end if
			end if
			else
					Wscript.Echo "Error: Unable to connect to database!"
			End If
		else	
		Wscript.echo ""
		Wscript.echo "Retain value not correct."
		Wscript.echo ""
	end if
	else
	Wscript.echo ""
			Wscript.echo "Number of days input not valid."
		Wscript.echo ""
	end if
	else 
	WScript.Echo "*** Invalid Input ***"
	Wscript.Echo ""
	Wscript.Echo "You must enter a target folder as your first argument, target org ID as your second, and the number of days as your third."
	Wscript.Echo ""
	Wscript.Echo "You may also enter an optional fourth argument to retain logs (they are deleted by default). "
	Wscript.Echo ""
	Wscript.Echo "	Example: cscript dbexport.vbs c:\folderexport customerorgid 6 "
	Wscript.echo ""
	Wscript.Echo "  Example (for retention): cscript dbexport.vbs c:\folderexport customerorgid 6 retain"
	Wscript.echo ""
end if
