Hello Everyone,

Here is a tutorial on how to find out the fieldnames to use in Stored Queries and Data Partitions.

This is a task that any Service Desk Manager Administrator will be performing regularly.
Sometimes it isn't intuitive what the Object and Attributes of an on-screen field are just from the Field Label.

There are "rules of thumb" which can work very nicely, most of the time. Until they don't, and you spend time wondering "Why not? What is this field called?"

• Attribute names are the same as the field names.
  (Except when they aren't.)
• Objects and Attributes map to database Tables and Columns.
  (On occasion.)
• The product documentation "Technical Reference Guide" is the best source of how Objects, Attributes, Tables and Columns map.
  (Except when it has a misprint. It is pretty good most of the time, it must be said.)
• The "Active" state is always registered as "Active = 1".
  (Except when "del_flag = 0" is used.)
• The form you are looking at with Field Label "XYZ" refers to the same field as another form with Field Label "XYZ."
  (Optimists see the best that there is in the world. )
• And so on!

To avoid these frustrations, it is often best to "look at the data" and see exactly what is being used on the system.

This tutorial has been built using Service Desk Manager 12.9 screenshots, but is applicable to all prior versions on all platforms.

__________________________________________________________________________

Tutorial - Identifying Objects and Attributes for use in Queries

The main "sources of truth" for this purpose are:

• Identify web forms which return the data in the form you want.
• "View Source" of the HTMPL web page to view the objects and attributes.
• Use "bop_sinfo" to confirm location of underlying data.

Note that there are many ways of performing this procedure and shortcuts that may be used. The advantage of this method is the cross-checking against the underlying data. It is not the only way though, and you may modify it to suit.

Scenario:

Construct a Stored Query that returns all Active Incidents with an Incident Area of "Software.Environment".

Method:

1) Identify a web form which shows the data that we want. Preferably in the form that we want - it is worth hunting around through a few forms

In this case, we've already identified that the "Incident Detail" form contains a field with label "Incident Area" that contains the data in the exact form we wish to query on. That is, it is in easily readable text and not expressed as an "ID" or as a cross-reference or as an abbreviation.

The data that we wish to check is "Software.Environment".
Always begin in the form that contains the main information that we want to display - in this case, Incidents. It is this that will be used to build the Stored Query most likely.

2) We need to identify the Attribute that is serving the field "Incident Area." This is done by "View Source."

• On Internet Explorer, "Cntrl + right-click" anywhere on form and choose "View Source."
• On Chrome and Firefox, position mouse next to the "Incident Area" field, "Cntrl + right-click" and choose "View frame source."

Screenshots will be given for Internet Explorer, but Chrome and Firefox give similar results.

3) Identify the factory that is being used by searching for string "var propFactory". The factory will be quotes to the right. Here, as may be expected, it is "cr".
We'll use this information in a minute.
 

While in this form, search for the Field Label identified earlier of "Incident Area". Adding a suffix like so may prevent a false return being given:

• Incident Area","

The "Attribute" of "category" is displayed the right of the Field Label.

4) Go to a Service Desk Manager server and open a Command Prompt.
Note - instructions given here are for Windows, but are applicable to UNIX systems with minor changes to commands such as "FIND."

The bop_sinfo command will be used to identify the underlying objects and attributes.
Note that unlike the methods outlined in the introduction, the bop_sinfo command will display the actual objects and attributes are being used by Service Desk Manager.

Use the syntax: bop_sinfo -d <<factory>> | find " <<attribute>> ".
Use the factory and attribute identified from Step 3, namely "cr" and "category."
Tip: Padding the attribute with a leading and trailing space should restrict output to use the one attribute that matches.

• bop_sinfo -d cr | find " category "

Note: Modify or omit "FIND" command on UNIX systems.
Tip: See all attributes sorted in order with "bop_sinfo -d cr | sort".

We can see from the returned result that neither a "STRING" nor an "INTEGER" was returned. Instead, an "SREL" to another object "pcat" was returned. (It is linked by attribute "persistent_id.)
This indicates that the text string "Software.Environment" would NOT be found in this object "cr" against attribute "category." We must go to the "pcat" object and identify which attribute contains the plain text string "Software.Environment" that we wish to use in our "readable" query.

4) Return to the web interface and look for the data entry form that updates the data we wish to filter our query by. The Incident Area data is entered under the Administration tab, Service Desk, Incidents/Problems/Requests, Areas.
Note that in this example, there are sub-categories below "Software.Environment" that we are not interested in.
Open "Software.Environment."

5) Note that the plain text for "Software.Environment" is stored against the Field Labelled "Symbol."
Repeat the earlier steps to View Source. We wish to check which Object and Attribute "Symbol" is stored in, and whether it is in its own table or in a cross-referenced table.

6) Note the Factory again. Here it is "pcat", which matches to the output that we saw from the earlier bop_sinfo link.

Find the Attribute which matches to the Field Label. In this example, the additional field delimiters help to narrow down several returns of "Symbol" to just the one that is being displayed on the form:

• "Symbol","

We can see the Attribute "sym" to the right of the field name.

7) Return to the server command prompt and bop_sinfo. We will check that the "sym" table is not linked elsewhere:
Using the factory and attribute and building the bop_sinfo as before:

• bop_sinfo -d pcat | find " sym "

