BIT9 PathNames

We live in a day and age when security (data, network, server, etc) is seemingly at the forefront of the daily news. In our quest to improve security we seemingly always find more and more products that are supposed to help with that security. Sometimes, those products require a data repository. As it happens, it is not uncommon for that repository to be in a database. Today, I am looking into a specific issue with the Bit9 product.

Why?

There are some very good reasons as a matter of fact. One really big reason is that I could find no decent information about this issue. The bigger reason is the seemingly indifferent level of response and the delays I saw in response from the Bit9 support channels.

In this specific case, the first response from their support channels took more than two days. Subsequent responses were more than a week later. For the client, this actually caused delays in a project they were working on. Was the issue significant? It was not an outage causing issue, but it was one that did seem to continue to grow and cause concerns with disk space.

The Issue

bit9It may be appropriate to discuss what Bit9 does prior to breaking into the details about the issue. If you have never had any experience with this product before, you may be running a product from a competitor such as Symantec. Bit9 is a an endpoint protection tool from Carbon Black. This helps to prevent against malware and endpoint attacks. In short it is a security based tool to protect your computing enterprise.

Go ahead and click the image to link to their site if you wish to learn more about how it works. For me, the nitty gritty specifics on how it works is a bit outside the scope of this article.

In short, Bit9 records a record of every filename and every filepath for every client machine. It stores these in a database in SQL Server. This shouldn’t be too much of an issue. For one instance serving about 300 client machines we saw about 1.2 million paths being stored. One another instance with far fewer clients and far fewer actual paths on those clients (physically checked) we had 114 million paths stored in the database. The difference in size was 20GB vs ~160GB. When the server is installed with all defaults, you can imagine how this looked for this instance – a very bloated OS volume.

Investigating this issue from a database perspective, I looked to find what was consuming so much space in the database. To do that, I ran my tablespace script and discovered the following:

bit9_tablespacehl

A significant standout there with the pathnames table. Looking at the definition of the table I see something far more interesting and disturbing all at once.

pathnames_table

I see a table with three columns and two large string fields. Each of these fields has a non-clustered index on it. This may or may not be such a big problem (other than the fact that the size of the non-clustered indexes on this table are much larger than the data) except that each of the string fields is an exact duplicate of the other. That’s right. Within this table, the data is duplicated into this second string field and each field has its own index. Not only does it appear that I have a ton of duplicated data, it appears I have entirely useless indexes (neither had been touched for a read since the server had been up)

I inquired about this to the folks at Bit9, both from a design perspective and from an archival perspective. Absolute silence on the design (expected). The inquiry about archival (or purge) of non-essential data did fetch a response – albeit a painfully slow response. The basic question is: “Is it safe to purge or archive old or unnecessary data.” The response we received was “run this and we will tell you what to do next.”

Great, they sent a script to help determine the state of data within the database. I am not posting their script here. Suffice it to say that the script they sent was not very pretty. They query about 20 tables, union the results from those tables, then perform a not in operation to see how many of the pathnames are invalid. No problem. Executing the script did reveal the following:

orphan_result

There seems to be the problem. 95.89% of the rows being stored in the pathnames table are orphaned records! This is a bit of a problem. The software does not appear to manage removal of invalid paths. From here, I knew what the course of action needed to be and acted on it. A big piece of the equation was provided from the results of the script. Another piece was provided in the makeup of the script. From these pieces of information, I created a purge script to help manage the orphan problem. I then put that script into an agent job and set it to run on a weekly basis.

If you find yourself in the same boat, here is the script needed to purge the data. If running this, I recommend disabling the two non-clustered indexes and then performing the delete (especially if you sit at 96% orphaned and over 100 million rows). After deleting the mass amount of orphans, go ahead and rebuild the indexes to stay in compliance with the software contract until Bit9 responds about the index requirement and the schema of the table.

Yep! It is just like that. The worst part of the whole thing is actually the delete. The delete operation will touch about 37 indexed views to update and delete. Be prepared for a long running process if you have a situation like I encountered.

For kicks, here is what that plan would look like in one of my favorite tools (SQL Sentry Plan Explorer).

bit9_deleteplan

What is your pain scale and story?

pain_scale

4 Comments - Leave a comment
  1. Erik Darling says:

    That’s brutal. I was in a similar position with a vendor app used to examine file contents that used a huge list of NIST hashes to skip known files. GUIDs were stored like 5 different ways, and none of them were right.

  2. BJ says:

    Jason,

    The table contains 95% non-existent entries on purpose.

    That file inventory is kept for investigative purposes. If the FBI shows up and asks your customer if they’ve seen a particular binary in their network, even 6 months ago, that table is worth it’s weight in gold!

    That being said, I wouldn’t argue the quality of the indexing, you obviously know your stuff when it comes to SQL, but the customer can configure their system to only keep X days of 0 prevalence files in the database so they have a running history instead of an everlasting history.

    As you mentioned it’s a multi-day long query to whack the multi-hundred million record set but it’ll do it. Once thats done it’ll keep itself trimmed if the config is set.

    • Jason Brimhall says:

      One of the issues we found that I failed to mention is that the config was set to keep minimal history (60 days). These tables contained data from a borked setup that was more than a year old. They still have plenty of data in the tables. We just removed all of the orphaned records. If it is orphaned, it is essentially corrupt data. If it is needed for investigative purposes, the application needs to maintain the relationship information in the other tables to prevent it from becoming orphaned. Really it speaks to several things, design issues, data quality issues and then performance issues.

Leave a comment

Your email address will not be published. Required fields are marked *










Calendar
August 2016
M T W T F S S
« Jul   Sep »
1234567
891011121314
15161718192021
22232425262728
293031  
Content
SQLHelp

SQLHelp

  • @live_sql: #sqlhelp Is there a way to allow users attach DB's using the GUI and them not being SA. I crested custom server roles but still no GUI?
  • @joachimpr: Are your reports at work always taking forever to show you data? This could be the reason why! #SQLServer #sqlhelp
  • @NickyvV: Did anyone ever find a solution for memory consumtion of PP in Excel 2010 that @Kjonge made for 2013 and SSAS? #sqlhelp #ssashelp
  • @Ranjeeth: Can I have DBSer01, DBServ02 having replicas in DBServ03(2 AGs, diff clusters one target). #sqlhelp #alwayson trying to save testing time.
  • @jdanton: @Sir_NiN_Sir both free, but dev=enterprise, feature wise, express, much more limited. For learning, I’d go dev, or just use Docker. #sqlhelp
  • @Sir_NiN_Sir: How's different MSSQL Developer edition and Express edition? both edition for free licensed or not? If I need to practice t-sql.#sqlhelp
  • @jdanton: @Spinner_ Nope. Added a bunch of features to it, as it of SP1 2016. #sqlhelp
  • @Spinner_: #sqlhelp are Microsoft's deprecating sql server express?
  • @SQLHA: @ke0mms Would need to know about your issue before I could even think about giving an answer. #sqlhelp
  • @SQLHA: @ke0mms What do you mean by availability server? That's not a "thing" :) #sqlhelp

Welcome , today is Wednesday, July 26, 2017