Problems Logging In to Squil and Squert

[J B]

I'm having some issues with our current SecurityOnion master server;

When logging into Squert, the browser just spins with "Connecting" and I never get the interface.

Connecting to the Sguil server results in "Unable to connect to localhost on 7743".

When I run sostat, it freezes when it gets to "Sguil Uncategorized Events" and never completes. If I hit CTRL+C it'll abort the query, but then freeze on the next one, "Sguil events summary for yesterday" CTRL+C and we next freeze at "Top 50 All Time Sguil Events" If i hit CTRL+C again, the sostat command completes.

Running sguil-db-purge seems to hang at the event table cleanup.

The sguild.log shows no errors, and shows it is adding autocat rules to events.

I've tried purging the db, restarting the server, restarting apache, etc. with no luck. I can login to Squert after the Apache restart, but it never loads events.

Any pointers on how to resolve this is appreciated!

[J B]

Forgot to mention that Squert will revert back to the previous behavior after a while.

[Wes Lambert]

Without running sostat, it's a little hard to pinpoint the exact issue.  How long are you retaining events in securityonion_db?

Did sguil-db-purge complete successfully?  Have you tried modifying DAYSTOKEEP in /etc/nsm/securityonion.conf and re-running it?

How much CPU/RAM are you using?

Have you tried running "sudo mysqlcheck -c securityonion_db"?

You may want to try running sostat-redacted again to see if it will complete, even though it may take a while.  If it completes, please attach the output as a text file or use a service like Pastebin.com to share it with us.

Thanks,
Wes

On Nov 29, 2016 3:35 PM, "J B" <t0ta...@gmail.com> wrote:

Forgot to mention that Squert will revert back to the previous behavior after a while.

--
Follow Security Onion on Twitter!
https://twitter.com/securityonion
---
You received this message because you are subscribed to the Google Groups "security-onion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to security-onion+unsubscribe@googlegroups.com.
To post to this group, send email to security-onion@googlegroups.com.
Visit this group at https://groups.google.com/group/security-onion.
For more options, visit https://groups.google.com/d/optout.

[J B]

> Did sguil-db-purge complete successfully?  Have you tried modifying DAYSTOKEEP in /etc/nsm/securityonion.conf and re-running it?

I set DAYSTOKEEP to 3 and ran sguil-db-purge. It completed repair on data tables just fine but it's been sitting at "event table exists, dropping old tables and repairing recent tables." for a few hours now.

> How much CPU/RAM are you using?

The system has four cores and 20 GB RAM. It hasn't come anywhere near exhausting this although I've seen the CPU at 100% for a period of time, then it drops.

> Have you tried running "sudo mysqlcheck -c securityonion_db"?

The last time I ran it this reported OK for everything. Will try it again if the db purge ever completes.

> You may want to try running sostat-redacted again to see if it will complete, even though it may take a while.  If it completes, please attach the output as a text file or use a service like Pastebin.com to share it with us.

Will try that again. Waiting to see if the purge ever finishes.

[J B]

UPDATE: The db purge has never completed. Waited on it all day, still sitting at "event table exists, dropping old tables and repairing recent tables."

[Wes]

On Wednesday, November 30, 2016 at 3:21:36 PM UTC-5, J B wrote:
> UPDATE: The db purge has never completed. Waited on it all day, still sitting at "event table exists, dropping old tables and repairing recent tables."

JB,

Are you able to run the following MySQL query?

sudo mysql --defaults-file=/etc/mysql/debian.cnf -Dsecurityonion_db -e "SELECT COUNT(*) FROM event WHERE status=0"

If so, how many uncategorized events do you have?

You may also want to look at:

https://taosecurity.blogspot.com/2013/02/recovering-from-suricata-gone-wild.html

Have you tried running mysqlcheck again?

Thanks,
Wes

[J B]

I've tried the first query, and started walking through the link but both queries just hang and never seem to complete.

[J B]

Restarting SQL allowed the queries to run, I only had around 900 or so uncategorized events, but the db-purge is now hanging agian.

[Wes]

On Thursday, December 1, 2016 at 2:47:56 PM UTC-5, J B wrote:
> Restarting SQL allowed the queries to run, I only had around 900 or so uncategorized events, but the db-purge is now hanging agian.

JB,

Would you be able to attach the output of sguil-db-purge.log?

What is the output of the following?

ls -alt /nsm/elsa/data/elsa/tmp/buffers/* | wc -l

Thanks,
Wes

[J B]

sguil-db-purge.log from the run yesterday;

Thu Dec 1 05:01:01 UTC 2016
Retention policy set to 3 days (deleting data prior to 20161128).
Repair policy set to 7 days (repairing tables back to 20161124).
Uncat policy set to 3000 uncategorized events (categorizing events until we get down to 3000).
Stopping: securityonion
* stopping: sguil server (not running)[ WARN ]
There are 2942 uncategorized events, which does not exceed the max of 3000.
data table exists, dropping old tables and repairing recent tables.
securityonion_db.data_SCOINK01-eth1-1_20161128 repair status OK
securityonion_db.data_SCOINK01-eth1-2_20161128 repair status OK
securityonion_db.data_SCOINK01-eth1-3_20161128 repair status OK
securityonion_db.data_SCOINK01-eth1-4_20161128 repair status OK
securityonion_db.data_SCOINK01-eth1-5_20161128 repair status OK
securityonion_db.data_SCOINK01-eth1-6_20161128 repair status OK
securityonion_db.data_SCOINK01-eth1-7_20161128 repair status OK
securityonion_db.data_SCOINK01-eth1-8_20161128 repair status OK
securityonion_db.data_SCOINK01-eth1-9_20161128 repair status OK
securityonion_db.data_phoink1-eth1-3_20161128 repair status OK

event table exists, dropping old tables and repairing recent tables.

It stops at that last line and never completes.

The output of ls -alt /nsm/elsa/data/elsa/tmp/buffers/* | wc -l returns the number 3.

[Wes]

JB, are you still experiencing issues with this? What happens if you lower DAYSTOKEEP to "1" and run sguil-db-purge?

Thanks,
Wes