krypted.com

Tiny Deathstars of Foulness

You can grant access to certain columns to view in SQL without providing access to specific users to see the whole database. This is pretty useful when delegating reporting to users, without giving them access to all of the data in your database. For example, a user might be able to see a column with an address, but not a column with a credit card number, increasing database security while allowing you to delegate certain tasks when appropriate. In this article, we’ll use the same “Customers” table from our first articles, signupdate: ID Site Contact Address City Zip Country SignupDate 1 Krypted Charles Edge my house Minneapolis 55418 US 2005-01-01 2 Apple Tim Cook spaceship Cupertino 95014 US 2015-12-05 3 Microsoft Satya Nadella campus Redmond 98053 US 2014-11-01 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 2010-03-10 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US 2016-01-01 Next, we’ll create a view called SignupDate that only has customers that signed up on January 1st of 2005. This view returns the data set of contacts and signup dates: CREATE VIEW signupdate AS SELECT * FROM Customers WHERE OrderDate='2005-01-01'; The syntax is similar to a SELECT, but with CREATE VIEW followed by the name of the view and then AS followed by the SELECT statement. The view is a virtual table containing the output of the query rather than data. Once created, use use the signupdate view in a query: SELECT * FROM signupdate; This SQL statement returns the following results: 1 Krypted Charles Edge my house Minneapolis 55418 US 2005-01-01 You can also use the view to contain a query with just the columns you want, according to how you structure your query, thus granting access to specific columns, without granting access to all of the columns in a table.

March 14th, 2016

Posted In: SQL

Tags: , , , , , ,

Previously, we looked at the SQL SELECT and WHERE options to obtain output and then constrain what would be displayed in a query. The AND & OR operators are used to add additional logic so you can filter records based on more than one condition. Which is to say to search based on the contents of multiple columns within a single table. AND is used to show information if two different conditions are true and OR is used to show information if either condition is true. Below is a selection from the “Customers” table that showed in our first article an we will use it to run some SQL sorting statements using the ORDER BY keyword: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook spaceship Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US The following example SQL statement outputs customers from the country “US” AND the city “55418”, in the “Customers” table from above: SELECT * FROM Customers WHERE Country='US' AND Zip='55418'; The following SQL statement selects all customers from the city “Minneapolis” OR the city “Cupertino”, in the “Customers” table: SELECT * FROM Customers WHERE City='Cupertino' OR City='Minneapolis'; As the logic of your searches expands, you can combine AND and OR by nesting logic within parenthesis (who said pre-algebra would be useless?!?!). For example, the following SQL statement selects all customers from the country “US” AND the city must be equal to “Minneapolis” OR “Cupertino”, in the “Customers” table: SELECT * FROM Customers WHERE Country='US' AND (City='Minneapolis' OR City='Cupertino');

February 2nd, 2016

Posted In: SQL

Tags: , , , , , , , ,

Previously, we covered the SQL SELECT Statement, to pull data from a SQL database. Here, we’ll constrain our search for items that match a given string, or pattern using the WHERE clause to filter search results, rather than getting all of the records and parsing the output. The WHERE clause extracts records that fulfill a specified string and follows the general syntax as follows, replacing the word column with the name of the column in one of your tables and the word table with the name of a table that you’d like to search within: SQL WHERE Syntax SELECT column,column FROM table WHERE column operator value; Below is a selection from the “Customers” table that showed in our first article an we will use it to run some SQL sorting statements using the ORDER BY keyword: ID Site Contact Address City Zip Country 1 Krypted Charles Edge my house Minneapolis 55418 US 2 Apple Tim Cook spaceship Cupertino 95014 US 3 Microsoft Satya Nadella campus Redmond 98053 US 4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US 5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US The following SQL statement selects all the customers with the zip code (Zip column) matching “55418”, in the “Customers” table: SELECT * FROM Customers WHERE Zip=55418; In the above search, I didn’t have to quote what I was looking for. The reason is that an integer doesn’t require quoting; however, if we were searching for a name, or any other text record, we should use quotes. So to repeat the search for Site, looking for Krypted, we would use the following: SELECT * FROM Customers WHERE Site=Krypted; We used an = operator, but it’s worth noting that there are a number of others that can be super-helpful. The following operators are available when using a WHERE clause:
    • = Equal
    • <>  or != Not equal to
    • > Greater than
    • IN Indicates multiple potential values for a column
    • < Less than
    • >= Greater than or equal
    • <= Less than or equal
    • BETWEEN Between an inclusive range
    • LIKE Looks for a provided pattern
