Skip to main content

Procedures

dbo.spAddNodeCustColumn​

Add new custom property for object to NodeCust table.

Parameters​

NameTypeDescription

@intKindId

int

Property kind ID (tblKind.intKindId)

dbo.spAssetTreeNodeChilds​

Returns the object off-springs in the tree.

Parameters​

NameTypeDescription

@nodeId

int

dbo.spAssetTreeNodeChilds_Rights​

Returns the object off-springs in the tree, adheres to the permission in the object tree.

Parameters​

NameTypeDescription

@nodeId

int

@personId

int

dbo.spAssignLicenceItemToChildren​

Assigns the license item to historical upgrades as well.

Parameters​

NameTypeDescription

@LicHistItemId

int

dbo.spCalcNodeName​

Converts the tree object name according to the properties forming the object name.

Parameters​

NameTypeDescription

@nodeId

int

dbo.spCalcNodeNameAll​

Converts the names of all tree objects, the names of which are formed from properties.

Parameters​

dbo.spCalcNodeNameClass​

Converts the names of all tree objects, the names of which are formed from properties, according to the group ID.

Parameters​

NameTypeDescription

@TemplateNodeId

int

dbo.spCalcNodeNameList​

Parameters​

NameTypeDescription

@NodeIds

nvarchar(max)

dbo.spCalcNodeNameSubtree​

Converts the names of sub-tree objects, the names of which are formed from properties.

Parameters​

NameTypeDescription

@parentId

int

dbo.spCalcUnreadTicket​

Sets the flag of the ticket to read/unread by the person.

Parameters​

NameTypeDescription

@PersonId

int

@HdTicketId

int

@ActId

int

@ApprovalItemId

int

dbo.spCloseHdTicket​

Required values will be stored in tHdTicket after closing the ticket.

Parameters​

NameTypeDescription

@iHdTicketId

int

@iSolverPersonId

int

@bIgnoreRights

bit

dbo.spConvertPropertiesToDate​

Converts text properties of the specific kind to date type.

Parameters​

NameTypeDescription

@intKindId

int

@newKindName

nvarchar(255)

dbo.spConvertPropertiesToFloat​

Converts text properties of the specific kind to float type.

Parameters​

NameTypeDescription

@intKindId

int

@newKindName

nvarchar(255)

dbo.spCopySection​

Copies the @srcSectionId service and sets it as the child of the @dstParentSectionId service. The service can be copied including (@copyChildren) Subtree. The newly created service ID is available from the @newSectionId output parameter.

Parameters​

NameTypeDescription

@srcSectionId

int

@dstParentSectionId

int

@copyChildren

bit

@newSectionId

int

dbo.spCreateDetects​

Create scans according to parameters.

Parameters​

NameTypeDescription

@nodes

nvarchar(max)

List of computer Ids separated with commas.

@kindId

int

Scan type ID (1=HW, 2=SW).

@isManual

bit

Scan immediately.
Note: If the value is 1, the existing pending tickets are overwritten in order to accelerate them.

@methodId

int

Scan method ID. If it is NULL, the scan for this type is disabled.

@isSwFull

bit

Full SW scan.

@swInclude

nvarchar(255)

SW scan, include files.

@swExclude

nvarchar(255)

SW scan, skip files.

@period

int

Scan period (days).

@personId

int

Person ID. If it is NULL, use the system account.

dbo.spCreateDetectsBySetting​

Plans scans according to saved setting to a specific computer (or to all computers in the active registry).

Parameters​

NameTypeDescription

@nodes

nvarchar(max)

List of computer Ids separated with commas. If the parameter is NULL, detections are planned according to the saved setting to all computers in the active registry.

@isManual

bit

Scan immediately.
Note: If the value is 1, the existing pending tickets are overwritten in order to accelerate them.

@personId

int

Person ID. If it is NULL, use the system account.

@detectProfileId

int

Scan profile ID. If set, scans for selected computer nodes are scheduled according to this profile.
Note: If the value of @nodes is null, the parameter has no effect.

