Action to export CCPulse-stats to file or DB

system · May 25, 2007, 12:43pm

Hi.

Is it possible to define an action where a certain stat-value could be exportet to a file or db from CCPulse? E.g every minute a certain statvalue is written to a file (which again could be exported via ftp or something)

We need a quick-fix for this, so I guess it would be easy to implement if possible in CCPulse.

RunarM

vivek · May 25, 2007, 4:07pm

yes you can do that..

I have done similar setup in our company.

Basically I send Service Level to Oracle db. Whenever SL changes in CCpulse, threshold will trigger a script and update SL table in the DB.

You need to use VB Script code to do this…
Let me know if you need help.

vivek · May 25, 2007, 4:22pm

To make your life easy… I have put a sample script.

Create a new Threshold and past the following script in it.

Action script doesn’t need to have anything special, you can just change the color.

if Threshold.StatValue >= 0 then

 On Error Resume Next
 Skill_Name="Employee_Phone"

 Connection_String = "driver={Microsoft ODBC for Oracle};SERVER=<dbsid>;UID=<dbid>;PWD=<dbpassword>;"     
 
 Set conn = CreateObject("ADODB.Connection")
 conn.Open Connection_String
 set rs = CreateObject("adodb.recordset")

 sql2="update service_level_table set service_level='"&Threshold.StatValue&"' where skill_name='"&Skill_Name&"'"
 rs = conn.Execute(sql2)

 Set rs = nothing
 conn.close
 set conn = nothing

 Threshold.Result = true

end if

cavagnaro · May 25, 2007, 7:45pm

Sorry to ask, but in which scenario you will want to do this? Why data on the DataMart DB is not enought?

Just curious ;D

mark · May 25, 2007, 7:48pm

cavagnaro - you could then build a softwallboard which can be displayed on desktops.

That is one scenario.

vivek · May 25, 2007, 7:52pm

Mark is right.

Actually genesys recommended way is use GIS.
But you need license for GIS and…etc. but in this way you can do it in house.
Datamart and other stuffs are for historical, this is for real time.

cavagnaro · May 25, 2007, 8:27pm

Very good example
Thanks

system · May 29, 2007, 8:27am

Thanks a lot!

The scenario is actually what mentioned; we need realtime-statistics for displaying on a webpage. Actually, we have a GIS-license, but this is a short-term solution until we get it up running.

system · May 29, 2007, 8:37am

Runar, have you tried Quest wallboard server? I got it from this forum and because it is using an active X component which can be plugged into a webpage, we now have our wallboard functionality totally covered by it.

Jeff

mark · May 29, 2007, 9:08am

Shockingly I never kept track of what was happening with Quest!
How easy is it to implement the plugin to a webpage?

Mark

system · May 29, 2007, 9:30am

No, I have not actually, but that could maybe work from what i read. Couldnt find a link or email?

Marpos · February 25, 2016, 4:34pm

Hi all, i ve tried to use Mark’s code but it doesnt works for me

if Threshold.StatValue >= 3 then

 On Error Resume Next

 Connection_String = "driver={Microsoft ODBC for Oracle};SERVER=GTRIT;UID=fioritom;PWD=a1b2c3d4;" 
 
 Set conn = CreateObject("ADODB.Connection")
 conn.Open Connection_String
 set rs = CreateObject("adodb.recordset")

 sql2="Insert Into GTRIT.dbo.test (StatGuardado) Values ('"&Threshold.StatValue&"') "
 rs = conn.Execute(sql2)

 Set rs = nothing
 conn.close
 set conn = nothing

 Threshold.Result = true

end if

Ive tried to write servername as “2PEC0043\GTRIT” (Local pc name is 2PEC0043) and also “.\GTRIT” but it doesnt work either. Im using CCPulse on the same PC where i got the server. I use MS SQL Server 2008 and CCPulse+ 7.5.

Anyone have and idea ? Thanks !

cavagnaro · February 25, 2016, 4:35pm

Wait what?
If you have MSSQL then why use an Oracle driver??

Marpos · February 25, 2016, 5:07pm

OMG, my bad. But used this one already and is the same:

Connection_String = “driver={SQL Server Native Client 10.0};SERVER=2PEC0043\GTRIT;DATABASE=GTRIT;UID=fioritom;PWD=a1b2c3d4;”

And also tried “SQL Server”.

cavagnaro · February 25, 2016, 9:41pm

So ODBC libraries are installed?
Via ODBC on Control Panel, does it work?

Wombat · February 26, 2016, 11:02am

I’ve been working on something similar whilst we work on getting SDK installed. I’ve ended up with the following Excel Macro that I use to save all the active CCPulse windows in .HTML format. After that you can import these into whatever format you prefer for your final report.


Sub CCPulse_Save_Windows()

'should add a check that only one CCPulse instance is running

'set a variable as CCP_window so we can run a loop and count repeats
    Dim CCP_window As Integer
    CCP_window = 1

'move focus to CCPulse
    AppActivate "CCPulse+"
    
