myk3 Posted September 24, 2010 Share Posted September 24, 2010 I am trying to create a script to poll from 2 separate databases.. One is a WSUS server (file type is .db)and the second is Symantec endpoint server. I currently have a .xls file that pulls from these servers, but i need to uninstall excel from these servers and need a way to pull the info from them still.. I am under the impression that the "built in" database for WSUS does not allow for remote connections. nay advice would help tremendously. Mike Link to comment Share on other sites More sharing options...
4Eyes Posted September 25, 2010 Share Posted September 25, 2010 (edited) myk3, Hey... one Mike has to help another. Some general suggesions follow. As you will often see here... what is it that you actually want to do? If you are trying to see if workstations are up to date SEP and MS updates wise, it may be possible to do it another way. Or are you trying to determine if the WSUS and SEP servers are up to date? If the WSUS doesn't allow remote connections then run a script on it directly and put the results somewhere that your main script can read it. 4Eyes Edited September 25, 2010 by 4Eyes Link to comment Share on other sites More sharing options...
myk3 Posted September 26, 2010 Author Share Posted September 26, 2010 Currently the .xls SQL statements we have pull information from he two server such as how many users have connected within the last 30 days, virus alerts, etc.. Is there any good tutorials on how to make an ODBC connection? Mike Link to comment Share on other sites More sharing options...
myk3 Posted September 29, 2010 Author Share Posted September 29, 2010 Well I have just finished the code.. Everything looks and works well.. here it is for anyone who is interested.. #include <date.au3> ;include the prebuilt date / time functions into the script if FileExists(@WorkingDir &"\Output\") = 0 then ;Check to see if "ouput" dir is available if not create it DirCreate(@WorkingDir &"\Output\") endif If FileExists(@WorkingDir &"\Output\*.csv") Then ;Check to see if there are any "CSV" files in the "Output" dir.. if so delete them FileDelete(@WorkingDir &"\Output\*.csv") endif _query("AV A","D434956B8F51FF1501B0D990699E33DA") ;Call functions and pass parameters to pass to the function 1st is $CSV 2nd is $DBGroupID _query("AV B","34EFBA208F51FF150062DE7B22BFE412") _query("AV V","108D72528F51FF15005E305FEF5DB2AF") _query("AV I","28C2AC218F51FF1500CC823746BE4E5E") _query("AV Unknown","1F9C4E678F51FF1501CB4A6A5C6BC2DC") exit ;exit the script Func _query($CSV, $DBGroupID) ;function to query database and output findings to a csv file. $conn = ObjCreate("ADODB.Connection") ;create ADODB connection $DSN ="SymantecEndpointSecurityDSN" ;ODBC connection driver found in Windows ODBC connections $conn.open($DSN) ;open the connection with the driver $rs = ObjCreate("ADODB.RecordSet") ;create a record set to store the query results in $sql = ("SQL STATEMENT HERE") ;^ SQL statement to be used later in the function $rs.open($sql, $conn) ;Query the open connection with the SQL statement $file = fileopen (@WorkingDir &"\Output\" & $CSV &".csv",2) ;create a file in the working dir \output and save it to a CSV file for later editing in Excel FileWriteLine($file,"As of "&_NowDate()&" "& _NowTime(4)) ;add the current date / time to the first line of the CSV file if $rs.eof Then ;test to see if there is no data in the record set if it is empty write "no data..." to 2nd line of CSV FileWriteLine($file,"No Data at This Time") Else Do ;if the recordset does have data do the next items FileWriteline($file, $rs.fields("Host Name").value & "," & $rs.fields("Workgroup").value & "," & $rs.fields("Last Logged On User").value & "," & $rs.fields("Location").value) ;^ write data from recordset into the CSV file based on the SQL query $rs.MoveNext ;move to the next record in the recordset until $rs.eof ;continue the DO loop until all records are sent to the CSV FileClose($file) ;close the open CSV file EndIf ;end the if statement $rs.close ;cclose the current recordset $conn.close ;close the current connection EndFunc ;end function Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now