dbo.spCreateNodeFromTemplate​

Creates an object based of the object template definition.

Parameters​

NameTypeDescription

@templateClass

int

@name

nvarchar(255)

@target

int

@person

int

@skipGenerateFromSequence

bit

@computerSetId

int

@shouldLogPropValueChange

bit

dbo.spCustomColumn_Add​

Add new custom field.

Parameters​

NameTypeDescription

@sTable

nvarchar(255)

@sColumn

nvarchar(255)

@sTitle

nvarchar(255)

@mDescription

nvarchar(1024)

@nOrder

float

@bUseColumnValue

bit

@sType

nvarchar(255)

@nLength

int

@Multiline

bit

@Localize

bit

@RoleId

int

@Precision

int

dbo.spCustomColumn_Drop​

Removing custom fields.

Parameters​

NameTypeDescription

@sTable

nvarchar(255)

@sColumn

nvarchar(255)

dbo.spDropNodeCustColumn​

Drop custom property of object from tblKind and NodeCust table.

Parameters​

NameTypeDescription

@intKindId

int

Property kind ID (tblKind.intKindId)

dbo.spEvalFileRule​

Evaluation of one rule for files recognition.

Parameters​

NameTypeDescription

@FileImageId

int

dbo.spEvalRegRule​

Evaluation of the one rule for the recognition of registries.

Parameters​

NameTypeDescription

@RegImageId

int

dbo.spEvalSwDetection​

Evaluates SW scan based on patterns from the software products library.

Parameters​

NameTypeDescription

@DetectId

int

dbo.spGeneratePropValuesFromSequence​

Sets other values ​​for properties by numeric sequences.

Parameters​

NameTypeDescription

@NodeId

int

@person

int

@shouldLogPropValueChange

bit

dbo.spGetExternalServiceMetricId​

Returns ID of external the service metric.

Parameters​

NameTypeDescription

@externalServiceId

int

ID of the external service.

@metricName

nvarchar(max)

Service metric.

@feature

nvarchar(max)

Name of the feature.

@groupName

nvarchar(max)

Name of the group.

dbo.spGetNextNumFromIdSeq​

Increments numerical row definition and returns current definition. If uniqueness is breached, it automatically searches the next free number based on the row definition.

Parameters​

NameTypeDescription

@IdSeqId

int

@actualNum

nvarchar(255)

dbo.spHdSection_UpdateName​

Full service name update (@iHdSectionId) including path in service tree.

Parameters​

NameTypeDescription

@iHdSectionId

int

dbo.spHdSectionForRequesterSearch​

Searching for the Requester’s services. Returns a table of services matching the entered parameters arranged by the path in the tree. The procedure can be customized. In order to customize the procedure, create a new procedure named spHdSectionForRequesterSearch_Custom and assign a permission to run it to the "db_executor" group. For the proper functionality of ALVAO applications, the procedure must receive the same parameters as the original procedure and return the same columns.

Parameters​

NameTypeDescription

@sample

nvarchar(300)

@count

int

@personId

int

@requesterId

int

@isForMoveToService

bit

dbo.spHdTicketPassToSolver_ExceptionalSolverList​

Returns a list of exceptional solvers, used to assign tickets listed in table #temp1. The procedure can be customized. In order to customize the procedure, create a new procedure named spHdTicketPassToSolver_ExceptionalSolverList_SolverList_Custom and assign a permission to run it to the "db_executor" group. For the proper functionality of ALVAO applications, the procedure must receive the same parameters as the original procedure and return the same columns. The @iUserPersonId parameter, representing the signed in user, is not used in the standard procedure.

Parameters​

NameTypeDescription

@iUserPersonId

int

dbo.spHdTicketPassToSolver_SolverList​

Returns a list of solvers, used to assign tickets listed in table #temp1. The procedure can be customized. In order to customize the procedure, create a new procedure named spHdTicketPassToSolver_SolverList_Custom and assign a permission to run it to the "db_executor" group. For the proper functionality of ALVAO applications, the procedure must receive the same parameters as the original procedure and return the same columns. The @iUserPersonId parameter, representing the signed in user, is not used in the standard procedure.

