Focal Point
[SOLVED] retrieving the server and or SQL database name

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/3577020096

April 25, 2018, 01:16 PM
folkie
[SOLVED] retrieving the server and or SQL database name
If I want to find out who is running a focus procedure, I can use something like this:

-SET &&USERID=GETUSER('A66');
-TYPE Your username is &&USERID

Is there something equivalent to GETUSER to find out information about the reporting server and/or the connection to the SQL db or anything else like that?

Or, what are these things called so that I can search for them? Are these functions? Reserved words? System variables?

Thanks,
Mark

[going from v77 to v82; and wanting to verify I'm using the correct server]

This message has been edited. Last edited by: FP Mod Chuck,


WF 8.2.02
App Studio
DataMigrator
ReportCaster
Windows Server 2016
April 25, 2018, 01:29 PM
Francis Mariani
Mark, it is difficult to see how this would work: one report could access multiple database connections and/or multiple tables, the report could be using WebFOCUS code or SQL Passthru - how would you envision the system provide this info?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
April 25, 2018, 02:12 PM
folkie
Francis,

I was hoping it would give me the information similar to the way it can tell me which user is running a procedure.

Thanks,
Mark


WF 8.2.02
App Studio
DataMigrator
ReportCaster
Windows Server 2016
April 25, 2018, 02:24 PM
CoolGuy
folkie,

There is a place within the Admin Console that lets you configure your site.wfs profile with "custom settings". Therein, you can add some variables for your environment to make available to you and your users regarding their sessions.

These are as follows:

<SET> IBIMR_folder (pass)
<SET> IBIMR_fullpath (pass)
<SET> IBIMR_user (pass)
<SET> IBIMR_domain (pass)
<SET> MR_FULL_FEXNAME (pass)
<SET> MR_ITEM_HANDLE (pass)
<SET> IBIMR_memberof (pass)
<SET> FOCSECGROUPS (pass)
<SET> FOCSECGROUP (pass)
<SET> FOCSECUSER (pass)
<SET> userDescription (pass)


I do not know of one that lets you get the server a master file is hitting myself. You could create a fex of all your master files and assign the server they each belong to into an amper, then write some logic that checks which server belongs to which master that way I would think. Then add it here to your site.wfs. GETUSER() is a function however. Not something necessarily related to custom settings. There may be other functions similar, but I've not come across any that give a request's server it's hitting.

There's also a WHENCE command that shares which app folder a master is being read from. If your app folders are named after the servers and databases they come from, you could pull from that I would think.

Just some ideas and insights.

Good luck!


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
April 25, 2018, 03:30 PM
folkie
CoolGuy,

Okay. I'll see if I can figure out something there.

Thanks,
Mark


WF 8.2.02
App Studio
DataMigrator
ReportCaster
Windows Server 2016
April 25, 2018, 03:55 PM
Francis Mariani
The big difference between retrieving the User and retrieving the database connection is that ONE user is running the report you're retrieving the user for, whilst multiple database connections and/or tables could be used by that report.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
April 25, 2018, 04:02 PM
Francis Mariani
This documentation describes what Cool Guy posted.

WF 7.7.02 - WebFOCUS Security and Administration

Look at Manipulating WebFOCUS Variables > WebFOCUS Script Commands. This describes the available variables.

The doc does not solve your issue.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
April 25, 2018, 05:04 PM
eric.woerle
Folkie,

This sounds like something that you would want to look at resource analayzer for. You can monitor which DB is being used, procedure usage etc with that tool.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
April 25, 2018, 09:21 PM
David Briars
quote:
retrieving the server and or SQL database name

Here is an example for a MS SQL Server instance:
ENGINE SQLMSS SET DEFAULT_CONNECTION CON01
SQL SQLMSS PREPARE SQLOUT FOR
 SELECT
  @@servername   AS 'Server Name'
  ,@@servicename AS 'Instance Name'
  ,DB_NAME()     AS 'Database Name'
  ,HOST_NAME()   AS 'Host Name'
  ,@@VERSION     AS 'SQL Server Version'; 
END
-*
TABLE FILE SQLOUT
"MS SQL Server Details for Connection CON01"
PRINT *
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
 INCLUDE = jellybean_combo, $
 TYPE=DATA, COLUMN=N5, WRAP=.5, $
ENDSTYLE
END 

This message has been edited. Last edited by: David Briars,