Option Explicit '---------LogToFile Configuration--------- 'NOTE: Copy the configuration section To 'the beginning of an existing script. The 'values specified here must be set before 'calling the LogToFile sub. 'You can disable logging globally by 'setting the bEnableLogging option to false. dim bEnableLogging bEnableLogging = True 'Setting this to true will time stamp Each 'message that is logged to the log file 'with the current date and time. dim bIncludeDateStamp bIncludeDateStamp = True 'This will set the log file name to the 'current date and time. You can use this 'option to create incremental log files. dim bPrependDateStampInLogFileName bPrependDateStampInLogFileName = False 'Specify the log file location here. Path 'must contain a trailing backslash. If you 'would like to log to the same location as 'the currently running script, set this 'value to "relative" or uncomment out the 'line below. 'sLogFileLocation = "C:\LogFiles\" dim sLogFileLocation sLogFileLocation = "relative" 'Specify the log file name here. dim sLogFileName sLogFileName = "logtofiletest.txt" 'You can set whether or not you would like 'the script to append to an existing file, 'or if you would like it to overwrite 'existing copies. To overwrite set the 'sOverWriteORAppend variable to "overwrite" dim sOverWriteORAppend sOverWriteORAppend = "append" 'Here you can set the maximum number of 'lines you like to record. If the maximum 'is reached the beginning of the log file 'will be pruned. Setting this to a value 'of 0 will disable this function. dim vLogMaximumLines vLogMaximumLines = 0 'This is just like limiting the log file 'to a number of lines but limits by the 'total size of the log file. This value 'is in bytes. Setting this to 0 will 'disable this function. dim vLogMaximumSize vLogMaximumSize = 0 '-------END LogToFile Configuration------- Sub LogToFile(Message) 'LogToFile.vbs 10-18-07 'This script is provided under the Creative Commons license located 'at http://creativecommons.org/licenses/by-nc/2.5/ . It may not 'be used for commercial purposes with out the expressed written consent 'of NateRice.com dim oLogFSO, oLogShell, sLogFile, oLogFile If bEnableLogging = False Then Exit Sub Const ForReading = 1 Const ForWriting = 2 Const ForAppending = 8 Set oLogFSO = CreateObject("Scripting.FileSystemObject") If sLogFileLocation = "relative" Then Set oLogShell = CreateObject("Wscript.Shell") sLogFileLocation = oLogShell.CurrentDirectory & "\" Set oLogShell = Nothing End If If bPrependDateStampInLogFileName Then sNow = Replace(Replace(Now(),"/","-"),":",".") sLogFileName = sNow & " - " & sLogFileName bPrependDateStampInLogFileName = False End If sLogFile = sLogFileLocation & sLogFileName If sOverWriteORAppend = "overwrite" Then Set oLogFile = oLogFSO.OpenTextFile(sLogFile, ForWriting, True) sOverWriteORAppend = "append" Else Set oLogFile = oLogFSO.OpenTextFile(sLogFile, ForAppending, True) End If If bIncludeDateStamp Then Message = Now & " " & Message End If oLogFile.WriteLine(Message) oLogFile.Close If vLogMaximumLines > 0 Then Set oReadLogFile = oLogFSO.OpenTextFile(sLogFile, ForReading, True) sFileContents = oReadLogFile.ReadAll aFileContents = Split(sFileContents, vbCRLF) If Ubound(aFileContents) > vLogMaximumLines Then sFileContents = Replace(sFileContents, aFileContents(0) & _ vbCRLF, "", 1, Len(aFileContents(0) & vbCRLF)) Set oLogFile = oLogFSO.OpenTextFile(sLogFile, ForWriting, True) oLogFile.Write(sFileContents) oLogFile.Close End If oReadLogFile.Close End If If vLogMaximumSize > 0 Then Set oReadLogFile = oLogFSO.OpenTextFile(sLogFile, ForReading, True) sFileContents = oReadLogFile.ReadAll oReadLogFile.Close sFileContents = RightB(sFileContents, (vLogMaximumSize*2)) Set oLogFile = oLogFSO.OpenTextFile(sLogFile, ForWriting, True) oLogFile.Write(sFileContents) oLogFIle.Close End If oLogFSO = Null End Sub Dim objFSO, Path Dim objFileName, objTextFile, ReportFullPath Dim intMaxLength, i, sqlSubstr Const swDebug = false 'Connection string Dim strCon, strSQL 'strCon = "Driver={Microsoft ODBC for Oracle}; " & _ ' "CONNECTSTRING=(DESCRIPTION=" & _ ' "(ADDRESS=(PROTOCOL=TCP)" & _ ' "(HOST=123.123.123.123)(PORT=1521))" & _ ' "(CONNECT_DATA=(SERVICE_NAME=contentstore))); uid=user;pwd=password;" strCon = "Provider=SQLOLEDB.1;" & _ "Data Source=CognosServer;" & _ "Initial Catalog=ContentStore;" & _ "Trusted_Connection=yes;" Dim oCon: Set oCon = WScript.CreateObject("ADODB.Connection") Dim oRs: Set oRs = WScript.CreateObject("ADODB.Recordset") oCon.Open strCon 'ORACLE: strSQL = "select max(dbms_lob.getlength(cmobjprops7.spec)) FROM cmobjprops7" 'SQL Server: strSQL = "select max(datalength(cmobjprops7.spec)) FROM cmobjprops7 inner join cmobjects on cmobjects.cmid = cmobjprops7.cmid where classid = 10" Set oRs = oCon.Execute( strSQL ) intMaxLength = CInt(CDbl(oRs.Fields(0)) / 3000) oCon.Close Set oRs = Nothing For i = 1 to intMaxLength + 1 'ORACLE: sqlSubstr = sqlSubstr + ", nvl(dbms_lob.substr( cmobjprops7.spec,3000," & i * 3000 + 1 & "),' ') spec"&i 'SQL Server: sqlSubstr = sqlSubstr + ", coalesce(substring( cmobjprops7.spec," & i * 3000 + 1 & ",3000),' ') spec"&i Next 'SQL Server 'This uses a recursive query to avoid having to create a UDF on the server. It should be possible to create a similar query in Oracle. 'This will generate a list of all reports (classid 10), and loop through finding the parent objects. 'The results will then be filtered further, leaving only the root content and directory. Additional objects may be added by changing the 'filter from classid=10 to, for example, classid in (10, 37, 80), (10=report, 37=query, 80=analysis) select * from cmclasses to see the full list. strSQL = "WITH Recursive_CTE " & _ "AS" & _ "(" & _ "SELECT" & _ " cmobjectsParent.cmid " & _ " , cast('' as varchar(1000)) path" & _ " , cmobjnamesParent.name " & _ " , cmobjectsParent.pcmid" & _ " , 1 as RecursionLevel" & _ " , cmclasses.name className" & _ " , cmobjectsParent.classid classID" & _ " , 0 isUser " & _ " , cmobjectsParent.cmid reportCMID" & _ " FROM " & _ " cmobjnames cmobjnamesParent " & _ " inner join cmobjects cmobjectsParent on cmobjectsParent.cmid = cmobjnamesParent.cmid " & _ " inner join cmclasses on cmclasses.classid = cmobjectsParent.classid " & _ "WHERE " & _ "isdefault=1 " & _ "and cmobjectsParent.classid=10 " & _ " UNION ALL " & _ "SELECT " & _ " cmobjects.cmid " & _ " , cast(case when Recursive_CTE.isUser>1 or cmobjects.classID = 5 " & _ " then '' else '\' + cmobjnames.name end + Recursive_CTE.path as varchar(1000) ) path " & _ " , Recursive_CTE.name " & _ " , cmobjects.pcmid " & _ " , Recursive_CTE.RecursionLevel+ 1 as RecursionLevel " & _ " , cmclasses.name className" & _ " , cmobjects.classid classID" & _ " , Recursive_CTE.isUser + case when cmobjects.classID = 5 then cmobjects.cmid else 0 end isUser" & _ " , Recursive_CTE.reportCMID" & _ " FROM " & _ " cmobjnames " & _ " inner join cmobjects on cmobjects.cmid = cmobjnames.cmid " & _ " inner join cmclasses on cmclasses.classid = cmobjects.classid " & _ " INNER JOIN Recursive_CTE ON Recursive_CTE.pcmid = cmobjects.cmid " & _ "where cmobjnames.isdefault=1 " & _ "and cmobjects.cmid <>0 " & _ ") " & _ "SELECT " & _ " Recursive_CTE.name, " & _ " case when Recursive_CTE.isUser >0 then '\users\' + coalesce(cmobjprops33.name,'unknown or deleted') else '' end + Recursive_CTE.path " & _ sqlSubstr &_ " FROM " & _ " Recursive_CTE " & _ " inner join cmobjprops7 on cmobjprops7.cmid = Recursive_CTE.reportCMID " & _ " left outer join cmobjprops33 on cmobjprops33.cmid = Recursive_CTE.isUser " & _ "where " & _ "classID in (33,40)" LogToFile(strSQL) oCon.Open strCon Set oRs = oCon.Execute( strSQL ) dim spec While Not oRs.EOF Set objFSO = CreateObject("Scripting.FileSystemObject") Path = strCleanPath( oRs.Fields(1)) Path = "c:\temp\reports\" & Path CreateFolderTree(Path) ReportFullPath = Path & "\" & strClean(oRs.Fields(0).Value) ReportFullPath = left(ReportFullPath,255) objFileName = ReportFullPath & ".xml" Set objTextFile = objFSO.CreateTextFile (objFileName, True, True) For i = 2 to intMaxLength + 2 spec = spec + oRs.Fields(i).value Next objTextFile.WriteLine( spec ) objTextFile.close spec = empty LogToFile("Adding " + oRs.Fields(0).Value + " path: " + oRs.Fields(1).Value ) if len(oRs.Fields(intMaxLength+2).Value)=3000 Then LogToFile("Need to add a few more") END IF oRs.MoveNext Wend oCon.Close Set oRs = Nothing Set oCon = Nothing '-------------------- '- Create Folder Path '-------------------- Sub CreateFolderTree(strTempPath) If Not objFSO.FolderExists(objFSO.GetParentFolderName(strTempPath)) Then CreateFolderTree(objFSO.GetParentFolderName(strTempPath)) If Not objFSO.FolderExists(strTempPath) Then objFSO.CreateFolder(strTempPath) End Sub Function strClean (strtoclean) Dim objRegExp, outputStr Set objRegExp = New Regexp objRegExp.IgnoreCase = True objRegExp.Global = True objRegExp.Pattern = "[(?*"",\\<>&#~%{}+_.@:\/!;]+" outputStr = objRegExp.Replace(strtoclean, "-") objRegExp.Pattern = "\-+" outputStr = objRegExp.Replace(outputStr, "-") strClean = outputStr End Function Function strCleanPath (strtoclean) Dim objRegExp, outputStr Set objRegExp = New Regexp objRegExp.IgnoreCase = True objRegExp.Global = True objRegExp.Pattern = "[(?*"",<>&#~%{}+_.@:\/!;]+" outputStr = objRegExp.Replace(strtoclean, "-") objRegExp.Pattern = "\-+" outputStr = objRegExp.Replace(outputStr, "-") strCleanPath = outputStr End Function