Parameters​

NameTypeDescription

@iUserPersonId

int

dbo.spHdTicketPassToSolverInHdSection_ExceptionalSolverList​

Returns a list of exceptional solvers, used to assign the ticket when transitioning to the @iHdSectionId service.

Parameters​

NameTypeDescription

@iHdSectionId

int

dbo.spHdTicketPassToSolverInHdSection_SolverList​

Returns a list of solvers, used to assign the ticket when transitioning to the @iHdSectionId service.

Parameters​

NameTypeDescription

@iHdSectionId

int

dbo.spHdTicketRecalcFirstReaction​

Recomputes the solution of the first reaction for the ticket.

Parameters​

NameTypeDescription

@ticketId

int

dbo.spHdTicketRecalcNextActionDeadline​

Recomputes the target date of the current target for the ticket.

Parameters​

NameTypeDescription

@ticketId

int

dbo.spHdTicketResolve​

Switches the ticket to Resolved.

Parameters​

NameTypeDescription

@iHdTicketId

int

@iSolverPersonId

int

@bIgnoreRights

bit

@dResolved

datetime

dbo.spHdTicketSolverOpen​

Reopens the ticket by the solver.

Parameters​

NameTypeDescription

@iHdTicketId

int

@iSolverPersonId

int

dbo.spIncrementMetricValue​

Increments the service metric value by the incremental value.

Parameters​

NameTypeDescription

@metricId

int

ID of the service metric.

@value

int

Incremental value.

dbo.spInsertToOperatorServiceUsage​

A new record is inserted to the OperatorServiceUsage table or the LastUsed value is updated at the existing record.
Only 20 latest records are saved in the table for each user.

Parameters​

NameTypeDescription

@PersonId

int

ID of the main solver who used the given service while registering the ticket.

@HdSectionId

int

Used service ID while registering the ticket.

dbo.spInsertToPersonEmailUsage​

Inserts a new record into the PersonEmailUsage table.

Parameters​

NameTypeDescription

@UsedByPersonId

int

ID of the user who used the e-mail address.

@UsedEmail

varchar

Used e-mail address for sending the message.

dbo.spMakeKindUnique​

Update table tblKind and column txtName to be unique.

Parameters​

dbo.spMoveNode​

Moves the object in the object tree.

Parameters​

NameTypeDescription

@node

int

@target

int

@person

int

dbo.spMoveNodes​

Bulk transfer of objects.

Parameters​

NameTypeDescription

@nodeIds

nvarchar(max)

List of object IDs, separated by commas.

@destNodeId

int

ID of target object.

@personId

int

ID of the person who is performing the operation.

@dteHist

datetime

Date of transfer.

@retCode

int

Returned value in the form of a bit array:

  • 0 = operation was successful.
  • 1 = user is not authorized to transfer objects (role).
    Some objects were not transferred because:
  • 2 = the user is not authorized to carry out the transfer (object security).
  • 4 = the object has the attribute "immovable".
  • 8 = the transferred object is also the target object.
  • 16 = the target object is also a child object of one of the moved objects.
  • 32 = the transferred object is already a child object of the target object.
  • 64 = is moved to the Recycle Bin or Discarded assets and some objects have their own propertyIt is responsible for the assets together with a specific user

dbo.spNodeProperty​

Returns the list of object properties with values converted to string.

Parameters​

NameTypeDescription

@nodes

Node

Table with object IDs (tblNode.intNodeId) for which all their properties are returned. If empty, properties for all objects are returned.

@kindId

int

Specific property kind (tblKind.intKindId).

@inherited

bit

Whether to return inherited properties.

@localeId

int

dbo.spNotifyMacros​

Auxiliary procedure which inserts the values of the macros used for configuring notifications into the #T table.

Parameters​

NameTypeDescription

@iHdTicketId

int

dbo.spOpeningHoursCache_Update​

