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:

https://docs.bmc.com/docs/TSOperations/113/amigo-checklist-for-truesight-operations-management-814553031.html

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