VBSCRIPT Format CSV Columns

I don't think you can format a csv file, it is a simple text file. Within Excel you can apply formatting but I don't think it is stored in the file it is only temporary as long as excel is open.

CSV is nothing else than a plain text format with well-defined delimiters for values and rows. There is no formatting feature, nor colour.

HTML reports can contain colour. However, since you want to have that in Excel anyway, why not storing as Excel file?

Heres a thought, I dont have office installed on the server however I think this could be converted via VBS (Not sure how), once completed could this formatting be done? Once I receive the file on my workstation where I do have office installed I could open it there.

You can use vbs to automate excel to format the csv and then save it as an excel file.

Well it needs to all be done on the server, the issue is the file is emailed out, couldnt we instead build an excel spreadsheet (like HTML), accomplishing all the above based on the file it has in CSV format as a template?

I don't follow. If you need to output a formatted report from a server without Excel, you will have to create a html file with the data and formatting.

Well, here is the full script, if there is an easier way let me know:

On error resume next
Dim data
Dim recordCount
Dim regEx
Set regEx = new RegExp
regEx.Pattern = "\r|\n|,|"""

Set obj = CreateObject("Scripting.FileSystemObject")
obj.DeleteFile("C:\usermgt\userreport.csv")

Set con = CreateObject("ADODB.Connection")
con.ConnectionString = "Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Data Source=vdpsql08r2\logon;Initial Catalog=Users"
con.Open
strQry = "SELECT * FROM LOGONS (NOLOCK) WHERE DISABLED='Y' AND LegalHold='N' AND DisabledDays>61"
set data = con.execute(strQry)

Set filsSysObj = CreateObject("Scripting.FileSystemObject")    
Set csvFile = filsSysObj.OpenTextFile("C:\usermgt\userreport.csv", 8, True)

recordCount = data.Fields.Count      

do until data.EOF
Separator = ""
for i = 0 to data.Fields.Count - 1
Column = data.Fields( i ).Value & ""
if regEx.Test( Column ) then
Column = """" & Replace( Column, """", """""" ) & """"
end if
csvFile.Write Separator & Column
Separator = ","
next
csvFile.Write vbNewLine
data.MoveNext
loop

Dim MyDate
MyDate = Replace(Date, "/", "-")
Set objFSO = CreateObject("Scripting.FileSystemObject")
strCSV = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "userreport.csv"
strNewCSV = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "\Reports\ADUsers_"& mydate & ".csv"
Set objInput = objFSO.OpenTextFile(strCSV, 1, False)
Set objOutput = objFSO.CreateTextFile(strNewCSV, True)
objOutput.WriteLine "RecNumber,DC Auth,Station,LastLogon,samAccountName,LegalHold,Disabled,DisabledBy,DisabledDays,DaysLastLogon,DisabledDate,Action"
objOutput.Write objInput.ReadAll
objOutput.Close
objInput.Close

Set objEmail = CreateObject("CDO.Message")

objEmail.From = "usermanagement@domain.com"
objEmail.To = "email@address.com"
objEmail.Subject = "AD Users Report for "& mydate &""
objEmail.Textbody = "Attached is the report showing who has been disabled 90 days and not on legal hold. " &_
            + "Users contained in this report are either Disabled and within 30 days of deletion OR " &_
            + "Have not logged into the network for at least 45 days and should be looked into. "& vbcrlf &" " &_
            + " "& vbcrlf &" " &_
            + "Users highlighted in Yellow are within 7 Days of being deleted. "& vbcrlf &" " &_
            + "Users highlighted in Red are within 48 hours of being deleted. " & vbcrlf &" " &_
            + " "
objEmail.AddAttachment "C:\usermgt\Reports\ADUsers_"& mydate & ".csv"

objEmail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = _
        "ip address"
objEmail.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update

objEmail.Send