Updates the tOpeningHoursCache table for specific operating hours.

Parameters​

NameTypeDescription

@openingHoursId

int

ID of operating hours (tOpeningHours.iOpeningHoursId).

dbo.spPersonByPhoneNumber​

Finds the user based on telephone number.

Parameters​

NameTypeDescription

@caller

nvarchar(255)

dbo.spPersonSearch​

The procedure returns the list of persons according to the searched chain. The procedure can be customized. In order to customize the procedure, create a new procedure named spPersonSearch_Custom2 and assign a permission to run it to the "db_executor" group. For a proper functionality of ALVAO applications, the procedure must receive the same parameters as the original ones (see below) and return the same columns. The procedure returns the table containing the following columns:
PersonId - contains Id of the User found. We do not recommend customizing.
PersonEmail - contains the primary e-mail of the User found. We do not recommend customizing.
PersonEmail2 - it contains the secondary e-mail of the User found by default. We do not recommend customizing.
DisplayLine1 - it contains the User name and surname by default. The value of this column is used in prompting the persons on the first line of the item on the WebApp. The column can be customized.
DisplayLine2 - contains the department, office, personal number, organization, office phone and cell phone of the person. The value of this column is used in prompting the persons on the second line of item on the WebApp. The column can be customized.
DisplaySingleLine - it contains DisplayLine1 and DisplayLine2 combined columns by default. The column can be customized.

Parameters​

NameTypeDescription

@sample

nvarchar(100)

Searched string. If the procedure is not customized, the search proceeds in the person's name, department, office, personal number, organization, office phone number and cell phone number.

@accountId

int

Id of the organization in which the persons are to be searched. If the value is NULL the search will take place among all persons.

@guest

bit

It specifies whether the Host type user will also be shown in the results.

@personId

int

Person Id. If it is filled and the @sample parameter is NULL, the procedure returns a person with the given Id.

@where

nvarchar(4000)

Text string in which it is possible to define the custom part of the where condition for an assembled query. For proper functionality of ALVAO applications it is necessary that the custom procedure contains a tPerson table with a p alias.

@top

int

Specifies the number of selected records. If the @top is not filled in, the procedure returns 100 records.

@onlyCustomizableUsers

bit

@rightLimitationPersonId

int

dbo.spReadAllCommunication​

Marks all communication within the ticket regarding the entered person as read.

Parameters​

NameTypeDescription

@PersonId

int

@TicketId

int

dbo.spReadAllDiary​

Marks all ticket events with regard to the entered person as read.

Parameters​

NameTypeDescription

@PersonId

int

@TicketId

int

dbo.spRebuildHdSectionLocalizationCache​

Restores the localization cache for services.

Parameters​

dbo.spRebuildLocalizationCache​

Restores all the localization cache.

Parameters​

dbo.spRebuildReorganizeIndexes​

Rebuild or reorganize all indexes in the database based on their current fragmentation.

Parameters​

dbo.spRecalcCustColInformation​

Recalculates information about the type and length of records in the tColumn table.

Parameters​

dbo.spRefreshLicProductCoverage​

Updates the ProductCoverage table (the whole one or only a specific license)

Parameters​

NameTypeDescription

@LicHistId

int

dbo.spRefreshNodeParent​

Recalculates the parent-child object cache in the tblNodeParent table.

Parameters​

NameTypeDescription

@parentId

int

The ID of the object in whose sub-tree the cache is to be recalculated. NULL = whole tree.

dbo.spRefreshNodePath​

Updates the cache of the path in the tree (tblNode.txtPath).

Parameters​

NameTypeDescription

@node

int

dbo.spRefreshRolePersonMembership​

Updates the cache of the persons' membership in groups.

Parameters​

dbo.spRefreshSwProfileProductMembership​

Updates the cache of the software sub-profiles membership in profiles.

Parameters​

dbo.spRemoveDetectResults​

Removes the results of specific scans (ID,ID,...).

Parameters​

NameTypeDescription

@detlist

