5 Replies Latest reply on Jun 21, 2010 3:01 PM by S Crawford

    How to filter based on job run start date (for compliance runs)?

    S Crawford

      Is it possible to specify a date and time for a particular compliance template that was run by a job in BSARA?


      I am trying to create a compliance report and I am able to display the results based on compliance template.  But it displays the results of every single time it was run in a compliance job.  I tried creating a prompt page to ask for the 'Job Run Start Time', but I get an error when trying to filter using this object.  I would like to be able to specify or choose by job run date/time and only display those compliance results.


      The following filters do not work:


      [Job Run Start Time] = ('June 17, 2010 11:30:06 PM EDT')

      [Report Layer].[Job Run].[Job Run Start Time] in ('2010-06-17 23:30:06')

      [Report Layer].[Job Run].[Job Run Start Time] = ?Job_Runs?  --- (?Job_Runs? was a parameter passed in from the prompt page)


      Seems like this should be easy to do, but can't make it work.

        • 1. Re: How to filter based on job run start date (for compliance runs)?

          What exactly is the use case? The out of box compliance reports cover these use cases:

          1. "Compliance summary by policy" filters on the latest run. This filter is also available in the model for custom reports.

          2. "Compliance trend by policy" shows results over time by job run, summarized by template.


          Sounds like what you are lookling for is similar to the second report in a table format, and potentially a filter.

          The trend report is using the job run date hierarchy that can be found under the job run folder in the model.


          Tip: In BBDS-SA 2.0 and above you can right-click on data items in the report results screen and select "Lineage". It traces the data item back to the model elements including a full path.

          • 2. Re: How to filter based on job run start date (for compliance runs)?
            S Crawford

            I am trying to list basic compliance policy results in a report that will indicate where certain servers need to be updated / patched.  It will actually show each server's results in a table (not a chart), listing each rule and whether it passed or failed.


            I took a screenshot of what the first page of the report looks like.  There are two other pages that are summary results, but from this page you can see that the report basically lists the rules in the compliance template and the results.   I have a filter setup that lets me choose which component template to report against, but I am looking to do the same for the Job Run Start Time, so I can generate reports for each time the template was run, instead of listing every single run in one report.  Is there a way to filter the date and time against the Job Run Start Time?  I noticed the out-of-box report you mentioned (Compliance Trend by Policy) lets you filter by month, day, year... but I did not see time filters.  I have multiple runs of this template in the same day, so I would need to specify the time as well.




            PS - I am running on BSARA 1.0.03 Hotfix 10




            • 3. Re: How to filter based on job run start date (for compliance runs)?

              Got it. I looked at the "Detaild Compliance Results" report that ships with the product. It is probably the closest to your requirement.

              It displays a job run time column which is based on [Report Layer].[Job Run].[Job Run Start Time]. You mentioned that this filter did not work for you. What exactly happens when you try to filter using this field?

              I created a simple report in Query Studio what shows template name, server name and rule name and also added the "Job Run Start Time" that is available under the "Compliance Summary" folder in the model. I could filter on it accurately using a range From/To filter.

              1 of 1 people found this helpful
              • 4. Re: How to filter based on job run start date (for compliance runs)?
                S Crawford

                Yes, trying to create a filter with the Job Run Start Time value is giving me trouble.  For example, when I try entering this for a filter:


                [Report Layer].[Job Run].[Job Run Start Time] in ('2010-06-17 23:30:36')


                I get this error when the report runs:

                UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult" is inactive. UDA-SQL-0107 A general exception has occurred during the operation "open result". ORA-01843: not a valid month


                So obviously I don't know what the proper syntax is supposed to be for 'Job Run Start Time'.  I get similar errors when trying to pass this through a prompt page as well.


                Let me know if you figure out the syntax for the To/From filter and I'll try importing it into my report.  I, too, have noticed that sometimes it's easier to go through Query Studio to create certain objects and expressions and then copy them over to Reports Studio... Thanks

                • 5. Re: How to filter based on job run start date (for compliance runs)?
                  S Crawford

                  Hi Hayim,


                  I took your advice and went through Query Studio to build a basic version of the report I'm looking for and created the filters there, and then moved everything over to Reports Studio to see how it created the filters there.  That worked for me... I now have a prompt page that lets the user choose the compliance job name and the date range.  For both of these, 'Generated Prompts' were used, which I didn't try when I initially tried creating the prompt page.  The filters simply reference both of those parameters from the prompt page.


                  Thanks for reminding me to use the Query Studio as a 'cheating' method to see how filters are supposed to be setup in Reports Studio...!