13 Replies Latest reply on Jun 3, 2016 10:35 AM by curtisi

    LEM Database

    edwardpoll

      Can you get access to the LEM database to do direct SQL queries?

       

      Ever since upgrading to 5.7 from 5.4 (via 5.6), the reporting has been unusably slow (if it works at all, yes there is a call open but its been weeks now and I'm struggling to get a response with any meaning), To be honest it'd be a damn site easier just to fire the SQL query directly at the DB.

       

      Any thoughts?

        • Re: LEM Database
          curtisi

          Edward, this article from the KB details what it takes to make a JDBC connection to the LEM database:

           

          SolarWinds Knowledge Base :: Creating a Custom Report

           

          Update June 02 2016: In light of the new Success Center links, here's the new path to that KB:

           

          Create Custom Reports Using Crystal Reports - SolarWinds Worldwide, LLC. Help and Support

           

          Perhaps that can help you run queries directly?

            • Re: LEM Database
              edwardpoll

              This is what we've followed and whilst this allows you to connect to the database the tables all appear empty when you do. There is either something serious wrong with our install or there is some thing special the reporting tool does, to be able to see the data...permissions or a secondary connection maybe, but I can only find the contego username for the connection.

               

              Any ideas on other dbadmin accounts?

               

              Ed

                • Re: LEM Database
                  curtisi

                  We've never supported trying to access the database directly, but:

                   

                  1.  Open the Properties of the Reports shortcut

                  2.  At the end of the Target: line, outside the quotes, add /L.  The line should end like this: SolarWinds Log and Event Manager Reports\SWLEMReports.exe" /L

                  3.  Open Reports and run the report again.

                  4.  Navigate to the Reports application directory.  There should now be a SWLEMReports.log file.

                   

                  Maybe that log can give you some insight into what the Reports are doing?

                    • Re: LEM Database
                      edwardpoll

                      I've done all that....using a jdbc connection and running the sql directly doesn't return results, neither does the report application. The log created with /L does show the application doing something to convert/populate "tables" presumably temp ones, doesn't help though as its application output rather than direct sql. The only SQL it shows is he sql I have written into the report. Ndepth however is fine when the same query is run......I have a call open but not getting anywhere fast..

                       

                      Are there any other users that can connect to the database directly? contego doesn't appear to have enough permissions to get the data on its own. I appreciate that it's not fully supported but anything is worth a try from our point of view.

                       

                      Thanks

                       

                      Ed

                       

                      Sent from my HTC

                        • Re: LEM Database
                          edwardpoll

                          Interestingly if I try a connecting to the LEM DB remotely;

                           

                                         1. Using the hsqldb-2.3.1.jar lem_lucius.jar and lem_util.jar as the driver libraries.

                                         2. Connecting to jdbc:hsqldb:hsql://%LEMHOSTNAME%/alertdb using the contego username and password

                           

                                         I successfully establish a connection BUT the tables all appear empty apart from public.eventsummary, public.alertpath, public.alertdatetimerange,public.internalrules, public.alertdailykeys.

                           

                          IF we run the report engine in debug mode I can see the log file shows the report engine making reference to both the AlertDB database (as above) but also the AlertDBLucius database - trying to establish a database connection to AlertDBLucius with the same methods above, fails with "unknown db alias" errors.

                           

                          The Report engine is clearly doing something in its pre-query stage that allows it to access the tables and their content.

                           

                          Any pointer on what that might be would be greatly appreciated.

                           

                          Thanks


                          Ed

                            • Re: LEM Database
                              lspencer@aafcu.com

                              Did you ever get anywhere with this?  Mine does this with version 6.1.0.  I tried opening a case with support when I had 6.0.1 and all they did was wait a few weeks, make me explain it again, wait a few more until I got so busy I could keep explaining it to them.

                                • Re: LEM Database
                                  drodinthe559

                                  Have you gotten anywhere on this? I've tried using the JDBC drivers with Crystal Reports 2014 and  get the same results. I see tables fields but no data.

                                    • Re: LEM Database
                                      sejod2004

                                      Poke.

                                      I am trying to do the same thing. Their reports take way too long to run. We need direct access to the DB. Getting no where with support.

                                      For an appliance that reporting is such a crucial part, it doesnt make it easy to filter or create custom reports.

                                      Tried running one report and 4 hrs later still running. What would i do during an audit? Tell them to come back next week?

                                       

                                      If we cant get direct access or if reporting doesnt become more robust we will buying something else.

                        • Re: LEM Database
                          graydebowen

                          I am in the same situation.  The knowledge base link from 'curtisi' above is now dead.  Might we clarify what we all can do from the SolarWinds Log and Events Manager reports engine?

                           

                          Can do:

                          There is a list of pre-configured LEM reports.  These can be run with user defined start and stop times or with TOP 'n' entries.  Some reports can take a while and some may have no data but is not the issue here.  These 'built in' reports will access the LEM database and return data (if there is any in the database)

                          The graphs of the TOP reports are bar charts.  The chart format cannot be changed.  There is no option for  a pi or line chart

                          A timed report such as 'user log on failure' produces a tabular output.

                          Any of the built in reports can be scheduled to run and saved to a user specified location.

                           

                          A built in tabular report can be modified by 'select expert' this allows fields to be chosen and filtered by some value.  For example we can filter 'log on failure' for user name does not exist.

                           

                          Cannot do:

                          Would someone clarify that the following are not possible through the reports engine.  If they can be I would be grateful to know where to start with sorting them.

                           

                          The 'select expert' option does not allow columns to be removed from reports.  For example the 'log on failure' report has 17 columns resulting in a print view header section that has 2 rows of titles.  Cutting the number of columns to 5 or less could still include the key information that is needed but would make the report much easier to analyse.

                           

                          Any report that is modified with 'select expert' cannot be scheduled through the LEM reporting engine.  Only basic built in reports with default parameters can be scheduled.

                           

                          Scheduled built in TOP reports create a pdf with a nice chart on the initial page and with the underlying data on following pages of the pdf (there could be 100 or more of these pages).  It is not possible to schedule creating just page 1 of a report.  The report could be split when created but the reporting engine will not schedule this.

                           

                          The graphical and text reports produced manually by LEM using nDepth cannot be scheduled.

                           

                          Summary:

                           

                          If LEM cannot schedule the reports that we may want can we access the underlying database and run our own SQL? 

                           

                          Is the data held in MS SQL and in the clear.  I should like to run SQL and output the data.  I would look to do this at first through the SQL Server management interface.  Having proved that I can get the data that I want I would write php scripts to run the SQL then write the output as CSV files.  The php scripts would run with Windows task manager.  The resulting files would be passed onto management in Excel for ease of viewing.  I have done this with other MS SQL databases so the plan will work if the SQL queries produce meaningful data.

                           

                          I need clarification of what LEM is capable of producing in scheduled reports before moving onto to the underlying database.  With correct user access permissions (SELECT is all that is needed) a user should be able to get information and there is no way that the data itself will be affected.

                            • Re: LEM Database
                              nicole pauls

                              The data stored in LEM is not in a relational database. The LEM reports engine artificially uses SQL by virtue of pre-loading your selected timeframe into a small, temporary, relational database. You have to run reports from LEM reports because all of this pre-loading, selecting, and sorting is done via that communication, not via SQL. Unfortunately, there isn't really a way to query the entire LEM database directly with something like SQL.

                               

                              You're right that you can't edit the report template; the only way to edit the report template (columns, etc) is with Crystal Reports itself, which is what LEM reports is based off of. That's complicated and often frustrating to use, but potentially doable.

                               

                              You CAN filter a report with the select expert and run that as a custom report. Filter your report, save it (even with data) into your Custom Reports folder, then run that report from Custom Reports (it'll ask for a new timeframe but your filter data will be saved).

                               

                              The "Event Summary" reports are the reports that ONLY have graphs without the following pages, so it's kind of like getting the first page of the report, but you're right that there's no "summary" reports that are identical to every report. There's not really a way (short of editing with Crystal Reports) to cut the detail from the graphs.

                              • Re: LEM Database
                                curtisi

                                Select Expert will not allow you to change the layout/view of a report, so no removing or adding or rearranging columns with the LEM Reports console.

                                 

                                You can run modified reports on a schedule, though.

                                 

                                1. Pick a stock report to start with, run it for some time-range.  Shorter is better, so long as it includes some sample(s) of what you want in the final report
                                2. Use Select Expert to filter the report down to the samples you care about
                                3. Export that Report (there's a button in the ribbon) and save in the Crystal Reports RPT format.  You'll want to save the file where you installed the LEM Reports console in the Custom Reports folder.  By default, that path is:
                                  C:\Program Files (x86)\SolarWinds Log and Event Manager Reports\CustomReports
                                4. In the Reports console, in the Category drop down, pick "Custom Reports."  Hit F5 to force a refresh.  You should see your new report there.
                                5. Run your report as usual, and schedule the report as you would any other.

                                 

                                In Windows XP, it was simple to modify the file meta-data to change the Report title, but that feature seems to be missing in Windows 7/8/10.  Give the Report a reasonable file name, though, and you can expand the "File" column to make sure you have the right report.

                                  • Re: LEM Database
                                    graydebowen

                                    Thanks for the feedback.  I have run through the instructions from curtisi:

                                    The custom report and schedule broadly works as described:

                                     

                                    The default path to save the new custom report (from the filtered export) is in documents of the home folder.  I copied the files across to C:\Program Files (x86)\SolarWinds Log and Event Manager Reports\CustomReports and refreshing the custom reports window brought these up.  They could then be scheduled like any other report.

                                     

                                    I have done this with 2 reports, one on log on failures and another on authentication.  When these new custom report *.rpt files are run by loading them in the report viewer, then they are 1 or 2 pages long for a day's worth of data.  When run through the scheduler these same reports are 120+ pages long.  There are very few options to choose when scheduling a report.  Basically when to run it, what to do with the output and how long a time scale to collect data for.  It appears that the filtered parameters that I added and which must be saved in the *.rpt document (as they kick in when the report is opened manually) are not being read by the scheduling program (which is pretty much the built in Windows task scheduler).  Am I missing something in the set up?

                                      • Re: LEM Database
                                        curtisi

                                        You may need to open a Support ticket to have them look at what is happening.

                                         

                                        One thing to try:

                                         

                                        Edit your Reports short-cut so that the target ends with a /L, like so:

                                         

                                        "C:\Program Files (x86)\SolarWinds Log and Event Manager Reports\SWLEMReports.exe" /L

                                         

                                        Now, run the stock report that you based your report on.  You should have a log file pop-up, and if it doesn't, it'll be in the Reports install directory as SWLEMReports.log.  In that log, you'll see the query the Reports console ran, something like:

                                         

                                        SELECT "GENERICALERT_1"."EVENTINFO", "GENERICALERT_1"."DETECTIONIP", "GENERICALERT_1"."SEVERITY", "GENERICALERT_1"."PROVIDERSID", "GENERICALERT_1"."ALERTID", "GENERICALERT_1"."MANAGERTIME", "UNIQUENODEIDENTIFIER_1"."NAME", "INTERNALUSERLOGOFF_1"."SOURCEMACHINE", "INTERNALUSERLOGOFF_1"."INTERNALUSERNAME", "INTERNALUSERLOGONFAILURE_1"."SOURCEMACHINE", "INTERNALUSERLOGONFAILURE_1"."INTERNALUSERNAME", "INTERNALUSERLOGON_1"."SOURCEMACHINE", "INTERNALUSERLOGON_1"."INTERNALUSERNAME"

                                        FROM   "PUBLIC"."PUBLIC"."GENERICALERT" "GENERICALERT_1", "PUBLIC"."PUBLIC"."INTERNALUSERLOGON" "INTERNALUSERLOGON_1", "PUBLIC"."PUBLIC"."INTERNALUSERLOGONFAILURE" "INTERNALUSERLOGONFAILURE_1", "PUBLIC"."PUBLIC"."INTERNALUSERLOGOFF" "INTERNALUSERLOGOFF_1", "PUBLIC"."PUBLIC"."UNIQUENODEIDENTIFIER" "UNIQUENODEIDENTIFIER_1"

                                        WHERE  ("GENERICALERT_1"."UUID_"="INTERNALUSERLOGON_1"."UUID_") AND ("GENERICALERT_1"."UUID_"="INTERNALUSERLOGONFAILURE_1"."UUID_") AND ("GENERICALERT_1"."UUID_"="INTERNALUSERLOGOFF_1"."UUID_") AND ("GENERICALERT_1"."INSERTIONIP"="UNIQUENODEIDENTIFIER_1"."UNIQUEID") AND ("GENERICALERT_1"."MANAGERTIME">={ts '2016-05-27 07:00:00'} AND "GENERICALERT_1"."MANAGERTIME"<{ts '2016-06-03 09:29:03'}) AND ("GENERICALERT_1"."ALERTID"=N'InternalUserLogoff' OR "GENERICALERT_1"."ALERTID"=N'InternalUserLogon' OR "GENERICALERT_1"."ALERTID"=N'InternalUserLogonFailure')

                                        ORDER BY "GENERICALERT_1"."ALERTID", "GENERICALERT_1"."MANAGERTIME"

                                         

                                        (It's ugly)

                                         

                                        Now, run your custom report...is the query different?  Do you see any conditions that match your filters?

                                         

                                        In the scheduled task in Windows, you'll see that the task runs an INI file (it's one of the arguments on the "Actions")

                                         

                                        "C:\Program Files (x86)\SolarWinds Log and Event Manager Reports\SchedINI\RPT20030261-1.ini"

                                         

                                        If you open that INI, the first section has a "Filename" property.  Is this the right file for the Report you're trying to run?