nvarchar(max)

dbo.spRemoveDetects​

Removes the specific scan (ID,ID,...).

Parameters​

NameTypeDescription

@detlist

nvarchar(max)

dbo.spRemoveNodes​

Permanently removes specific objects from tree (ID,ID,...).

Parameters​

NameTypeDescription

@nodes

nvarchar(max)

dbo.spRemoveObjects​

Permanently deletes specific objects from the tree, including child objects (ID, ID ...).

Parameters​

NameTypeDescription

@nodes

nvarchar(max)

@childonly

bit

@maxremove

int

dbo.spReplaceSectionRights​

Overwrites the permissions of the @dstSectionId service by permissions according to @srcSectionId service.

Parameters​

NameTypeDescription

@srcSectionId

int

@dstSectionId

int

dbo.spReplaceSectionSLA​

Overwrites the SLA setting of the @dstSectionId service by SLA setting according to @srcSectionId service.

Parameters​

NameTypeDescription

@srcSectionId

int

@dstSectionId

int

dbo.spRequestCommunication​

Ticket events in the Communication, Important or Detailed log mode.

Parameters​

NameTypeDescription

@iHdTicketId

int

Ticket ID (tHdTicket.iHdTicketId).

@showRemoved

bit

Display deleted records. 0 = no, 1 = yes.

@iPersonId

int

Logged in person ID (tPerson.iPersonId).

@start

int

The order of the event from which the result will be returned (sorted chronologically).

@specificActId

int

Specific event ID (Tact.iActId). If entered, the content of only this one event is returned.

@mode

int

Mode. 1 = Communication, 2 = Important, 3 = Detailed log.

@localeId

int

LCID of the language in which the dynamically generated events will be generated. E.g. for CZ = 1029, EN = 1033.

@ftsConditions

nvarchar(max)

@query

nvarchar(max)

@top

int

Limits number of returned events.

@includeTotal

bit

1 = Total column contains number of all events, 0 = Total column is NULL

dbo.spRequiredPropertyValuesCheck​

Converts the warning cache to blank mandatory properties in the tblNode table.

Parameters​

NameTypeDescription

@nodeIds

nvarchar(max)

The ID of the object in which the cache is to be recalculated.

@recalculateAll

bit

Flag whether to recalculate the cache for all objects in the tblNode table.

dbo.spRequiredPropertyValuesCheck_TemplateProp​

Parameters​

NameTypeDescription

@kindId

int

dbo.spSavePersonTicketDisplay​

Inserts a record in the history of the last viewed tickets of the requester.

Parameters​

NameTypeDescription

@ticketId

int

@personId

int

dbo.spSetContextInfo​

Will set the user’s ID and date of action within the context of the database connection. These data are used, e.g., in the triggers which create the audit trace.

Parameters​

NameTypeDescription

@personId

int

User ID (tPerson.iPersonId)

@date

datetime

Date & time. If not entered, the current date and time will be used.

dbo.spStateReqCol​

Returns a table with required fields for transitioning the ticket @TicketId to @NewStateId.

Parameters​

NameTypeDescription

@TicketId

int

@NewStateId

int

@LocaleId

int

dbo.spTakeLicence​

Withdraws license for the computer software.

Parameters​

NameTypeDescription

@LicHistItemId

int

dbo.spTicketSearch​

Returns a list of tickets by the specified string.

Parameters​

NameTypeDescription

@sample

nvarchar(100)

@personId

int

@count

int

@where

nvarchar(4000)

dbo.spTransferLicence​

Transfers the license on a different computer / to a different cost center.

Parameters​

NameTypeDescription

@itemId

int

@person

int

@NewComputerNodeId

int

@NewCostCentre

nvarchar(255)

dbo.spUpdateColumnValuePath​

Updates values in hierarchic list of values (tColumnValue) for the @ColumnId custom field.

Parameters​

NameTypeDescription

@ColumnId

int

dbo.spUpdateFTS​

Update data in the FullTextSearch column for a specific @iHdTicketId ticket or for all tickets when entering 0 to the parameter.