So using another operator from above, we can also search for all sites that do not contain Krypted, using the following: SELECT * FROM Customers WHERE Site!=Krypted; So far, we’ve looked at searching for exact matches. We can also fuzzy our logic up by looking for items that contain a pattern AND something else, using the LIKE operator in a WHERE clause to search for items that contain part of a pattern in a search. The syntax for a SQL LIKE is similar, it begins with the SELECT statement that we’ve used throughout these articles so far, and then continues with the WHERE and then a LIKE to define the pattern, as follows, replacing column with the name of one of your columns, table with the name of the table that column is in and pattern with the actual search you’re looking to run: SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern; The following SQL statement selects all customers with a City starting with the letter “M” with the % inside a single quote to show where the wildcard data is (in this case, anything that appears after the letter M in a city name): SELECT * FROM Customers WHERE City LIKE 'M%'; The following SQL statement selects all customers with a City containing the pattern “Park (e.g. “Menlo Park”): SELECT * FROM Customers WHERE Country LIKE '%Park%'; You can also add NOT in front of Like to search for records that do not match a pattern (note that you don’t have all the same operators available, so this helps to get a little more logic in searches when needed). The following SQL statement selects all customers with Country NOT containing the pattern “US” (of which there are none in our sample data set): SELECT * FROM Customers WHERE Country NOT LIKE '%US%'; Or if we weren’t sure that we wanted to work in a spaceship, we could search for all addresses that weren’t spaceship: SELECT * FROM Customers WHERE Address NOT LIKE '%spaceship%'; Overall, the SELECT statement has some pretty basic logic and operations, because searching for data within a given table is a pretty straight forward task, you have a pattern, you look for items that match that pattern. As these articles continue, we’ll get into slightly more complex operations, but much of the work that we do with SQL is done after a query with another tool in order to get our data to display or be manipulated in ways that SQL doesn’t do on its own. One of the beauties of SQL, and why it can be so fast, is that it’s simple.

February 1st, 2016

Posted In: SQL

Tags: , , , , , , , , , ,

To automate the process of exporting Exchange mailboxes to .pst files, we’re going to use the exmerge tool using a Windows batch file. We can use a MAILBOXES.TXT file to choose which mailboxes we’ll be using and possibly a SUBJECTS.TXT or ATTACHMENTS.TXT to constrain our searches. To run the exmerge, run as a typical GUI based merge but save the search. Once saved, you’ll be able to select a path, which we’ll call c:\tmpexmerge. In here, you should see an EXMERGE.INI as well as a MAILBOXES.TXT (and possibly a few other files. In the MAILBOXES.TXT file you’ll see the CN information for the mailboxes selected in the previous: /O=FIRST ORGANIZATION/OU=FIRST ADMINISTRATIVE GROUP/CN=RECIPIENTS/CN=CEDGE You can copy and paste this line to add others, changing the last CN entry (or other pathing information if in other OUs: /O=FIRST ORGANIZATION/OU=FIRST ADMINISTRATIVE GROUP/CN=RECIPIENTS/CN=WSMITH Once you’ve saved the file, edit the EXMERGE.INI file if needed (you usually don’t need to edit this one. Then run exmerge.exe using the command line: exmerge -F C:\tmpexmerge\EXMERGE.INI -B -D This takes awhile, and even longer if you’re actually searching for keywords or doing multiple mailboxes, but not as long as it would take to do things manually. .PST files are saved using the file path in the exmerge.ini. Watch Task Manager to make sure the backup is still running. The -D option above opens the GUI exmerge to show what’s happening (kinda’).

September 10th, 2013

Posted In: Microsoft Exchange Server

Tags: , , , , , , , , , ,