'run loop to save all 8 windows
'do until used in case of a problem so we don't get an infinite loop
    
    Do Until CCP_window = 10

'on 9th loop we want to go back to excel so we add this here
        If CCP_window = 9 Then GoTo RESET
    
'save windows 1-8 in the loop
        SendKeys "%W", True
        Delay 400
        SendKeys CCP_window, True
        Delay 400
        SendKeys "%F", True
        Delay 400
        SendKeys "H", True
        Delay 400
        SendKeys "%S", True
        Delay 400
        SendKeys "%Y", True
        Delay 400
        
'add one to the counter so next loop triggers next window
        CCP_window = CCP_window + 1
    
    Loop
    
'exit command in case we reach loop 10 and exit loop
'clear variable
    CCP_window = vbNullInteger
    
    Exit Sub

'on 9th loop we call this section of code instead of saving a window
RESET:

'move focus back to Excel
    'AppActivate ("Microsoft Excel")
    Workbooks("CCPulse Mailer Test.xlsm").Activate
    Delay 250
    Range("A1").Select
    Delay 250
    
'reset NUMLOCK on as this is turned off by a bug with multiple sendkey commands
    SendKeys "{NUMLOCK}", True
    
'clear variable
    CCP_window = vbNullInteger
    
End Sub

Notes:

will work with up to 8 windows which is the maximum that Pulse will show in a single list
if you have less than 8 windows adjust all the numbers in the ‘Do…Loop’ part of the code to match
the code re-writes the files over existing files (that’s what the ‘SendKeys “%Y”, True’ is for) so you’ll need to manually save them first for the script to work

Marpos · February 26, 2016, 12:12pm

Thanks a lot !, that helped a lot. I didnt know it was possible to test.

Ive found a way to do it and i share because maybe it could help another noob like me :P. My problem is at login time… i dnot know why but ill investigate a little more. THANKS A LOT AGAIN !

[i]"One way to create a quick test query in Windows via an ODBC connection is using the DQY format.

To achieve this, create a DQY file (e.g. test.dqy) containing the magic first two lines (XLODBC and 1) as below, followed by your ODBC connection string on the third line and your query on the fourth line (all on one line), e.g.:

XLODBC
1
Driver={Microsoft ODBC for Oracle};server=DB;uid=scott;pwd=tiger;
SELECT COUNT(1) n FROM emp
Then, if you open the file by double-clicking it, it will open in Excel and populate the worksheet with the results of the query."[/i]

Marpos · February 26, 2016, 12:15pm

Wombat post:16:

I’ve been working on something similar whilst we work on getting SDK installed. I’ve ended up with the following Excel Macro that I use to save all the active CCPulse windows in .HTML format. After that you can import these into whatever format you prefer for your final report.
Sub CCPulse_Save_Windows()

'should add a check that only one CCPulse instance is running

'set a variable as CCP_window so we can run a loop and count repeats
    Dim CCP_window As Integer
    CCP_window = 1

'move focus to CCPulse
    AppActivate "CCPulse+"
    
'run loop to save all 8 windows
'do until used in case of a problem so we don't get an infinite loop
    
    Do Until CCP_window = 10

'on 9th loop we want to go back to excel so we add this here
        If CCP_window = 9 Then GoTo RESET
    
'save windows 1-8 in the loop
        SendKeys "%W", True
        Delay 400
        SendKeys CCP_window, True
        Delay 400
        SendKeys "%F", True
        Delay 400
        SendKeys "H", True
        Delay 400
        SendKeys "%S", True
        Delay 400
        SendKeys "%Y", True
        Delay 400
        
'add one to the counter so next loop triggers next window
        CCP_window = CCP_window + 1
    
    Loop
    
'exit command in case we reach loop 10 and exit loop
'clear variable
    CCP_window = vbNullInteger
    
    Exit Sub

'on 9th loop we call this section of code instead of saving a window
RESET:

'move focus back to Excel
    'AppActivate ("Microsoft Excel")
    Workbooks("CCPulse Mailer Test.xlsm").Activate
    Delay 250
    Range("A1").Select
    Delay 250
    
'reset NUMLOCK on as this is turned off by a bug with multiple sendkey commands
    SendKeys "{NUMLOCK}", True
    
'clear variable
    CCP_window = vbNullInteger
    
End Sub
Notes:

will work with up to 8 windows which is the maximum that Pulse will show in a single list

if you have less than 8 windows adjust all the numbers in the ‘Do…Loop’ part of the code to match

the code re-writes the files over existing files (that’s what the ‘SendKeys “%Y”, True’ is for) so you’ll need to manually save them first for the script to work

Hey !, nice solution. If cant solve it using the ODBC connection ill use this one !

Thanks a lot !

Marpos · February 26, 2016, 2:35pm

Well, solved it. My problem was login system. Thanks !

Wombat · February 26, 2016, 4:04pm

'grats ;D

Just realised that I need to add the other sub for the delay timer in that script, will do that later