
Trending in Support: Do you query?
Posted by Betty Neumann
We want your feedback; do you use queries against the TSPS database? If so, are they helpful? What are your go to queries?
While Support does not write queries for customers, there have been some occasions where we have used queries which have been developed by our engineers to help pull data and details from the databases using within the TrueSight Presentation Server PostGres database. These queries have been used primarily for troubleshooting issues and if you haven not worked with Dinesh Goel or Prashant Joshi you are missing out, these gentleman are out go to guys for all things database.
Queries can be used to collect specific data and based on some of the cases open in the last month, there has been an increase in requests for details which may be a bit cumbersome to pull from the GUI or other sources. For example, there have been several cases raised about which policies are applied to which Agents, which thresholds have been set where, and requests of a similar nature. Could there be an easier way to get this information? How can this be done?
Through the database! Go directly to the source! It is quick and easy and will give you the details of what you need. The hardest part would be figuring out which tables store which information, which BMC could probably create a whole class on, but for the purpose of this post, we will keep it simple. While we do not recommend using queries for every request and Support will not write a query for you, we would like you to take this opportunity to share some of the queries which help you navigate the TSPS world.
I will share some of the queries Support has provided recently through case work with customers and engineers. These queries are not official, no further documentation is available, they are straight database queries - simple select statements to pull some of the more popular details out of the database. I would like to give special thanks to Technical Support Professionals Ellen Coleman and @Lee Foster for contributing some of the scripts they use for this post.
A few items to note when attempting TSPS scripts:
• Be sure you have a working backup of the TSPS base before you run anything – the queries are usually just select statements but we don’t want to take any chances
• These queries work with TrueSight 10.x and 11.x versions
• These queries can be run on Windows or UNIX/Linux machines – Note: On Linux, you must source s the .tmprofile in /usr/pw/pronto/bin before the scripts will work
• The psql script/exe lives in /opt/bmc/TrueSightPServer/truesightpserver/modules/pgsql/bin OR "%TRUESIGHTPSERVER_HOME%\truesightpserver\modules\pgsql\bin\" where -U <dbusername>-d <databasename> -c <command to be executed>
• I would recommend piping the output to a file for most of these queries
• Run these queries at your own convenience/risk and note that not all these queries have been tested against each version of the TSPS database
Example for Windows:
Go to %TRUESIGHTPSERVER_HOME%\truesightpserver\modules\pgsql\bin\" then run the command below from the command prompt
psql -U <dbUser> -d truesight -c "select a.patrolagentid,a.policiesconsidered,b.hostname from patrolagent_pcfgtl a left outer join patrolagent_details b on a.patrolagentid = b.patrolagentid order by b.patrolagentid"
Example for Linux
Source .tmprofile in /usr/pw/pronto/bin cd to /opt/bmc/TrueSightPServer/truesightpserver/modules/pgsql/bin
./psql -U <dbUser> -d truesight -c "select a.patrolagentid,a.policiesconsidered,b.hostname from patrolagent_pcfgtl a left outer join patrolagent_details b on a.patrolagentid = b.patrolagentid order by b.patrolagentid"
Query to list which policies are applied to which Agents
psql -U <dbUser> -d truesight -c "select a.patrolagentid,a.policiesconsidered,b.hostname from patrolagent_pcfgtl a left outer join patrolagent_details b on a.patrolagentid = b.patrolagentid order by b.patrolagentid"
Query to list active policies
psql -U <dbUser> -d truesight -c "select oname from pepolicy where lifecycleStage='production' and poltype_id='uuid:policyType:3' order by oname"
Query to list servers which use a particular policy
psql -U <dbUser> -d truesight -c "select hostname from patrolagent_details where lower(policiesapplied) like '%<POLICY_NAME>%' order by hostname"
Examples/Clarification of the command:
With “lower” enter the policy name in all lower case:
psql -U <dbUser> -d truesight -c "select hostname from patrolagent_details where lower(policiesapplied) like '%linuxosdefault%' order by hostname"
Or no “lower” and Policy name is case sensitive.
psql -U tspsuser -d truesight -c "select hostname from patrolagent_details where (policiesapplied) like '%LinuxOSDefault%' order by hostname"
Query to list policy names of a certain type
psql -U <dbUser> -d truesight -c "select oname from pepolicy where poltype_id = 'uuid:policyType:3'"
- 'uuid:policyType:3' are Monitoring policies
- 'uuid:policyType:4' are Staging policies
- 'uuid:policyType:5' are Blackout policies
Query to list polices per Patrol Agent
psql -U <dbUpser> -d truesight -c "select * from patrolagent_details" > /tmp/PA.txt - List of Policies per Patrol Agent
Query to pull monitored host data from the Agent
psql -U <dbUser> -d truesight -c "select dnsname from device where dnsname NOT IN (select trim(hostname) from patrolagent_details where hostname IS NOT NULL)" truesight
**Note: Make sure dnsname and hostname are both either short name or fqdn.
Query to view threshold details for a specific parameter or for the full table
----specific parameter.txt
psql -U <dbuser> -d truesight -c "select * from template_thresholds where appclass='SWAP' and paramname=' SWPSwapUsedPercent' and severity='CRITICAL' AND thresholdtype='ABSOLUTE' AND comparator='GT' AND tenant='bmcrealm'" > C:\specific.txt
----full_table.txt
psql -U <dbuser> -d truesight -c "select * from template_thresholds" > C:\full_table.txt
Query to view deployable packages
psql -U <dbuser> -d truesight -c "SELECT * from create_deployable_package_xml"
Query to get a list of all PATROL Agents with an ISN in a file on TSPS
psql -U <dbUser> -d <dbname> -c "select hostname, patrolagentport, proxyhostname from patrolagent_details pa, proxy_details proxy where pa.proxyid = proxy.proxyid) to <file_location_name> WITH Delimiter ','"
**Note <file_location_name> is the parameter to provide file having details of PATROL Agents with ISN . For e.g. "C:\tableOut.txt"
<dbUser> is the DB user that you provided during TrueSight Presentation Server installation
Query to get a list of PATROL Agents in a file
psql -U <dbUser> -d <dbname> -c "select serverid, moinstanceid,hostname from patrolagent_details)" > PatrolagentWithInstanceId.txt
Query to show proxy details
psql -U <dbuser> -d truesight -c "select * from proxy_details"
Query to show a list of devices
psql -U <dbuser> -d truesight -c "SELECT * FROM device"
Query to list user preferences
psql -U <dbUser> -d truesight -c "select * from user_preference" > c:\temp\user_preference.txt
Query to list Agents where policies failed to apply
psql -U <dbUser> -d <dbname> -c "select serverid, moinstanceid, hostname, policiesapplied,policieslastappliedtime,policiesfailedtoapply,policiesfailedtoapplytime from patrolagent_details" > PatrolAgentDump.txt
Query to list group details
psql -U <dbUser> -d <dbname> -c "select * from device)" > 'c:\temp\devicecontents.txt'
Or -c "select * from groupdesc" > 'c:\temp\groupdesc.txt'
Or -c "select * from grouprelationship" > 'c:\temp\grouprelationship.txt'
Or -c "select * from groupcontents" > 'c:\temp\groupcontents.txt'
TrueSight 11.3.01 is here!!!!!…
The BMC Assisted MIGration Offering, or AMIGO, is a program designed to assist our customers in planning and preparing for product upgrades from an older, to a newer supported version. By engaging with BMC Technical Support Analysts, you will be provided with materials containing guidelines and best practices to aid in compiling your own upgrade plan. An upgrade expert will then review your plan, and offer advice and suggestions to ensure success through proper planning and testing.
The AMIGO program consists of a Starter Phase and a Review Phase. Each phase is initiated by opening a support case, and ends when the case is closed.
In the Starter Phase, an AMIGO Starter case is opened. Reference material will be provided and a call with a Technical Support Analyst will take place to discuss the details of your upgrade, and address any questions you may have. The AMIGO Starter case will be closed, and the next step will be for you to prepare a documented upgrade plan.
In the Review Phase, an AMIGO Review case is opened preferably two weeks prior to a set upgrade date. A call will be scheduled with an upgrade expert to review your detailed plan, providing feedback and recommendations, along with answers to any outstanding questions. As needed, a follow up discussion with a Technical Support Analyst may take place for feedback after the upgrade is performed.
The AMIGO program includes:
» A “Question and Answer” session before you upgrade
» A review of your upgrade plan with Customer Support
» An upgrade checklist
» Helpful tips and tricks for upgrade success from previous customer upgrades
» A follow-up session with Customer Support to let them know how it went. This will help BMC to enhance the process.
To get started, please review the details here:
Then open a BMC Support issue containing your environment information (product, version, OS, etc.) and the planned date of the installation, if known. We will contact you promptly, and work with you to ensure a successful and timely outcome.
New Knowledge Added over the last month:
000162720 Event shows incorrect status in TSPS 11.3.01 console but when looking at the event details the correct status is shown
000162570 The 'Allow Infrastructure Management PATROL repository management' permission used in the Unified Administrator role does not work properly in TrueSight 10.7
000162567 Deployment of TrueSight Presentation server packages fail when using HTTP vs HTTPS
000163287 Can you change Patrol Agent passwords via TSPS?
000163086 How to sync all connected Patrol Agents en masse
000162923 How to obtain from the TSPS database a list of PATROL Agents that have a specific infrastructure policy applied
000162957 Incorrect calculation for the SLO in TrueSight for the last month
000163153 When configuring TrueSight Infrastructure Management email2event functionality, how many unique mailbox configurations are allowed
000163107 How does one change the RSSO hostname as configured in the TrueSight Presentation Server 11. x configuration screen
000162935 Unable to import the solutions/KMs into the TrueSight Presentation Server 10.x, 11.x repository - Java.io.IOException: Unable to delete file: /tmp/bmc_products/install.ctl seen in the session.stdout log
Comments