Parameters​

NameTypeDescription

@iHdTicketId

int

dbo.spUpdateHdSectionRightsCache​

Updates the authorization cache on services.

Parameters​

dbo.spUpdateInsertProperty​

Updates the value of ALVAO system settings. If the setting does not exist, it is created.

Parameters​

NameTypeDescription

@PropertyName

nvarchar(255)

@sPropertyValue

nvarchar(2048)

@bPropertyValue

bit

@iPropertyValue

int

@dPropertyValue

datetime

dbo.spUpdateNodeCachedCols​

Updates values in cached object columns.

Parameters​

NameTypeDescription

@nodeIds

nvarchar(max)

List of object IDs (tblNode.intNodeId), separated by commas. When NULL, procedure updates columns on all objects.

dbo.spUpdateNodeCust_All​

Updates property inheritance for the whole database.

Parameters​

dbo.spUpdateNodeCust_Nodes​

Updates property inheritance for a specific nodes and subtrees.

Parameters​

NameTypeDescription

@nodeIds

nvarchar(max)

dbo.spUpdateNodeCust_ObjNew​

Updates property inheritance after a new object was created.

Parameters​

NameTypeDescription

@objId

int

dbo.spUpdateNodeCust_PropNew​

Updates property inheritance after a new object property was added.

Parameters​

NameTypeDescription

@kindId

int

dbo.spUpdateNodeCust_PropValChanged​

Updates property inheritance after a change in the property value.

Parameters​

NameTypeDescription

@objId

int

@kindId

int

dbo.spUpdateNodeCust_Subtree​

Updates the property inheritance for a specific subtree.

Parameters​

NameTypeDescription

@objId

int

dbo.spUpdateNodeCust_TemplateProp​

Parameters​

NameTypeDescription

@kindId

int

dbo.spUpdateNodeSearch​

Update data in the Search column for specific objects, objects of specific type or for all objects when entering no IDs.

Parameters​

NameTypeDescription

@nodes

Node

Table with object IDs (tblNode.intNodeId) to update. If empty and no @classId provided, all objects are updated.

@classId

int

Object type ID (tblClass.intClassId) to update. Only objects of that type are updated.

dbo.spUpdatePersonManager​

The procedure for the update of the pre-counted structure of managers and employees.

Parameters​

dbo.spUpdatePropertyValue​

Changes the value of the object property in AM. If history is permitted for the object, the change will be recorded in the log. If the property is the name of the object, it will be converted according to the new value.

Parameters​

NameTypeDescription

@nodeId

int

@kindId

int

@valueString

nvarchar(255)

@valueDate

date

@valueFloat

float

@valueInt

int

@personId

int

@shouldLog

bit

dbo.spUpdateSlaPausedHours​

Recalculates the time period of the SLA ticket pause.

Parameters​

NameTypeDescription

@ticketId

int

dbo.spUpdateStatistics​

Updating statistics of all tables in the database.

Parameters​

dbo.spUpdateTicketAlert​

Updates the record in tHdTicket based on the current TicketAlert content.

Parameters​

NameTypeDescription

@iHdTicketId

int

Ticket ID.

dbo.spUpdateTicketHoursToCurrentTarget​

Recomputes the time elapsed from the previous operating hours interval to the due date of the current target, either for all tickets with an internal target (when 1 is in @forceALL), for a specific ticket (when @ticketID is entered), or for all tickets which were transferred to the next interval of the operating hours (when NULL is entered in both parameters).

Parameters​

NameTypeDescription

@forceAll

bit

Decide whether the recalculation should be done for all tickets with an internal goal.

@ticketId

int

Ticket number for which a recalculation will be forced.

dbo.spUpdateTicketLastAction​

Coverts the date of the last action related to the entered ticket number.

Parameters​

NameTypeDescription

@TicketId

int

dbo.spWebAppLoginDelay​

Protection against password-guessing attack.

Parameters​

NameTypeDescription

@loginTry

int