1 Reply Latest reply on Mar 26, 2014 12:57 PM by cgregors

    WPM Uptime Report Weekdays 8AM-5PM?

    bharris

      I was wondering if anyone has written any WPM transaction reports that are filtered by Weekdays 8AM-5PM only?  I found an older discussion thread for interfaces (Report  - Percent utilization  - Business Hours) that allow for "Time of Day" in the filter, but I am not seeing that same option on the WPM Transaction reports. Is there a way to do a similar thing? 

        • Re: WPM Uptime Report Weekdays 8AM-5PM?
          cgregors

          I felt like digging into this because it has relevance to similar request I have from a customer.  Here's What I did:

           

          • Using Report writer, generate a report that includes EVERY possible column that can be used. It takes a while to do, but if you know exactly what you want reported on, then only select those fields.
          • Set the Time Frame to something small. I used the last 2 hours.  This won't matter much because we're going to throw it away later.
          • Under the Report menu, enable the "Show SQL" box.  A new tab named SQL will appear.
          • Copy the sql out of the SQL tab.  Here's what I got. Its long

          Select

            Transaction_Id,

            Transaction_Availability,

            Minimal_Duration,

            Maximal_Duration,

            Average_Duration,

            Times_Played, 

            Cast(Error_Message As nvarchar(250)) as Error_Message,

            Transaction_Id_1,

            Transaction_Name, 

            Cast(Description As nvarchar(250)) as Description,

            Playback_Interval,

            Is_Enabled,

            Is_Unmanaged,

            Unmanage_From,

            Unmanage_Until, 

            Cast(Transaction_Status As nvarchar(250)) as Transaction_Status,

            Transaction_Duration,

            Last_Played,

            Error_Message_1,

            Recording_Id,

            Recording_Name, 

            Cast(Description_1 As nvarchar(250)) as Description_1,

            Created,

            Last_Updated,

            Minimal_Duration_From_All_Locations,

            Average_Duration_From_All_Locations,

            Maximal_Duration_From_All_Locations,

            Location_Id,

            Location_Name,

            Url,

            Port,

            Use_Proxy,

            Proxy_Url,

            Connection_Status_Id,

            Connection_Status, 

            Cast(Connection_Status_Message As nvarchar(250)) as Connection_Status_Message,

            Last_Connection_Time,

            Number_of_All_Transactions,

            Number_of_Managed_Transactions,

            Hostname,

            DNS_Name,

            IP_Address,

            OS_Version

          From

            ( SELECT 

            TOP 10000 SEUM_TransactionsAvailability.TransactionId AS Transaction_Id,

            SEUM_TransactionsAvailability.PercentAvailability AS Transaction_Availability,

            SEUM_TransactionsAvailability.MinDuration AS Minimal_Duration,

            SEUM_TransactionsAvailability.MaxDuration AS Maximal_Duration,

            SEUM_TransactionsAvailability.AvgDuration AS Average_Duration,

            SEUM_TransactionsAvailability.RecordCount AS Times_Played,

            SEUM_TransactionsAvailability.ErrorMessage AS Error_Message,

            SEUM_TransactionsReportsView.TransactionId AS Transaction_Id_1,

            SEUM_TransactionsReportsView.Name AS Transaction_Name,

            SEUM_TransactionsReportsView.Description AS Description,

            SEUM_TransactionsReportsView.PlaybackInterval AS Playback_Interval,

            SEUM_TransactionsReportsView.IsLicensed AS Is_Enabled,

            SEUM_TransactionsReportsView.Unmanaged AS Is_Unmanaged,

            SEUM_TransactionsReportsView.UnmanageFrom AS Unmanage_From,

            SEUM_TransactionsReportsView.UnmanageUntil AS Unmanage_Until,

            SEUM_TransactionsReportsView.LastStatus AS Transaction_Status,

            SEUM_TransactionsReportsView.LastDuration AS Transaction_Duration,

            SEUM_TransactionsReportsView.LastPlayed AS Last_Played,

            SEUM_TransactionsReportsView.LastErrorMessage AS Error_Message_1,

            SEUM_RecordingsReportsView.RecordingId AS Recording_Id,

            SEUM_RecordingsReportsView.Name AS Recording_Name,

            SEUM_RecordingsReportsView.Description AS Description_1,

            SEUM_RecordingsReportsView.Created AS Created,

            SEUM_RecordingsReportsView.LastUpdated AS Last_Updated,

            SEUM_RecordingsReportsView.MinDuration AS Minimal_Duration_From_All_Locations,

            SEUM_RecordingsReportsView.AvgDuration AS Average_Duration_From_All_Locations,

            SEUM_RecordingsReportsView.MaxDuration AS Maximal_Duration_From_All_Locations,

            SEUM_AgentsAlertsData.LocationId AS Location_Id,

            SEUM_AgentsAlertsData.Name AS Location_Name,

            SEUM_AgentsAlertsData.Url AS Url,

            SEUM_AgentsAlertsData.Port AS Port,

            SEUM_AgentsAlertsData.UseProxy AS Use_Proxy,

            SEUM_AgentsAlertsData.ProxyUrl AS Proxy_Url,

            SEUM_AgentsAlertsData.ConnectionStatusId AS Connection_Status_Id,

            SEUM_AgentsAlertsData.ConnectionStatus AS Connection_Status,

            SEUM_AgentsAlertsData.ConnectionStatusMessage AS Connection_Status_Message,

            SEUM_AgentsAlertsData.LastConnectionTime AS Last_Connection_Time,

            SEUM_AgentsAlertsData.NumAllTransactions AS Number_of_All_Transactions,

            SEUM_AgentsAlertsData.NumManagedTransactions AS Number_of_Managed_Transactions,

            SEUM_AgentsAlertsData.Hostname AS Hostname,

            SEUM_AgentsAlertsData.DNSName AS DNS_Name,

            SEUM_AgentsAlertsData.IP AS IP_Address,

            SEUM_AgentsAlertsData.OSVersion AS OS_Version

            FROM

            (

            (SEUM_TransactionsAvailability

            INNER JOIN SEUM_TransactionsReportsView ON (SEUM_TransactionsAvailability.TransactionId = SEUM_TransactionsReportsView.TransactionId)

            ) 

            INNER JOIN SEUM_RecordingsReportsView ON (SEUM_TransactionsReportsView.RecordingId = SEUM_RecordingsReportsView.RecordingId)

            )

            INNER JOIN SEUM_AgentsAlertsData ON (SEUM_TransactionsReportsView.AgentId = SEUM_AgentsAlertsData.LocationId)

            WHERE

            ( DateTime BETWEEN 41721.875 AND 41722 )

            )

          As r

          • Notice how the Where clause says ( DateTime BETWEEN 41721.875 AND 41722 ). You can tear the query apart to figure out where it gets the DateTime field from, but lets assume it is correct for the last 2 hours.
          • Using a text editor, replace the where clause with the following

          // ( DateTime BETWEEN 41721.875 AND 41722 )

          (datepart(weekday,DateTime) in (2,3,4,5,6)) and    // monday - friday

          datepart(hour,DateTime) in (8,9,10,11,12,13,14,15,16,17)   // 08:00 - 17:59

          • Take the new SQL query and create an "Advanced SQL" report using the query.  NOTE: you'll have to remove the "//" comments as Report Writer doesn't like that commenting style.

          • Run the New Query. WARNING: the query is only day of week and time of day constrainted. It might will a lot of stuff.
          • Format the columns, set the Report Grouping and save the report.

           

          As an SQL exercise for you, figure out how to time constrain the DateTime to yesterday or whatever period you need the report to cover.

           

          Additionally you could have set a Filter on the query which would have produced a more complicated where () clause.

           

          Summary:

          1. Build reports using Report Writer
          2. Extract the sql that Report Writer wrote
          3. Manipulate the where() clauses for special situations.

           

          Chris.