Agree. You cannot create full featured Excel files from VBS without using Excel per Automation, requiring it to be installed. You can create Excel sheets as database tables without (the drivers are installed with 32bit ODBC on Windows), but that still does not allow for formatting/colouring.
If you need a server-based solution without having Excel installed, HTML seems to be the only available choice.

From a birds eye the code seems fine, if it does what you want use it.

The code works I just need to find a way to format this out before it gets emailed is all...

HTML.

ok, can we work this code to spit out HTML and accomplish the above? :)

Thank you!

We have several issues with the SQL used, the most important one is that we cannot know the field names and meanings. All we have is

SELECT * FROM LOGONS (NOLOCK) WHERE DISABLED='Y' AND LegalHold='N' AND DisabledDays>61

Select allOpen in new window

so we know the table name and columns "Disabled", "LegalHold", "DisabledDays".

For proper formatting, we need the exact column names in sequence as to appear in the report. Further the column for the deletion date is required; that is also the column the SQL should sort for in the first place, so rows are in correct order ("red" ones on top = most early deletion date).

Here is the create table portion this should give a good outline of the tables involved.

CREATE TABLE [dbo].[logons](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [Computername] [varchar](255) NULL,
      [Station] [varchar](255) NULL,
      [Timestamp] [datetime] NULL,
      [Username] [varchar](255) NULL,
      [LegalHold] [varchar] (2) NULL,
      [Disabled] [varchar] (2) NULL,
      [DisabledBy] [varchar] (100) NULL,
      [DisabledDays] [int] NULL,
      [DaysLastLogon] [int] NULL,
      [DisabledDate] [datetime] NULL,
      [Action] [varchar](30) NULL,
PRIMARY KEY CLUSTERED
(
      [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[logons] ADD  DEFAULT (getdate()) FOR [Timestamp]
GO
ALTER TABLE [dbo].[logons] ADD  DEFAULT 'N' FOR [LegalHold]
GO
ALTER TABLE [dbo].[logons] ADD  DEFAULT 'N' FOR [Disabled]
GO
ALTER TABLE [dbo].[logons] ADD  DEFAULT 'Account Active' FOR [DisabledBy]
GO
ALTER TABLE [dbo].[logons] ADD  DEFAULT '0' FOR [DisabledDays]
GO
ALTER TABLE [dbo].[logons] ADD  DEFAULT '0' FOR [DaysLastLogon]
GO

Just an FYI the deletion Date itself should never come into play on this report, I delete the record from the DB after inserting it into a different table all together so once they are deleted they drop off the report.

Is the deletion time an direct offset of DisabledDate? Say, 90 days after disabling the account will get deleted?

You are correct, I created a VBS file called maintenance and once we hit 91st day, will insert into the deleted table and then delete the record.

The idea is once a user is disabled, 90 days goes by delete the user unless they are on legal hold for any reason.

Ok. so let's make the SELECT delivering anything we need, in correct order:

SELECT ComputerName, Station, Username, DisabledDays, DaysLastLogon
FROM LOGONS (NOLOCK)
WHERE DISABLED='Y' AND LegalHold='N' AND DisabledDays>61
order by DisabledDays desc

Select allOpen in new window

I'm not sure your "have not logged in within 45" rule of your example code is included, as you do not query for DaysLastLogon >= 45.

Hi, would it be easier to leave the SQL as is, and convert the CSV to HTML on the server task?  Powershell could do it with this command:
Import-Csv 'C:\Files\UserReport.csv' | ConvertTo-Html | Out-File 'C:\Files\UserReport.html'

A very simplistic example, but we could also use VBScript (or someone better versed in Powershell could do it with that as well) to read the CSV values line by line, and write the HTML formatting to a file.

Regards,

Rob.

Hi,

There is a modification to the query I was going to do to also pick accounts with logons in excess of 45 days since last logon.  The rest would be pointed out with formatting of the results as per my question above, I figured I would use this is my starting point?

As for the powershell script I think it would be best just to export the data into HTML the sort order could help.

Hi, as a start, here is some VBS code that will take your CSV, and format it to HTML, taking into account the different values of the DisabledDays column.

Depending on field data types, you may get an error or two, but I think it should be OK.

Regards,

Rob.

strCSVFile = "C:\Temp\Scripts\Report.csv"
strReport = "C:\Temp\Scripts\Report.html"

Const ForReading = 1
Const adInteger = 3
Const adDate = 7
Const adVarChar = 200
Const adFldIsNullable = 32
Set dtmDate = CreateObject("WbemScripting.SWbemDateTime")
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Create the recordset to hold the data before creating the report
Set rstData = CreateObject("ADOR.Recordset")
rstData.Fields.Append "RecNumber", adVarChar, 30
rstData.Fields.Append "DC Auth", adVarChar, 1000
rstData.Fields.Append "Station", adVarChar, 1000
rstData.Fields.Append "LastLogon", adDate, , adFldIsNullable
rstData.Fields.Append "samAccountName", adVarChar, 20
rstData.Fields.Append "LegalHold", adVarChar, 1000
rstData.Fields.Append "Disabled", adVarChar, 30
rstData.Fields.Append "DisabledBy", adVarChar, 30
rstData.Fields.Append "DisabledDays", adInteger
rstData.Fields.Append "DaysLastLogon", adDate, , adFldIsNullable
rstData.Fields.Append "DisabledDate", adDate, , adFldIsNullable
rstData.Fields.Append "Action", adVarChar, 1000
rstData.Open

' Enumerate the files in the log folder to find all .csv files
Set objCSVFile = objFSO.OpenTextFile(strCSVFile, ForReading, False)
If Not objCSVFile.AtEndOfStream Then
	' Skip the header line
	objCSVFile.SkipLine
	While Not objCSVFile.AtEndOfStream
		strLine = Trim(objCSVFile.ReadLine)
		If strLine <> "" Then
			If InStr(strLine, ",") > 0 Then
				'Write the data row to the recordset
				intField = 0
				rstData.AddNew
				For Each strField In Split(strLine, ",")
					Select Case intField
						Case 3, 9, 10
							' Date fields
							If strField = "" Then strField = Null
					End Select
					rstData.Fields(intField) = strField
					intField = intField + 1
				Next
				rstData.Update
			End If
		End If
	Wend
End If
objCSVFile.Close

'rstData.Sort = "Computer,Action,[Date Last Modified]"
If Not rstData.EOF Then
	rstData.MoveFirst
	
	Set objReport = objFSO.CreateTextFile(strReport, True)
	objReport.WriteLine String(0, vbTab) & "<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>"
	objReport.WriteLine String(0, vbTab) & "<html xmlns='http://www.w3.org/1999/xhtml'>"
	objReport.WriteLine String(0, vbTab) & "<head>"
	objReport.WriteLine String(1, vbTab) & "<title>Audit Report</title>"
	objReport.WriteLine String(1, vbTab) & "<style type='text/css'>"
	objReport.WriteLine String(2, vbTab) & "body"
	objReport.WriteLine String(2, vbTab) & "{"
	objReport.WriteLine String(3, vbTab) & "font-family: Arial,Helmet,Freesans,sans-serif;"
	objReport.WriteLine String(3, vbTab) & "font-size: 0.65em;"
	objReport.WriteLine String(3, vbTab) & "margin: 0px 0px 0px 0px;"
	objReport.WriteLine String(2, vbTab) & "}"
	objReport.WriteLine String(2, vbTab) & "td, th"
	objReport.WriteLine String(2, vbTab) & "{"
	objReport.WriteLine String(3, vbTab) & "padding: 1px 5px;"
	objReport.WriteLine String(2, vbTab) & "}"
	objReport.WriteLine String(1, vbTab) & "</style>"
	objReport.WriteLine String(0, vbTab) & "</head>"
	objReport.WriteLine String(0, vbTab) & "<body>"
	objReport.WriteLine String(1, vbTab) & "<table border='1' align='center' width='98%' style='border-collapse:collapse;'>"
	objReport.WriteLine String(2, vbTab) & "<tr>"
	objReport.WriteLine String(3, vbTab) & "<td colspan='12'>"
	objReport.WriteLine String(4, vbTab) & "<h2>Audit Report</h2>"
	objReport.WriteLine String(4, vbTab) & "<p>Generated: " & Now & "</p>"
	objReport.WriteLine String(4, vbTab) & "<p>All dates are displayed in the time local to each monitored system</p>"
	objReport.WriteLine String(3, vbTab) & "</td>"
	objReport.WriteLine String(2, vbTab) & "</tr>"

	If Not rstData.BOF Then rstData.MoveFirst
	' Write the header row
	objReport.WriteLine String(2, vbTab) & "<tr>"
	For intField = 0 To rstData.Fields.Count - 1
		objReport.WriteLine String(3, vbTab) & "<th>" & rstData.Fields(intField).Name & "</th>"
	Next
	objReport.WriteLine String(2, vbTab) & "</tr>"
	
	While Not rstData.EOF
		' Write the data
		objReport.WriteLine String(2, vbTab) & "<tr>"
		For intField = 0 To rstData.Fields.Count - 1
			Select Case intField
				Case 8
					' DisabledDate field
					strField = rstData.Fields(intField)
					strColor = "white"
					If IsNumeric(strField) = True Then
						If CInt(strField) >= 83 And CInt(strField) <= 88 Then
							strColor = "yellow"
						ElseIf CInt(strField) >= 89 And CInt(strField) <=90 Then
							strColor = "red"
						End If
					End If
					objReport.WriteLine String(3, vbTab) & "<td align='center' style='background-color:" & strColor & "'>" & strField & "</td>"
				Case Else
					objReport.WriteLine String(3, vbTab) & "<td align='center'>" & rstData.Fields(intField) & "</td>"
			End Select
		Next
		objReport.WriteLine String(2, vbTab) & "</tr>"
		rstData.MoveNext
	Wend
	rstData.Close
	objReport.WriteLine String(1, vbTab) & "</table>"
	objReport.WriteLine String(0, vbTab) & "</body>"
	objReport.WriteLine String(0, vbTab) & "</html>"
	objReport.Close
End If

MsgBox "Done"

Select allOpen in new window

ill test it and report back the results.

ok tried it, got this error, odd one...
error.png

Ok, so it will depend on data types and which fields are "allowed" to be empty.  Can you post a sample of your CSV, what the column data types are, and which might be empty sometimes?

Rob.

I think any fields should allow null or empty results, users who have not logged in for a while will have null disable dates and so on, other fields will also be null or empty too.

Sure, so until I get to change it, in the meantime you can fill in all fields with bogus data to see what it would look like.

Rob.

Line 43 Char 6 still kicks out the same error
This is the line:            rstData.Fields(intField) = strField

However I put On Error Resume Next at the top it finishes the conversation, should this be looked into or do you think we would be ok?

It should be fixed because you are probably missing rows of data in the HTML file. Can you identify which rows are missing, put those in a sample CSV file, and upload it here? You can change some data, I just think we'll either need to change a data type, or replace some odd characters.

Rob.

Here is a  sample. I think I was missing the DaysLastLogon Field.
test-data.csv

Thanks. I can't test it right now. I'll get to it later tonight.

Actually.... I had DaysLastLogon as a date. Can you change adDate on line 23 to adInteger? Remove the On Error Resume Next as well and we'll see what we get.

Rob.

ok

DOH

line 23 is already like that:
rstData.Fields.Append "DisabledDays", adInteger, , adFldIsNullable

wait wrong line! Testing now.