Requesting assistance extracting report of overlaps in Change Windows 1

[rleyba828]

Hi Team,

Just need your help generating a report. I am using Crystal Reports XI. I am running a report that lists all our IT Department's Infrastructure Changes and Scheduled Change Windows. The primary table is a list of Changes and change related detail (persons, IDs, Implementation Start DateTime and Implementation End DateTime fields, etc.) The primary key in that table is ChangeNumber. Next, I have a child table which is a list of devices (servers, firewalls, network equiment) that are linked to the primary table via a ChangeNumber field. So this table only has three fields --ChangeNumber, DeviceType, DeviceName. I have already linked the two tables together in my report so I can generate lists showing schedules, engineers, etc.

A Change Window normally involves multiple devices at once and on a given day there are multiple Engineers with multiple Change Windows with multiple devices each, thus keeping track of which devices are being touched within a given time is quite daunting.

I would like to generate a report of Change Conflicts which lists devices to be updated twice (or more) by two (or more) IT engineers.

Something like this:

[tt]
Device: Server01

device ChangeNumber Implementation Stard DateTime Implementation End DateTime IT Staff
Server01 C000115 24-Jan-2012 9:00 PM 24-Jan-2012 10:00 PM Engineer-A
Server01 C000122 24-Jan-2012 9:30 PM 24-Jan-2012 10:00 PM Engineer-B


Device: Firewall02

Firewall02 C000115 24-Jan-2012 9:00 PM 24-Jan-2012 10:00 PM Engineer-A
Firewall02 C000135 24-Jan-2012 9:00 PM 24-Jan-2012 11:00 PM Engineer-B
Firewall02 C000142 24-Jan-2012 8:00 PM 24-Jan-2012 10:00 PM Engineer-A

[/tt]
In the above scenario, there are OVERLAPS in change windows involving Server01 and Firewall02 and I want Crystal to show me these overlaps.

Thanks in advance for all the help.

 

[lbass]

Allow all records into the report for a selected time period, insert a group on device, sort ascending by implementation start and then by implementation end, and then use a suppression formula like the following:

distinctcount({change.changenumber},{device.devicename})=1 or
distinctcount({change.engineer},{device.devicename})=1 or
(
(
previousisnull({device.devicename}) or
(
{device.devicename}=previous({device.devicename}) and
{change.implementationstart}>=previous({change.implementationend})
)
) and
(
nextisnull({device.devicename} or
(
{device.devicename}=next({device.devicename}) and
{change.implementationend}<next({change.implementationstart})
)
)
)

This should get you close.

-LB

 

[rleyba828]

Thanks lbass, we are getting close indeed. I have done the record suppression using the formula below.

An excerpt of my output (changed the device names for security):

[tt]
Firewall-A
C8057 Firewall-A 2/2/2012 11:00:00PM 2/3/2012 5:00:00AM
C8190 Firewall-A 2/2/2012 11:19:00PM 2/3/2012 4:19:00AM

Firewall-B
Firewall-C
Firewall-D

Firewall-E
C7921 Firewall-E 1/31/2012 11:00:00PM 2/1/2012 1:00:00AM
C8187 Firewall-E 1/31/2012 11:00:00PM 2/1/2012 4:00:00AM
C6394 Firewall-E 2/2/2012 12:06:00AM 2/2/2012 2:07:00AM

Firewall-F
C7777 Firewall-F 1/31/2012 11:00:00PM 2/1/2012 2:00:00AM
C7346 Firewall-F 1/31/2012 11:00:00PM 2/1/2012 5:00:00AM
C8057 Firewall-F 2/2/2012 11:00:00PM 2/3/2012 5:00:00AM
C8190 Firewall-F 2/2/2012 11:19:00PM 2/3/2012 4:19:00AM

[/tt]

* As can be seen, Firewall-A reports 2 records conflicting, none for Firewalls b, c and D.

** Report shows 3 conflicting records for Firewall-E but there should really be only TWO, not sure why the third one shows up.

*** There are 4 conflicting records for Firewall-F, but they are actually TWO different instances of TWO records each....how to separate them?

Here is my formula, using ACTUAL field names now.

[tt]
distinctcount({ChangeRecord.ChangeNumber},{ChangeNumberDevices.DeviceName})=1 or
distinctcount({ChangeRecord.NetworkImplementorName},{ChangeNumberDevices.DeviceName})=1 or
(
(
previousisnull({ChangeNumberDevices.DeviceName}) or
(
{ChangeNumberDevices.DeviceName}=previous({ChangeNumberDevices.DeviceName}) and
{ChangeRecord.ImplementationStartDate}>=previous({ChangeRecord.ImplementationEndDate})
)
) and
(
nextisnull({ChangeNumberDevices.DeviceName}) or
(
{ChangeNumberDevices.DeviceName}=next({ChangeNumberDevices.DeviceName}) and
{ChangeRecord.ImplementationEndDate}<next({ChangeRecord.ImplementationStartDate})
)
)
)

[/tt]

*We are getting close LBass, thanks again for all the help.

 

[rleyba828]

Actually, at the end of all this, I need to burst the report, and have the groupings Grouped by Engineer (level 1 grouping) and then by Device (level 2 grouping) so that I can advise each engineer what their change conflicts are and for which devices.

The output above (without the grouping yet, would look like this):

[tt]
Firewall-A
C8057 Firewall-A Engineer-A 2/2/2012 11:00:00PM 2/3/2012 5:00:00AM
C8190 Firewall-A Engineer-B 2/2/2012 11:19:00PM 2/3/2012 4:19:00AM

Firewall-B
Firewall-C
Firewall-D

Firewall-E
C7921 Firewall-E Engineer-C 1/31/2012 11:00:00PM 2/1/2012 1:00:00AM
C8187 Firewall-E Engineer-D 1/31/2012 11:00:00PM 2/1/2012 4:00:00AM
C6394 Firewall-E Engineer-F 2/2/2012 12:06:00AM 2/2/2012 2:07:00AM

Firewall-F
C7777 Firewall-F Engineer-C 1/31/2012 11:00:00PM 2/1/2012 2:00:00AM
C7346 Firewall-F Engineer-D 1/31/2012 11:00:00PM 2/1/2012 5:00:00AM
C8057 Firewall-F Engineer-A 2/2/2012 11:00:00PM 2/3/2012 5:00:00AM
C8190 Firewall-F Engineer-B 2/2/2012 11:19:00PM 2/3/2012 4:19:00AM

* Thanks very much.

[/tt]

 

[lbass]

I don't have time yet to review this, but you won't be able to use this method AND group by engineer, since the suppression is based on a SEQUENCE of records that include other engineers.

-LB