10 Replies Latest reply: Sep 17, 2013 9:06 AM by Milan_Volejnik RSS

querying past alerts

chlsmith

If I pull up an alert, I can see that the Past Activity is available, too.   Is there a way I can query the past activity on my own?   For instance, if an alert was raised 4 times in one week, 8 times in another, etc.?    I have enough SQL skills to group the data, I just don't see where I can select it from...

 

Thanks!

 
  • Re: querying past alerts
    Ram Esakky

    Hi Chlsmith

     

    The Solarwinds Virtualization Manger admin guide here has string of search queries (refer page 123) that can help you to create your own search/report using queries.

     

     

    -Ram Esakky

    • Re: querying past alerts
      chlsmith

      Even with that, I don’t see how to pull Past Alerts.   For instance, I want to pull the number of times the Disk Latency Alert has been raised for the last 3 months, grouped by day.   I would expect to see something like:

       

      8/23                       12

      8/22                       1

      8/21                       14

      Etc.

       

      I don’t see where I can create such a query and pull that past history data.   Is there a field or attribute that I can pull?   Is that of type “VM” or “Container”, or something???

       

      Chris

      • Re: querying past alerts
        Ram Esakky

        Hi Chlsmith,

         

        Someone from the VMAN team should be helping you with this soon.

         

        -Ram Esakky

        • Re: querying past alerts
          Milan_Volejnik

          Chris, historical data is stored in alert_recs table, alert details (i.e. name, owner, etc.) are in workspace_file table.

           

          The command may look like this:

           

          select date_trunc('day', a.time_stamp), count(a.guid)

          from alert_recs a

          JOIN workspace_file as w ON a.alert_guid = cast(w.id as varchar)

          where

          w.name = 'VM Disk Latency'                             -- name of the alert

          and a.time_stamp > now() - interval '100 days'   -- set least date

          and a.raised = true

          and a.statechange = true

          group by date_trunc

           

          Run the SQL query directly over the database and you will get the results. To allow users to connect the VMAN database please see this thread.

           

          By default the alert_recs table contains one year historical data. You can change it in VMAN: modify months.to.keep.alerts property in Setup - System Properties menu .

           

          Please let us know if it fits your needs.

          • Re: querying past alerts
            chlsmith

            So, how would you suggest that I get to the database to run such a query?

            • Re: querying past alerts
              Milan_Volejnik

              Here's one of possible solutions:

              1) create a file (rwx permissions for root) containing this script:

               

              SQL query

              psql -d hyper9db -U h9pgsu -H -o /home/VM_Disk_latency_results_`date +"%Y_%m_%d"`.html -c "

              select date_trunc('day', a.time_stamp), count(a.guid) as Alert_raised

              from alert_recs a

              join workspace_file as w ON a.alert_guid = cast(w.id as varchar)

              where

              w.name = 'VM Disk Latency'                              -- alert name

              and a.time_stamp > now() - interval '100 days'          -- days back

              and a.raised = true

              and a.statechange = true

              group by date_trunc"

              -this file creates an HTML file in /home folder named "VM_Disk_latency_results_YYYY_MM_DD.html". Example of output file is here:

               

              2) run the file manually or copy it into /etc/cron.monthly folder to get results on monthly basis (or create your own schema in /etc/crontab).

              • Re: querying past alerts
                chlsmith

                Are you saying to do all of this from the console of the server, or is there something within the VMan webpages that will get me here?

                • Re: querying past alerts
                  Milan_Volejnik

                  There's an option to create your own Python reports (in /data/var/hyper9/data/content/reports folder, reports are available in Reporting - On Demand Reporting menu). I tried to create one but unfortunately no success.

                   

                  Small workaround - if you change 1st line of the script to

                  psql -d hyper9db -U h9pgsu -H -o /usr/share/tomcat/webapps/swvm/VM_Disk_latency_results_`date +"%Y_%m_%d"`.html -c "

                  then you can see results at http://VMAN_IP_addr/swvm/VM_Disk_latency_results_YYYY_mm_dd.html.

                  • Re: querying past alerts
                    chlsmith

                    So you’ve told me that there’s this place I can create reports under “On-Demand Reporting”, and the Administrator Guide says VMan “has an interface to create ad-hoc reports in the field,” but for the life of me, there’s no option for me to do this.   I’m logged in with the main administrator user and I don’t see it anywhere.   I see nothing but canned reports and the buttons to run them.   I’m yet to see the screen you wrote your query in.

                     

                    • Re: querying past alerts
                      Milan_Volejnik

                      Sorry to mislead you. The reports are created from the console in /data/var/hyper9/data/content/reports folder. Once the Python report is created, you will see it in VMAN under "On-Demand Reporting" menu. I don't know any interface in VMAN for creation of such reports.

                       

                      As it's written in the Admin guide, "Reports are generated from direct connections with live data sources". The alert_recs historical data is stored in database. I wasn't able to make the connection to the database working.

                       

                      Hope it's clear now.