Need to extract specific entries from open text field in crystal 1

[robg82]

I need to pull specific user entries from a 'work log' field. This field is updated anytime a user adds a comment to the designated ticket, and the entry is tagged with the username and a timestamp. The entries are only available through the work log.

Below is an example of a typical work log:

1/1/2011 15:25:00 user1

this is an example work log entry

1/1/2011 17:25:00 user2

this is example2 work log entry

1/1/2011 15:25:00 user1

this is example3 work log entry

1/1/2011 15:25:00 user13

this is example4 work log entry






I've had problems getting a formula to extract only 'user1' work log entries from this table's field. I've tried using split and desiganted users or time stamps but it returns everything from my target down. There is no max or min text for the field in the DB so the entries are dynamic in their length.

Below is an example of a formula that's returning everything from 'user1' most recent entry down:

stringvar array x := split({HIS_MOD_TRB_Trouble.Work_Log,'user1');
x[ubound(x)]


I can supply more details if necessary.

 

[lbass]

Is your example above all from one row? I.e., is it one instance of a work log? Or is it four rows?

If it is one row, are you saying you want to show all lines within the field that contain a specific user? Is the user field REALLY displayed as 'User1' or could it be like 'Jane Doe'?

Please show what you would expect to be returned based upon your sample data above.

-LB

 

[robg82]

Yes, the worklog is all one row.

I do want to show all entries by a specific user. The usernames are uniform to first intial lastname. So it would show 'jdoe'.

Example of what I would like to see. I changed the time stamp for the 2nd entry. They should not have been the same time in the example used above:

1/1/2011 15:25:00 user1

this is an example work log entry

1/1/2011 18:30:00 user1

this is example3 work log entry



Thanks for the reply!

 

[lbass]

Try a formula like this, which assumes you have a parameter for entering the user you want to search for:

whileprintingrecords;
stringvar array x := split({table.worklog},chr(13));
stringvar y;
numbervar i;
numbervar j := ubound(x);
for i := 1 to j do(
if {?User} in x then
y := y + x + chr(13)
);
if len > 1 then
left(y,len-1)

-LB

 

[robg82]

Thank you much for the info.

This formula is returning the entire worklog if the specified user is included.

I tried tweaking the formula a bit, but still cant get it to return only the specified user's entries. I do have access to an audit table where i am able to count the number of entries and get the timestamps for those individual entries. If only the audit table kept the work log entries this would be easy!

Would that information help to retrieve this data?

 

[lbass]

Please show the exact formula you used that was returning the whole work log.

-LB

 

[lbass]

PS. If you implemented the formula correctly, then the problem might be that you need to use chr(10) instead of chr(13). Try that.

-LB

 

[robg82]

Magic! So changing chr(13) to chr(10) is returning an entry in the middle of the worklog for the specified user. It is omitting info if the user enters to many blank lines in an entry. It is also only returning the 1st line of their 2nd entry in the same worklog.


Example of what im seeing below.

Full worklog. Everything in red is not being returned and user jdoe was specified:


Tuesday, January 10, 2012 12:25:55 PM nms
Process ID: blah
Node: xxxx

Summary: Alert: OM Device Interface xxxx - GigabitEthernet?/? ?? xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx--17 Receive Utilization is 21% and Transmit Utilization is 141% - DeviceIP: xxx.xxx.xxx.xxx

Outage Discovered: 01/10/12 12:21:32

Outage Occurred: 01/10/12 12:18:21

Tuesday, January 10, 2012 12:28:54 PM jdoe
NOC - IP - Group

enovotny xxx xxx.xxx.xxx.xxx Alert: OM Device Interface xx - GigabitEthernet?/? ?? xxxxxxxxxxxxxxxxxxxxxxxxxx Receive Utilization is 21% and Transmit Utilization is 141% - DeviceIP: xxx.xxx.xxx.xxx 1 Investigating 1/10/12 12:18:21 PM 1/10/12 12:18:21 PM E1089-1 Normal



This alarm comes in constantly.


Tuesday, January 10, 2012 12:39:15 PM jdoe
NOC - IP - Group

here it is again

xxx xxx.xxx.xxx.xxx Alert: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Receive Utilization is 15% and Transmit Utilization is 137% - DeviceIP: xxx.xxx.xxx.xxx 1 1/10/12 12:36:14 PM 1/10/12 12:36:14 PM blah Normal


Tuesday, January 10, 2012 1:30:44 PM jmurphy
NOC - IP TIER 3 - Group
CPM showed the interface rx/tx bandwidth at 2meg. Changed it to 1gig. will monitor to see if alarm returns.






What returning using the formula you provided:

"1/10/2012 12:28:54 PM jdoe jdoe xxx xxx.xxx.xxx.xxx Alert: OM Device xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx17 Receive Utilization is 21% and Transmit Utilization is 141% - DeviceIP: xxx.xxx.xxx.xxx 1 Investigating 1/10/12 12:18:21 PM 1/10/12 12:18:21 PM blah Normal
1/10/2012 12:39:15 PM jdoe"

 

[lbass]

Please show the content of the exact formula you are using (copy and paste it into the thread).

-LB

 

[robg82]

whileprintingrecords;
stringvar array x := split({HIS_MOD_TRB_Trouble.Work_Log},chr(10));
stringvar y;
numbervar i;
numbervar j := ubound(x);
for i := 1 to j do(
if {?User} in x then
y := y + x + chr(10)
);
if len > 1 then
left(y,len-1)

 

[lbass]

Try changing the third line to:

stringvar y := "";

-LB

 

[robg82]

Formula returning the same information.



whileprintingrecords;
stringvar array x := split({HIS_MOD_TRB_Trouble.Work_Log},chr(10));
stringvar y := "";
numbervar i;
numbervar j := ubound(x);
for i := 1 to j do(
if {?User} in x then
y := y + x + chr(10)
);
if len > 1 then
left(y,len-1)

 

[lbass]

Be sure to format the formula to "can grow".

I think my formula is correct, so please clarify again exactly what your base data is in one row (don't add any of your own comments or colors) before adding the formula. Then add the result you are getting with the formula.

-LB

 

[robg82]

Thank you for working with me on this.

I did not have the formula field set to 'can grow' previously. I have set it to 'can grow' for below example.

raw worklog field:

Tuesday, January 10, 2012 12:25:55 PM nms
Process ID: xxx
Node: xxxx
Summary: Alert: OM Device Interface xxx
Receive Utilization is 21% and Transmit Utilization is 141% - DeviceIP: xxx

Outage Discovered: 01/10/12 12:21:32

Outage Occurred: 01/10/12 12:18:21

Tuesday, January 10, 2012 12:28:54 PM jdoe
NOC - IP - Group

jdoe xxx--17 Receive Utilization is 21% and Transmit Utilization is 141% - DeviceIP: xxx 1 Investigating 1/10/12 12:18:21 PM 1/10/12 12:18:21 PM xxx Normal



This alarm comes in constantly.


Tuesday, January 10, 2012 12:39:15 PM jdoe
NOC - IP - Group

here it is again

xxx
% - DeviceIP: xxx
1 1/10/12 12:36:14 PM 1/10/12 12:36:14 PM E1089-1 Normal


Tuesday, January 10, 2012 1:30:44 PM jmurphy
NOC - IP TIER 3 - Group
CPM showed the interface rx/tx bandwidth at 2meg. Changed it to 1gig. will monitor to see if alarm returns.




returned from formula:

"1/10/2012 12:28:54 PM jdoe
jdoe xxx--17 Receive Utilization is 21% and Transmit Utilization is 141% - DeviceIP: xxx 1 Investigating 1/10/12 12:18:21 PM 1/10/12 12:18:21 PM xxx Normal
1/10/2012 12:39:15 PM jdoe"





After trying this with a few more tickets i was only getting the date/time and username stamp, and no work log entry. Looks like one entry returned in the example i gave because the user put their username into the worklog entry. That may be why the 2nd entry was not returned in the above example.

 

[lbass]

I thought you only wanted the lines that contained the user name. Please show exactly what you expect to be returned based on the above entry.

-LB

 

[robg82]

Sorry for not being clear. I would like both the line containing the timestamp and username plus the corresponding entry.

Example:

Tuesday, January 10, 2012 12:39:15 PM jdoe
NOC - IP - Group

here it is again

xxx
% - DeviceIP: xxx
1 1/10/12 12:36:14 PM 1/10/12 12:36:14 PM E1089-1 Normal

 

[lbass]

So the only way you can tell whether an entry belongs to a specific user is that there is a date and the user name at the end of the date line changes?

-LB

 

[robg82]

hello lbass,

Precisely. The date/time stamp is tagged above the corresponding entry. There are no breaks other than an empty line between entries.

 

[robg82]

*Date/time and username preceed every log entry.

Left that out on the above post.

 

[lbass]

Can you do a test by creating two formulas and then report back with what they return?

instr({table.worklog},chr(13))

instr({table.worklog},chr(10))

These two are often found in the same record, and because you weren't using can grow last time, I am unsure of whether both are present.

-LB