Re: Bring back the ARSList MVP for 2013

[Jim Ashton]

Yes.

I'll confess to not voting this year (for the first time) since my own Remedy 
involvement has diminished substantially, but I don't think that should devalue 
the efforts of those contributors who bring the list to life and keep it 
breathing.

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"

Re: .NET API

[Jim Ashton]

I'm heavily dependent upon it and expect to continue to use it indefinitely.

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"

Re: SQL ?

[Jim Ashton]

If you are working in SQL Server (2000 or later), the following UDF will do the 
trick:

ALTER FUNCTION [dbo].[fnGetRemedyDateOnly](@Days int)
RETURNS datetime
AS
BEGIN
DECLARE @BaseDate datetime
DECLARE @TargetDate datetime
SET @BaseDate='1900-01-01'
SET @TargetDate=dateadd(dd,@Days - 2415021,@BaseDate)
RETURN (@TargetDate)
END

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"

Re: associate a user name with a preference server

[Jim Ashton]

No, unfortunately you do not.  TEST1 and  test1 are interpreted as being 
equivalent from the point of view of whatever procedure is looking for 
associated preference servers.  So if you log in as TEST1 on ServerA, and 
then test1 (which should default to ServerB) ServerA shows up in the 
preference server textbox.

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the 
Answers Are"

User tool: Associate a user with a preference server

[Jim Ashton]

When logging in to the user tool, is it possible to associate a particular 
user with a particular preference server?

By way of example, suppose I have two users, "testuser" and "TESTUSER".  
testuser is supposed to use preference server "PreferenceServer1", and 
TESTUSER is supposed to use preference server "PreferenceServer2".

Each of these users has it's own specific home directory.

If I select "testuser" from the drop-down list of user names, the 
Preference Server field fills in, but it always fills in with the LAST 
preference server selected, NOT the one I want it associated with 
("PreferenceServer1" above).  

So here's the question: Is there a way to associate the user and the 
preference server so the correct preference server appears when the user is 
selected at login?

Thanks in advance,

Jim.

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the 
Answers Are"

Re: associate a user name with a preference server

[Jim Ashton]

Thanks, L.J.  That works exactly as you described.  

Unfortunately, the example I gave did not adequately define the nature of 
my problem.  My two users who require different preference servers are 
actually called test1 and TEST1 (utilizing case sensitivity of user names, 
which apparently does not extend to preference server association).  
Neither of these naming conventions is readily changeable; test1 duplicates 
NT logins for integrated security, and TEST1 is an external system which we 
have no choice but to use, and over which we have utterly no control (so 
those cannot be changed either).

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the 
Answers Are"

associate a user name with a preference server

[Jim Ashton]

Hello.

Is it possible to permanently associate a particular user name and a given 
preference server?

For example, I have users TEST1 and TEST2, and preference servers ServerA 
and ServerB.  I want TEST1 to always use ServerA, and TEST2 to always use 
ServerB when logging in with the Desktop Client on a particular workstation.

But what actually occurs is the "Preference Server" dropdown always 
contains just two choices: (none) and the LAST preference server selected.

So if TEST1 logs in to ServerA, then TEST2 comes along, the preference 
server choice she is presented with is ServerA - no option to select 
ServerB.  

Is there any way to work around this so each user sees the preference 
server appropriate to him, rather than the last one selected?

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the 
Answers Are"

Re: OT: Creating a view from 2 tables using SQL...

[Jim Ashton]

Hi, Joe.

If I understand your problem correctly, I think you may need a UNION query.

Something similar to that below:

SELECT Col1, Col2
FROM Table1
UNION
SELECT Col1, Col2
FROM Table2


This assumes Col1 is a compatible data type in both Table1 and Table2, as 
is Col2. This will give you the entire set of data from both tables in one 
view; that is if each table contains 10 records, the view will consist of 
20 records.

Cheers,
Jim.

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the 
Answers Are"

Re: Trace a user to an IP address?

[Jim Ashton]

Thanks to all for your suggestions re. this question.  I overlooked the 
inclusion of IP addresses in the API log when I was checking logs as an 
option, which prompted the question initially.  Hooking into the login 
process and capturing additional data seems the most logical approach, and 
I will investigate this further at a later date.

Cheers,
Jim.

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the 
Answers Are"

Trace a user to an IP address?

[Jim Ashton]

Is it possible to determine the IP address of a client workstation logging 
on to Remedy as a particular user?

I have a user which is reserved for special purposes, but is periodically 
appearing as logged on when I don't expect it to be.  I would like to 
determine the source of these logons, which would be facilitated with an IP 
address, or some other means of identifying the workstation involved.

Environment is as follows:

AR System: Version 6.00.01 Patch 1454
OS: Windows 2000 SP4
SQL: MS SQL Server 2000 SP4

