This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

querying past alerts

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!

  • 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

  • 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

  • Hi Chlsmith,

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

    -Ram Esakky

  • 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.

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

  • 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:

    pastedImage_8.png

    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).

  • 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?

  • 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.

  • 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.

  • 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.