10 Replies Latest reply on Feb 27, 2009 10:15 AM by Pål

    Storing monitor results in SQL Server?

       Hi,

      I'm currently trialing ipMonitor and like what I see, but I need the ability to perform my own reports and queries on the results returned by the monitors for our internal reporting.  Is it possible to configure ipMonitor to store its data into a SQL database for historical reporting?

      If this isn't possible does anyone know if can we interrogate ipMonitor directly via Excel?

      Thanks.

      Marcus

        • Re: Storing monitor results in SQL Server?
          chris.lapoint

          This is not currently possible with ipMonitor.   If that type of historical reporting is critical to your organization, you might want to check out Orion instead http://oriondemo.solarwinds.com/Orion/SummaryView.aspx?viewid=119&netobject=

          • Re: Storing monitor results in SQL Server?

            That can be done through their SOAP interface. I have a small Windows Service that polls the monitors every 30 seconds and stores them to SQL. It works great.


             


            dlesko

              • Re: Storing monitor results in SQL Server?
                chris.lapoint

                To prioritize this request, I'd like to better understand your use-cases and environment.   Can you help  me out by answering the following questions?

                1. Which monitor types are you using in your environment?

                2. How many monitors do you have deployed?

                3. What specific reports are you trying to pull that you're not able to do with the built-in reporting?

                  • Re: Storing monitor results in SQL Server?

                    Hi Chris,

                    this is something we would be interested in as well.....

                    1. Which monitor types are you using in your environment?

                    We use a wide range of monitors

                    2. How many monitors do you have deployed?

                    at this stage we have 780 monitors on 300 odd devices, across two countries

                    3. What specific reports are you trying to pull that you're not able to do with the built-in reporting?

                    Basically we would leverage our existing Business Objects reporting options and utilise the Web Intelligence reporting from within business objects, which allow users to create their own reports on the fly. This gives the business greater flexibility in reporting and users are not constrait to built in reports or reports which 'IT' create. If you create the Web Intelligence "universe" correctly your reporting options are endless, once the 'universe' is created it removes the need for IT to create reports on request, as users simply create their own.

                     Hope this helps.........

                    Shane 

                • Re: Storing monitor results in SQL Server?

                  We are storing monitors that are entering down state to a sql server via a .vbs script that is executed by an external process action. We are also triggering tracert for some wan and firewall ping monitors on different remote sites in order to get a clue on where the fault could be at the very moment of the failure.

                  The sql data is published to an asp site where technicians can mark the alert as "faultfinding in progress" and later on clear the alert and attach a note to it with a description containing the cause and solution. If the same monitor alerts again at any time you can instantly see a history of that monitor and if you are lucky also the solution and which technician that can be blamed for not fixing it permanently :)

                  This works great for us since we are about 40ppl handling the alerts and its generating some sort of knowledge base.

                  The same page are also displaying montors currently in warning, down and lost state.

                  • Re: Storing monitor results in SQL Server?
                    msthursday

                    We're also very intersted in this.  At the moment we need to report service availability at the group level instead the monitor level and the built in reports don't give us that flexibility.  We're currently using about 1000 counters, including SQL, FTP, Exchange, Windows services, ping, disk space, and SNMP counters.

                    Thanks,

                    Amanda

                    • Re: Storing monitor results in SQL Server?

                      Ok here goes, this is my quick and dirty solution I have a similar script in order to create a tracert log in the event of a WAN link failure. Im not going to post the .asp code for the website here but I have uploaded a screen shots so that you can get the general idea.

                       

                      SQL database

                      Create a SQL database named ipMonitorAlerts, add the table AlertTable with the following fields(in this order):

                      monitorid,datetime,monitoraddr,monitorname,monitortype,monitorstatus

                      Create a login for the db.

                       

                      .vbs script

                      Create a directory for the script (C:\ScriptDIR in this example) and add the following code into a file named DBwriter.vbs

                      Function writeSQL(writeData)
                       strConnection = "Driver={SQL Server};Server=SQL-SERVER.DOMAIN.COM,1434;Database=ipMonitorAlerts;Uid=ipMonWriter;Pwd=StrongPasswordHere;"
                       Set conn = CreateObject("ADODB.Connection")
                       conn.Open strConnection
                       conn.Execute("INSERT INTO AlertTable(monitorid,datetime,monitoraddr,monitorname,monitortype,monitorstatus) VALUES ('" & writeData(0) & "','20" & writeData(1) & "','" & writeData(2) & "','" & writeData(3) & "','" & writeData(4) & "','" & writeData(5) & "')")
                       conn.Close
                      End Function

                      Function stripQuotes(strWords)
                       stripQuotes = replace(strWords, "'", "''")
                       stripQuotes = replace(stripQuotes, ";", ":")
                      End Function

                      Function killChars(strWords)
                       Dim badChars
                       Dim newChars

                       badChars = array("select", "drop", "--", "insert", "delete", "xp_")
                       newChars = strWords

                       For j = 0 to uBound(badChars)
                        newChars = replace(newChars, badChars(j), "")
                       Next
                       killChars = newChars
                      End Function

                      If WScript.Arguments.Count < 1 Then Wscript.quit

                      theList = Split(Wscript.Arguments(0),"^")

                      If UBound(theList) <> 5 Then Wscript.quit

                      'Prevent SQL injection
                      For i = 0 to Ubound(theList)
                       theList(i) = stripQuotes(theList(i))
                       theList(i) = killChars(theList(i))
                      Next

                      writeSQL(theList)

                       

                      ipMonitor Alert

                      Add an Alert with an external process action with the following settings:

                      Executable Name: cscript.exe

                      Directory: c:\windows\system32

                      Startup Directory: C:\ScriptDIR

                      Failure command line:

                      C:\ScriptDIR\DBwriter.vbs "%monitorid%^%date% %time%^%monitoraddr%^%monitorname%^%monitortype%^%monitorstatus%"

                       

                      I hope you can get this to work