Any advice would be appreciated.

Thanks in advance,

Jim.

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the 
Answers Are"

Windows Cross Ref Blank Password feature - how does it work?

[Jim Ashton]

I would like to try and gain an understanding of the mechanics behind the 
Cross Ref Blank Password Windows NT login validation feature.  Please note 
that I am not asking for an explanation of how to implement the feature 
(we've been using it for some time) but rather how it actually works - what 
goes on behind the scenes.

Since we are talking about users' network login passwords, I feel obligated 
to be able ensure those same users that the security of said passwords is 
not being violated in some way through their use by Remedy, and until I 
fully understand how password validation is accomplished, I can't provide 
that assurance.

Any insight would be greatly appreciated.

Thanks in advance,

Jim.

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the 
Answers Are"

Re: OT: ARUtilities Holiday Special

[Jim Ashton]

Apologies to the list for resending this. I meant to send it solely to 
myself as a reminder.  It is a very useful product!

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the 
Answers Are"

Re: OT: ARUtilities Holiday Special

[Jim Ashton]

On Fri, 8 Dec 2006 07:47:44 -0500, [EMAIL PROTECTED] wrote:

>Hello everyone, Happy Holidays.
>
>ARUtilities Suite is now available for half price during the month of 
December.
>The sale will end January 1, 2007.
>
>ARUtilities Suite includes:
>ARUtilities 7.0
>ARUtilities 6.5
>ARUtilities 5.6
>ARUtilities 4
>
>ARUtilities 7.0 has had a recent update that addressed issues reported by 
users,
>and new features requested by users. New search abilities include 
searching Help
>Text and Change History for all objects on your servers.
>
>For more information and to download visit
>http://www.arutilities.com
>
>Comments are welcome at [EMAIL PROTECTED]
>problems can be reported to [EMAIL PROTECTED]
>
>Thank you so much for your support.
>Have a safe and happy holiday.
>
>Les Ganton
>
>___

>UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where 
the Answers Are"
>=

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the 
Answers Are"

Re: Retrieving Date from SQL

[Jim Ashton]

Yet another option

The following T-SQL functions convert date/time to/from Remedy and account 
for GMT and Daylight Savings Time (set up for Canada):


/* NOTES on this function:
Input parameters @GetUTCDate and @GetDate expect the results of the 
GetUTCDate() and GetDate()
built-in functions respectively.  These functions won't work within the 
context of
a UDF.
@RemedySeconds is the value from a Remedy date field.

SAMPLE USE:
declare @result datetime
declare @remedyseconds int
select @remedyseconds=1140120203 --create_date from shr_categorization 
where entry_id=0001583

select @result=msdb.dbo.fnGetRemedyDate(@remedyseconds,GetUTCDate(),GetDate
())
print @result

Acknowledgements: 
http://classicasp.aspfaq.com/date-time-routines-manipulation/how-do-i-
convert-local-time-to-utc-gmt-time.html
*/
USE MSDB
GO

ALTER FUNCTION fnGetRemedyDate(@RemedySeconds int, @GetUTCDate datetime, 
@GetDate datetime)
RETURNS datetime
AS
BEGIN
 DECLARE 
@sdt smalldatetime, 
@edt smalldatetime,
@i tinyint, 
 @Offset int,
 @RemedyDate datetime,
 @UTCDate smalldatetime
 SELECT @UTCDate = DATEADD(second, @RemedySeconds, '1970-01-01') -- 
value of Remedy (epoch) time GMT 

 -- find first Sunday in April 
 SET @i = 1
 WHILE @i < 7 
 BEGIN 
  SET @sdt = RTRIM(YEAR(@UTCDate))+'040'+RTRIM(@i)+' 02:00'
  IF DATEPART(weekday,@sdt)=1  
  BEGIN 
   SET @i = 7 
  END 
  SET @i = @i + 1 
 END 
 -- find last Sunday in October 
 SET @i = 31 
 WHILE @i > 24 
 BEGIN 
  SET @edt = RTRIM(YEAR(@UTCDate))+'10'+RTRIM(@i) + ' 02:00' 
  IF DATEPART(weekday,@edt)=1  
  BEGIN 
   SET @i = 24 
  END 
  SET @i = @i - 1 
 END 
 SELECT @Offset = DATEDIFF ( ss , @GetUTCDate , @GetDate )
/* NOTE that the adjustment below is slightly flawed; you are comparing the 
initial value in GMT/UTC time
to the start/end times of DST in the local time zone (since they can't be 
adjusted for UTC without knowing
the adjustment factor)!
A more accurate (but also complex) methodology involves use of the registry 
as described at
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?
txtCodeId=544&lngWId=5
*/
 IF (@UTCDate>[EMAIL PROTECTED] AND @UTCDate<@edt) 
  SET @offset = @offset + 3600 -- an hour's worth of seconds 
 SELECT @RemedyDate = DATEADD(second,(@RemedySeconds + 
@Offset), '1970-01-01')
 RETURN(@RemedyDate)
END


/*
SAMPLE USE:
declare @TargetDate datetime
declare @RemedyDate datetime
set @TargetDate='2006-02-09'
select @RemedyDate=msdb.dbo.fnSetRemedyDate(@TargetDate,GetUTCDate(),GetDate
())
print @RemedyDate
*/
ALTER FUNCTION fnSetRemedyDate(@TargetDate datetime, @GetUTCDate datetime, 
@GetDate datetime)
RETURNS int
AS
BEGIN
-- work out GMT/UTC offset value
 DECLARE @Offset int,
@sdt smalldatetime, 
@edt smalldatetime,
@i tinyint
 -- find first Sunday in April 
 SET @i = 1
 WHILE @i < 7 
 BEGIN 
  SET @sdt = RTRIM(YEAR(@TargetDate))+'040'+RTRIM(@i)+' 02:00'
  IF DATEPART(weekday,@sdt)=1  
  BEGIN 
   SET @i = 7 
  END 
  SET @i = @i + 1 
 END 
 -- find last Sunday in October 
 SET @i = 31 
 WHILE @i > 24 
 BEGIN 
  SET @edt = RTRIM(YEAR(@TargetDate))+'10'+RTRIM(@i) + ' 
02:00' 
  IF DATEPART(weekday,@edt)=1  
  BEGIN 
   SET @i = 24 
  END 
  SET @i = @i - 1 
 END 
 SELECT @Offset = DATEDIFF ( hh , @GetDate, @GetUTCDate )
-- adjust for DST (see NOTE in fnGetRemedyDate)
 IF (@TargetDate>[EMAIL PROTECTED] AND @TargetDate<@edt) 
  SET @Offset = @Offset - 1 -- an hour's worth of seconds 

--apply offset value to target date (so date is entered in GMT time, not 
local time)
 SELECT @TargetDate = dateadd(hh,@Offset,@TargetDate)

-- determine Remedy int value for revised target date
 DECLARE @RemedyDate int
 SELECT @RemedyDate=ABS(DATEDIFF(ss,@TargetDate,'1970-01-01'))
 RETURN(@remedyDate)
END

/*
SAMPLE USE:
declare @result int
declare @GetUTCDate datetime
select @result=msdb.dbo.fnSetRemedyDate(GetUTCDate())
print @result
*/
CREATE FUNCTION fnSetRemedyCurrentDate(@GetUTCDate datetime)
RETURNS int
AS
BEGIN
 DECLARE @RemedyDate int
 SELECT @RemedyDate=ABS(DATEDIFF(ss,@GetUTCDate,'1970-01-01'))
 RETURN(@remedyDate)
END

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the 
Answers Are"

Re: Fwd: Question: SQL

[Jim Ashton]

What if you try an active link that executes on After Modify instead? 

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the 
Answers Are"

Re: Fwd: Question: SQL

[Jim Ashton]

I think you may need one more set of brackets. It appears that the query 
always returns 1 because Request_ID = '$Request ID$' is always true and at 
least one of the OR conditions is true somewhere in the 
IPVPN_IPACT_Overview table.
Put another set of brackets around everything following the AND (as shown 
below) which will result in both that particular request and at least one 
of the OR conditions needing to be true to return a value.

SELECT COUNT (Request_ID) FROM dbo.IPVPN_IPACT_Overview WHERE (Request_ID =
'$Request ID$') AND ( ((PATINDEX('%SOW%', UPPER(Scope_of_Work)) > 0) OR
(PATINDEX('%IPDP%', UPPER(Scope_of_Work)) > 0) OR (PATINDEX('%MACD%',
UPPER(Scope_of_Work)) > 0)) )

Cheers,
Jim.

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the 
Answers Are"

Re: Create a Button to Copy Details of Parent

[Jim Ashton]

John,

I'm not familiar with Helpdesk application but I did create something 
similar for Asset Management (I think, based on your description of the 
problem).  In Asset Management I have a button which allows a user to copy 
a Purchase Requisition (AST:PurchaseRequisition), including the details of 
the requisition.

It works roughly as follows:
When the user clicks the "Copy Requisition" button, an active link opens a 
new AST:PurchaseRequisition window in Submit mode and copies various values 
from the instance of AST:PurchaseRequisition on which the button was 
clicked. One of the values set in the new instance is a temp field 
(zTmpCopyRequisitionID) which contains the Requisition ID of the 
requisition being copied. The active link then closes the initial window.

Active Link = AST:PR_CopyRequisition
Execute On = Button/Menu Item = CopyRequisition_btn
Action 1 = Open Window - Window Type=Submit - Target Location=New -
  Form Name=AST:PurchaseRequisition - Field Mapping=various
Action 2 = Close Window

Another active link is attached to the Load event of the 
AST:PurchaseRequistion form. It runs on the condition that the temp 
variable referred to above (zTmpCopyRequisitionID) is not null.  If 
zTmpCopyRequisitionID has a value, the active link calls an active link 
guide (which actually copies the detail table values), resets 
zTmpCopyRequisitionID to null, and refreshes the line item table.

Active Link = AST:PR_Initialize_CopyRequisitionLineItems 
Execute On = Loaded
Action 1 = Call Guide - Guide Name=AST:PR_CopyRequisitionCreateLineItems -
table loop=LineItems_tbl
Action 2 = Set Fields - zTmpCopyRequisitionID=$NULL$
Action 3 = Change Field - Field=LineItems_tbl - Refresh Table Field

The detail table on the requisition form (LineItem_tbl - table property-
qualification) is conditionally populated from the line item form 
(AST:ReqLineItem) based on either matching instance ids OR a non-null value 
in zTmpCopyRequisitionID ie. it uses the requisition value in this field to 
populate the table with all the line items matching this requisition id.

The active link guide loops through this table and creates a new line item 
in AST:ReqLineItem (linked to the new requisition) for each line in the 
table by repeatedly calling AST:PR_CopyRequisitionCreateLineItem.

Active Link = AST:PR_CopyRequisitionCreateLineItem
(called by guide)
Action 1 = Push Fields - Push Value To=AST:ReqLineItem - qualification=
(1=2) - If No Requests Match=Create a New Request - Fields=various (all the 
column fields from LineItem_tbl)

After all the new line item records are created, zTmpCopyRequisitionID is 
reset to null and the table is refreshed.  This time, the table gets its 
values from the line items linked to the new requisition, rather than from 
the old requisition.

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the 
Answers Are"

Re: Direct SQL - How can I use a $TIMESTAMP$ reference?

[Jim Ashton]

Leigh,

Try the following statement (I've substituted a SQL-based calculation of 
epoch time for the $TIMESTAMP$ value):

SELECT count(*) FROM Issue 
WHERE  Customer_Abbreviation = '$Customer_Abbreviation$' AND 
Create_Date > (ABS(DATEDIFF(ss,GetUTCDate(),'1970-01-01')) - ($Threshold 
Days$ * 24 * 60 * 60))

ABS(DATEDIFF(ss,GetUTCDate(),'1970-01-01')) represents the current date and 
time in epoch time.

I'm assuming $Threshold Days$ is already an integer value; if not, you may 
need to include a CAST function to convert it from a varchar to an int.

Cheers,
Jim.

___
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org ARSlist:"Where the 
Answers Are"

Re: Search Sync Database - Not working

[Jim Ashton]

ARS Server version: 6.00.01 patch 1454
Windows Server version: Windows 2000 SP4
SQL: SQL Server 2000

I have successfully automated the Sync Search process as follows in the 
above environment, and have been using this for several months.

-- synchronize search database
DELETE FROM arsystem.dbo.object_search_admin  -- must remove existing 
record for CLI to work!

SET @strCmd='c:\progra~1\arsyst~1\admin\aradmin.exe -x jus000as5047 -u 
Demo -p Demo  -portnum  -s'

EXEC master..xp_cmdshell @strCmd

The trick is removing the existing record from object_search_admin first. 
It took some time to find that out...

___
UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org

Re: "orphaned" field on form view

[Jim Ashton]

Thank you, John.  It was rather clueless of me to miss that piece of 
functionality in the admin tool entirely.  

___
UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org

"orphaned" field on form view

[Jim Ashton]

Hello.

Has anyone encountered the situation of having an "orphaned" or "lost" 
field on a form view?  I have a web (fixed fields) view of the AST:Asset 
form (SchemaID 191 in my arschema table on SQL Server) which is supposed to 
have a Search Bar form action field (FieldID 1005), but which I cannot find 
anywhere on the form, either in the view in which it is supposed to be 
located or in any other view.

The field does exist in the field table.
The query "select * from field where schemaid=191 and fieldid=1005" yields 
one record with a field name of "Search Bar"

The field does NOT exist in the field_dispprop table, which I understand 
contains field label data, among other things.  I assume this may have 
something to do with why it is not visible in the "Find Field" drop-down 
list, which is (as far as I know) the only way to locate a field on a form.

Ideally, if it is not possible to locate this field, I would like to remove 
it entirely and attempt to recreate it, but I haven't been able to find a 
way to do this either.

Any help would be greatly appreciated!

Thanks,

Jim Ashton

___
UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org