Here we see it is flagged as a "STRING." This indicates that the plain text of the Incident Area is stored in object "pcat" in attribute "sym."

Tip: Additional information - not needed for this procedure.
You can examine the data in the Table directly to confirm that it returns data as expected eg:

• C:\PROGRA~2\CA\SERVIC~1\temp>pdm_extract -f "select sym from Prob_Category where sym = 'Software.Environment'"

TABLE Prob_Category
        sym
        { "Software.Environment" }
Prob_Category
        rows:1

Note that you'll need to know the "Table Name" for pdm_extract, not the "Object Name." Here, the table is "Prob_Category and the Object is pcat. These mappings are contained in Appendix B of the Technical Reference Guide.

8) We now have everything that we need to construct our Stored Query. We have verified the Objects and Attributes and how they map to the fields seen in the web client.

• Type is "Request."
• Query is: category.sym IN \'Software.Environment\' and type = \'I\' and active = 1

Incidents were identified as being stored in factory "cr." All Incidents, Requests and Problems belong to the "cr" object. The "Request" on the Type dropdown is used for all of these. If this was not obvious from inspection of similar Stored Queries, then tracing through the bop_sinfo and the Technical Reference Guide should advise what Type is to be used.

We had prior knowledge that Incidents were specified by type "I" and that active Incidents are identified by "active = 1". Again, these settings are commonly used in other Stored Queries. However, if they were not known, then exactly the same procedure as above would be used to identify them.

9) The final Stored Query added to the Scoreboard. Tests would be done at this point to confirm that the expected data is correctly returned.
In this case, it is okay as other Active Incidents are not returned, including Incidents which are sub-categories under "Software.Environment", which is the behaviour wanted.

I hope that this tutorial is useful in helping to identify fields.

If you have any tips on building Stored Queries, please add them here.

Thanks, Kyle_R.

Kyle_R:

Tip: Additional information - not needed for this procedure.

You can examine the data in the Table directly to confirm that it returns data as expected eg:

• C:\PROGRA~2\CA\SERVIC~1\temp>pdm_extract -f "select sym from Prob_Category where sym = 'Software.Environment'"

TABLE Prob_Category
        sym
        { "Software.Environment" }
Prob_Category
        rows:1

Note that you'll need to know the "Table Name" for pdm_extract, not the "Object Name." Here, the table is "Prob_Category and the Object is pcat. These mappings are contained in Appendix B of the Technical Reference Guide.

Thanks Kyle for the illustrative instructions. These are the basic things SDM admins should know by heart and that rookies should learn among the first.

I would like to correct the way to solve the table name of the object. In my opinion it's far more convenient and quicker to use the bop_sinfo command with parameter -q, which gives us just what we wanted: the table name of the object. For example:

bop_sinfo -q pcat
   --> Factory pcat < Prob_Category >

I myself prefer to use (always) bop_sinfo command with parameters -fa or -fda, where parameter -f gives me the basic infos of the factory before the attribute infos. One line of this factory info is object's table name, labeled with header "Schema name". For example:

bop_sinfo -f pcat
   -->
    Factory pcat
      Rel Attr        = persistent_id
      Common Name     = sym
      Func Group      = call_mgr_reference
      Display Name    = Request Area
      Display Group   =
      REST Operations = CREATE READ UPDATE
      Schema Name     = Prob_Category
      DBMS Name       = prob_ctg
      Producers       = pcat

I hope somebody finds this helpful!

BR,
Antti

antti.pyykko:

Thanks Kyle for the illustrative instructions. These are the basic things SDM admins should know by heart and that rookies should learn among the first.

I would like to correct the way to solve the table name of the object. In my opinion it's far more convenient and quicker to use the bop_sinfo command with parameter -q, which gives us just what we wanted: the table name of the object. For example:

bop_sinfo -q pcat
   --> Factory pcat < Prob_Category >

I myself prefer to use (always) bop_sinfo command with parameters -fa or -fda, where parameter -f gives me the basic infos of the factory before the attribute infos. One line of this factory info is object's table name, labeled with header "Schema name". For example:

bop_sinfo -f pcat
   -->
    Factory pcat
      Rel Attr        = persistent_id
      Common Name     = sym
      Func Group      = call_mgr_reference
      Display Name    = Request Area
      Display Group   =
      REST Operations = CREATE READ UPDATE
      Schema Name     = Prob_Category
      DBMS Name       = prob_ctg
      Producers       = pcat

I hope somebody finds this helpful!

BR,
Antti

Thank you, Antti,

That's a great contribution. This obviates the need to go to the documentation, and keeps the procedure firmly of the realm of things that can be directly examined.

I admit that I put in the pdm_extract just as an "optional extra" to the core procedure.  I'm glad you provided the extra detail to bridge the gap.

Thanks!Kyle_R.
 

Kyle_R:

Hello Everyone,

Here is a tutorial on how to find out the fieldnames to use in Stored Queries and Data Partitions.

<<Snip!>>

I hope that this tutorial is useful in helping to identify fields.

If you have any tips on building Stored Queries, please add them here.

Thanks, Kyle_R.

Thanks for this great tip Kyle!

EDITS
Jan 1 , 2014. Chris. Original post.
May 2, 2014. Kyle. Trimmed the resend of the post, as rather long.

wow that is a wonderful piece of information