locked
Can anyone help me out. RRS feed

  • Question

  • Im trying to make this script to pull data from citrix to excel.

    It creates the excel workbook but it won't save the excel file. Is it also possible to tell me how it won't overwrite everytime i run this script and the above rows stay the same.

    <reference object="MetaFrameCOM.MetaFrameFarm"/>
            <script language="VBScript">
    			
    		Strnow = now
            Strdate = Day(strnow) & "-" & Month(strnow) & "-" & Year(strnow) & ".xls"
            strFileName = ("c:\Temp\" & Strdate)
    		
    		Set objExcel = CreateObject("Excel.Application")
            
    		
    		'FileCheck = Dir$(strFileName)
    		'objExcel.Workbooks.Open(strFileName)
    		If FileCheck = strFileName Then
        
    	'Workbook exists, open it
    	  objExcel.Workbooks.Open(strFileName)
    	  'objExcel.Worksheets(1)
    	  
    	  Else
    	   objExcel.Workbooks.Add
    	   'objExcel.Worksheets(1)
    		objExcel.Visible = True
            intRow = 2
    		
            objExcel.Cells(1, 1).Value = "Total Sessions"
    		objExcel.Cells(1, 2).Value = "Actieve Sessions"
    		objExcel.Cells(1, 3).Value = "Disconnected Sessions"
    		objExcel.Cells(1, 4).Value = "Total Users"
    		 End If
    
            	
            	Const statusOK 			= 0
    			Const statusWarning 	= 1
    			Const statusCritical 	= 2
    			Const statusUnknown 	= 3
    
    			If WScript.Arguments.Count <> 4 Then
    				WScript.Echo "Usage: cscript "& WScript.ScriptName& " <hostname> <application_name> <warning> <critical> "
    				WScript.Quit(statusUnknown)
    			End If
    			strComputer			= LCase(WScript.Arguments(0))
    			strSessionAppName	= LCase(WScript.Arguments(1))
    			intWarningTreshold	= Int(WScript.Arguments(2))
    			intCriticalTreshold	= Int(WScript.Arguments(3))
    			
            	intSessions			=0
            	intUnknown			=0
                intActive			=0
                intConnected		=0
                intConnecting		=0
                intShadowing		=0
                intDisconnected		=0
                intIdle				=0
                intListening		=0
                intResetting		=0
                intDown				=0
                intInit				=0
                intCount			=0
                userList			=""
                numberOfUniqueUsers	=0
                
                Dim theFarm, aSession, SessionState
    			
                SessionState = Array("Unknown", _
                                     "Active", _
                                     "Connected", _
                                     "Connecting", _
                                     "Shadowing", _
                                     "Disconnected", _
                                     "Idle", _
                                     "Listening", _
                                     "Resetting", _
                                     "Down", _
                                     "Init")
    
                Set theFarm = CreateObject("MetaFrameCOM.MetaFrameFarm")
                if Err.Number <> 0 Then
                    WScript.Echo "Can't create MetaFrameFarm object"
                    WScript.Echo "(" & Err.Number & ") " & Err.Description
                    WScript.Echo ""
                    WScript.Quit Err.Number
                End if
    
                theFarm.Initialize(MetaFrameWinFarmObject)
                if Err.Number <> 0 Then
                    WScript.Echo "Can't  Initialize MetaFrameFarm object"
                    WScript.Echo "(" & Err.Number & ") " & Err.Description
                    WScript.Echo ""
                    WScript.Quit Err.Number
                End if
    
                If theFarm.WinFarmObject.IsCitrixAdministrator = 0 then
                    WScript.Echo "You must be a Citrix admin to run this script" 
                    WScript.Quit (statusUnknown)
                End If
    
                For Each aSession In theFarm.Sessions
    
                    If Err.Number <> 0 Then
                        WScript.Echo "Can't enumerate sessions"
                        WScript.Quit (statusUnknown)
                    End If
                    
                    
                    
                    If ((strComputer="all" Or strComputer=LCase(aSession.ServerName)) And (strSessionAppName="all" Or strSessionAppName=LCase(aSession.AppName)))  Then 
      			
    					intSessions=intSessions+1
    					userList=userList& ","& aSession.UserName
    					
    	             	Select Case (SessionState(aSession.SessionState))
    	             		Case "Unknown"
    	             			intUnknown=intUnknown+1
    	             		Case "Active"
    	             			intActive=intActive+1
    	             		Case "Connected"
    	             			intConnected=intConnected+1
    	             		Case "Connecting"
                				intConnecting=intConnected+1
                			Case "Shadowing"
                				intShadowing=intShadowing+1
                			Case "Disconnected"
                				intDisconnected=intDisconnected+1
                			Case "Idle"
                				intIdle=intIdle+1
                			Case "Listening"
                				intListening=intListening+1
                			Case "Resetting"	
                				intResetting=intResetting+1
                			Case "Down"	
                				intDown=intDown+1
                			Case "Init"	
                				intInit=intInit+1
                		End Select  
                	End If   			
    	                	
                Next
             	
             	If Not userList="" Then 
    	         	userList=Right(userList, Len(userList)-1)
    	            users=Split(userList, ",")
    	            uniqueUsers=distinctArray(users)
    	            numberOfUniqueUsers=UBound (uniqueUsers)+1
    	        Else
    	        	numberOfUniqueUsers=0
    	        End If 
             	     
                outputMessage=intSessions& " sesions - "& intActive& " active, "_
                	& intDisconnected& " disconnected. Number of users: "_
               		& numberOfUniqueUsers& ". | sessions_total="& intSessions& ";sessions_active="_
               		& intActive& ";sessions_disconnected="& intDisconnected& ";users="& numberOfUniqueUsers& ";"
                
                If intSessions >= intCriticalTreshold Then 
    				WScript.Echo "Critical: "& outputMessage
    				WScript.Quit (statusCritical)
    			End If
    
    			If (intWarningTreshold <= intSessions) And  (intSessions < intCriticalTreshold) Then 
    				WScript.Echo "Warning: "& outputMessage
    				WScript.Quit (statusWarning)
    			End If 
    			
    			If intSessions < intWarningTreshold Then
    
                    objExcel.Cells(intRow, 1).Value = intSessions
    				objExcel.Cells(intRow, 2).Value = intActive
    				objExcel.Cells(intRow, 3).Value = intDisconnected
    				objExcel.Cells(intRow, 4).Value = numberOfUniqueUsers
    				intRow = intRow + 1
                    WScript.Quit (statusOK)
    			End If
    			
    			 If FileCheck = strFileName Then
    			  objExcel.Workbooks.Save
    			  
    			      Else
            'Save new workbook
            objExcel.Workbooks.SaveAs strFileName
                End If
    		      
    			          'Close Excel
            objExcel.Workbooks.Close
            objExcel.Application.Quit
            Set objExcel = Nothing
            Set objExcel.Workbooks = Nothing
            Set objExcel.Worksheets = Nothing
    			  
    			Function distinctArray(aArray)
    			  	maxItems = UBound( aArray)
    			  	For x = 0 To maxItems
    			   	If InStr(sNewList,(aArray(x) & ",")) <= 0 Then
    			    	sNewList = sNewList & aArray(x) & ","
    			   	End If
    			  	Next
    			  	distinctArray = Split (Left(sNewList,Len(sNewList)-1), ",")
    			End Function   
    			
            </script>

    • Moved by Carl Cai Monday, February 3, 2014 5:42 AM
    Friday, January 31, 2014 9:45 AM

Answers

  • You should probably post your question in the Technet Scripting Guys forum for vb script.

    Please BEWARE that I have NO EXPERIENCE and NO EXPERTISE and probably onset of DEMENTIA which may affect my answers! Also, I've been told by an expert, that when you post an image it clutters up the thread and mysteriously, over time, the link to the image will somehow become "unstable" or something to that effect. :) I can only surmise that is due to Global Warming of the threads.

    • Proposed as answer by KareninstructorMVP Friday, January 31, 2014 1:32 PM
    • Marked as answer by Just Karl Tuesday, February 11, 2014 11:16 PM
    Friday, January 31, 2014 10:08 AM