krypted.com

Tiny Deathstars of Foulness

I’ve written about SQLite databases here and there over the years. A number of Apple tools and third party tools for the platform run on SQLite and it’s usually a pretty straight forward process to get into a database and inspect what’s there and how you might programmatically interact with tools that store data in SQLite. And I’ll frequently use a tool like Navicat to quickly and visually hop in and look at what happens when I edit data that then gets committed to the database.

But I don’t always have tools like that around. So when I want to inspect new databases, or at least those new to me, I need to use the sqlite3 command. First, I need to find the databases, which are .db files, usually stored somewhere that a user has rights to alter the file. For example,  /Library/Application Support/My Product. In that folder, you’ll usually find a db file, which for this process, we’ll use the example of Data.db.

To access that file, you’d simply run sqlite3 with the path of the database, as follows:

sqlite3 /Library/Application\ Support/My\ Product/Data.db

To see a list of tables in the database, use .tables (note that a tool like Postgress would use commands like /tr but in SQLite we can run commands with a . in front and statements like select do not use those):

.tables

To then see a list of columns, use .schema followed by the name of a table. In this case, we’ll look at iOS_devices, which tracks the basic devices stored on the server:

.schema iOS_devices

The output shows us a limited set of fields, meaning that the UDID is used to link information from other tables to the device. I like to enable column headers, unless actually doing an export (and then I usually do it as well):

.headers ON

Then, you can run a standard select to see what is in each field, which in the below example would be listing all information from all rows in the myapptable table:

select * from myapptable;

The output might be as follows:

GUID|last_modified|Field3|Field4
abcdefg|2017-01-26T17:02:39Z|Contents of field 3|Contents of field four

Another thing to consider is that a number of apps will use multiple .db files. For example, one might contain tables about users, another for groups, and another for devices in a simple asset tracking system. This doesn’t seem great at first, but I’ve never really judged it, as I don’t know what kind of design considerations they were planning for that I don’t know. If so, finding that key (likely GUID in the above example) will likely be required if you’re doing this type of reverse engineer to find a way to programmatically inject information into or extract information out of a tool that doesn’t otherwise allow you to do so.

February 24th, 2017

Posted In: Mac OS X, SQL

Tags: , , , , , , , , ,

The Time Machine service in Mountain Lion Server hasn’t changed much from the service in Lion Server. To enable the Time Machine service, open the Server app, click on Time Machine in the SERVICES sidebar. If the service hasn’t been enabled to date, the ON/OFF switch will be in the OFF position and no “Backup destination” will be shown in the Settings pane.

Screen Shot 2013-10-06 at 9.12.24 PMClick on the ON button to see the New Destination screen, used to configure a list of volumes as a destinations for Time Machine backups. The selection volume should be large enough to have space for all of the users that can potentially use the Time Machine service hosted on the server. When you click the Choose button, a list of volumes appears in a standard Finder selection screen.

Screen Shot 2013-10-06 at 9.14.10 PMHere, click on the volume to save your backups to in the sidebar. In most cases the Backup destination will be a mass storage device and not the boot volume of the computer. Once selected, click Choose and then if desired, limit the amount of storage on the volume to be used for backups. Click Create and a share called Backups is created and the service will start. Don’t touch anything until the service starts. Once started, add a backup destination at any time using the plus sign button (“+”) and defining another destination.

Note: A new feature in Mavericks Server is allowing for multiple backup destinations using the Server app, as well as allowing administrators to manage backups using the Backups tab.

Time Machine Server works via Bonjour. Open the Time Machine System Preference pane and then click on the Select Backup Disk button from a client to see the server in the list of available targets, much as you would do with an Apple Time Capsule.

Screen Shot 2013-10-06 at 9.17.28 PMUnder the hood, a backup share is creating in the file sharing service. To see the attributes of this share, use the serveradmin command followed by the settings option and then the sharing:sharePointList:_array_id:<Path to backup target>, so for a path of /Volumes/New Volume 1/Shared Items/Backups use:

sudo serveradmin settings sharing:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups

The output indicates the options configured for the share, including how locking is handled, guest access disabled, generated identifiers and the protocols the backups share listens as:

sharing:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:name = "Backups"
sharing:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:smbName = "Backups"
sharing:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:nfsExportRecord = _empty_array
sharing:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:afpIsGuestAccessEnabled = no
sharing:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:isTimeMachineBackup = yes
sharing:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:dsAttrTypeNative\:sharepoint_group_id = "F4610C2C-70CD-47CF-A75B-3BAFB26D9EF3"
sharing:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:isIndexingEnabled = yes
sharing:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:mountedOnPath = "/Volumes/New Volume 1"
sharing:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:dsAttrTypeStandard\:GeneratedUID = "FAB13586-2A2A-4DB2-97C7-FDD2D747A0CD"
sharing:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:path = "/Volumes/New Volume 1/Shared Items/Backups"
sharing:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:smbIsShared = no
sharing:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:smbIsGuestAccessEnabled = no
sharing:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:afpName = "Backups"
sharing:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:smbDirectoryMask = "755"
sharing:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:afpIsShared = yes
sharing:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:smbCreateMask = "644"
sharing:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:ftpName = "Backups"
sharing:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:timeMachineBackupUUID = "844A1C43-61C9-4F99-91DE-C105EA95BD45"

Once the service is running, administrators frequently fill up the target volume. To move data to another location, first stop the service and then move the folder (e.g. using mv). Once moved, use the serveradmin command to send settings to the new backup path. For example, to change the target to /Volumes/bighonkindisk, use the following command:

sudo serveradmin settings sharing:sharePointList:_array_id:/Shared Items/Backups:path = "/Volumes/bighonkindisk"

Another way to see the share and attributes of the share is through the sharing command:

sharing -l

Which should show output similar to the following:

List of Share Points
name: Backups
path: /Shared Items/Backups
afp: {
name: Backups
shared: 1
guest access: 0
inherit perms: 0
}
ftp: {
name: Backups
shared: 0
guest access: 0
}
smb: {
name: Backups
shared: 0
guest access: 0
}

There’s also a Bonjour service published that announces to other clients on the same subnet that the server can be used as a backup destination (the same technology used in a Time Capsule).

One major update in Mavericks Server is the addition of the timemachine service in the severadmin command line interface. To see the command line settings for Time Machine:

sudo serveradmin settings timemachine

The output shows that share info is displayed as with the sharing service, but you can also see the GUID assigned to each share that is a part of the backup pool of storage:

timemachine:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:dsAttrTypeStandard\:GeneratedUID = "FAB13586-2A2A-4DB2-97C7-FDD2D747A0CD"
timemachine:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:smbName = "Backups"
timemachine:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:afpIsGuestAccessEnabled = no
timemachine:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:smbDirectoryMask = "755"
timemachine:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:afpName = "Backups"
timemachine:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:smbCreateMask = "644"
timemachine:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:nfsExportRecord = _empty_array
timemachine:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:path = "/Volumes/New Volume 1/Shared Items/Backups"
timemachine:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:smbIsGuestAccessEnabled = no
timemachine:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:name = "Backups"
timemachine:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:ftpName = "Backups"
timemachine:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:smbIsShared = no
timemachine:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:afpIsShared = yes
timemachine:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:timeMachineBackupUUID = "844A1C43-61C9-4F99-91DE-C105EA95BD45"
timemachine:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:isTimeMachineBackup = yes
timemachine:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:backupQuota = 0
timemachine:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:dsAttrTypeNative\:sharepoint_group_id = "F4610C2C-70CD-47CF-A75B-3BAFB26D9EF3"
timemachine:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:isIndexingEnabled = yes
timemachine:sharePointList:_array_id:/Volumes/New Volume 1/Shared Items/Backups:mountedOnPath = "/Volumes/New Volume 1"

Additionally you can also query for the service to verify it’s running using full status:

sudo serveradmin fullstatus timemachine

Which outputs something similar to the following:

timemachine:command = "getState"
timemachine:state = "RUNNING"

While I found plenty to ramble on about in this article, Mass deployment is still the same, as is client side configuration. One change that appeared in Mountain Lion is that the screen for the Time Machine Options on the client no longer has an option for managing Versions, as seen here.Screen Shot 2013-10-06 at 9.25.05 PM

October 22nd, 2013

Posted In: Mac OS X, Mac OS X Server

Tags: , , , , , , , ,

Working with Shadow Copy requires elevated privileges. I usually access Shadow Copy through vssuirun. This prompts for elevating privileges. Once open, use the Settings pane to select the volume you’d like to schedule backups to. Then choose how much space shadow copies can use. Click on the Schedule button to configure how frequently backups run. I usually try to time these things for when the server isn’t slammed. Otherwise you might run into issues.

By default, Shadow Copy keeps 64 versions of each file. Running snapshots every hour. You can restore easily, by selecting a volume, although volume-based restores are not supported on system derives. Restores can be done using vssuirun and then using the Revert Now… button.

The quicker way to do all this is to use the vssadmin command, which has a lot more options. Run vssadmin along with the list verb to see a list of different types of objects. For example, to see a list of the storage used with Shadow Copy, use the vssadmin command, with the list verb and then the shadowstorage noun:

vssadmin list shadow storage

You’ll then see the storage for each volume, along with the space used, allocated and max for each. Run vssadmin followed by the list verb then shadows to see your shadow copy sets:

vssadmin list shadows

Each shadow copy set is displayed along with a generated ID. Creation times, volume information, tarot location, name of server and the type (e.g. client accessible) are all displayed. You can also use the add verb with these same options, along with a variety of switches for each. To add storage /for the a drive (G) on a drive (H) and give it a maxsize (64GB) use the following:

vssadmin add shadowstorage /for=g: /on=H /maxsize=64GB

Once you’ve added Shadow Copy Storage for a volume, you can then run a manual shadow copy on an enabled volume using the create verb, along with the shadow noun and then the /for: option, specifying the volume:

vssadmin create shadow /for=g:

To revert to a shadow copy, and this is dangerous as you might not want to revert so be careful here, use the revert verb along with shadow (yes, it’s singular as there’s only one) and then the /shadow option followed by the GUID of the copy to revert to:

vssadmin revert shadow /shadow=(AAAAAAAA-BBBB-11111-2222-CCCCCCCC)

To delete a shadow copy, use the delete verb, along with the shadows noun (yes, that’s randomly plural) and then the /shadow option following by the GUID of the shadow copy to delete (yes, I made that GUID up):

vssadmin delete shadows /shadow=(AAAAAAAA-BBBB-11111-2222-CCCCCCCC)

Alliteratively, use favorite option for this verb /oldest which just tosses the oldest backup (less typing, I’m lazy):

vssadmin delete shadows /for=g: /oldest

This is interactive as well, so you’ll have to hit y to confirm. Finally, when disabling all shadow copies (holy shiznit batman, we’re out of space big time) use the delete verb but this time followed by the drive letter to clear copies for:

vssadmin delete shadowstorage /for:g:

September 18th, 2013

Posted In: Windows Server

Tags: , , , , , ,

Profile Manager allows you to leave certain fields that are user-centric blank and it will prompt at the time that the profile is installed for the blank information. These are usually user-centric fields, such as short name and password. You can also create a profile in Profile Manager for each user you want to setup mail, Exchange, iCal, Address Book and other services that are tied to a specific user. You can enter the username for each and leave the password blank and the user will be prompted for the password but have the username filled in. And then there are payload variables.

Note: Before we get started on Payload Variables, it’s worth noting that many did not work well prior to 10.7.3, most notably %email%.

Profile Manager provides a number of ways to configure accounts and settings on iOS based devices. When a user logs in, the user’s name, email address, title, phone number and both the short name and GUID of the user’s account are able to be substituted using variables. These variables have a % in front of and behind the name of the variable, making them easy to identify when looking at accounts. These can easily be put into a profile’s payload. When a user logs in the contents of the payload variable are replaced with the information for the account that logged in using the /MyDevices page in the web enrollment interface. When the enrollment profile is downloaded to the device, the variable is substituted with the user’s information from directory services (for user payloads) or from the device itself (for device payloads).

Using payload variables is a really straight forward process. First, create a profile by logging into the Profile Manager web interface (the name of the server followed by /ProfileManager. When prompted, provide the username and password for an administrative account.

Click on a group or user who you would like to configure a profile for.

From the profile screen, select the payload that you’d like to configure.

Enter the variable into the field(s) you’d like the substitution to occur in. For example, here I’m using a variable everywhere currently possible.

Note: You can wrap the variable with other text. For example, if you enter krypton.com/%short_name% then for a user of cedge the variable would expand as krypton.com/cedge, useful in doing Exchange configurations.

Variables available for use include user and device variables. These user variables are as follows:

  • %email% – The email address (the EMailAddress attribute)
  • %first_name% – The first name (the FirstName attribute)
  • %full_name% – The full name (the RealName attribute)
  • %guid% The guid (the GeneratedID attribute)
  • %last_name% – The last name (the LastName attribute)
  • %job_title% The job title (the JobTitle attribute)
  • %mobile_phone% The mobile number (the MobileNumber attribute)
  • %short_name% The short name (the RecordName attribute, typically the name of the account )

The device variables are as follow:

  • %BuildVersion% – Full OS version on the device
  • %ICCID% – ICCID (from the SIM card)
  • %IMEI% – IMEI (International Mobile Equipment Identity)
  • %OSVersion% – Common version number of the device’s OS
  • %ProductName% – Product name
  • %SerialNumber% – Serial number
  • %WIFIMAC% – MAC address of the WiFi interface

There are also 802.1x variables, which include the following:

 

  • %AD_ComputerID%
  • %AD_Domain%
  • %AD_DomainForestName%
  • %AD_DomainGuid%
  • %AD_DomainNameDNS%
  • %AD_KerberosID%
  • %ComputerName%
  • %HardwareUUID%
  • %HostName%
  • %LocalHostName%
  • %MACAddress%
  • %SerialNumber%

 

March 26th, 2012

Posted In: iPhone, Mac OS X, Mac OS X Server, Mac Security, Mass Deployment

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

The 10.7.2 to 10.7.3 update for Lion Server has introduced a few issues in some environments that I’ve seen. It just so happens that the update corrects a lot of behavior with Lion Server while also introducing new features, so it’s something you’re gonna’ need to do eventually. Therefore, before I update, I would strongly recommend backing up all of your services, your service data and Open Directory.

Once you’ve run the 10.7.3 update, there are a few things that I’ve seen happen. The first is that the web server won’t start. If this happens, reset the web server back to factory default:

serveradmin command web:command=restoreFactorySettings

Once it’s reset, you should be able to import any data that was backed up before and get things back to normal. The second is calendar data. On a few different systems I’ve seen users have to nuke iCal and then reimport data. To nuke and pave iCal, see this post: http://krypted.com/mac-os-x-server/nukepave-ical-server-in-lion-server. Once iCal Server has been restored to full working order (after the last step in that article) you can use psql to restore your data from the location of your backups (here called /backup/caldav.sql):

psql -U _postgres -d caldav -f /backup/caldav.sql

There’s also a script located in /usr/share/caldavd/lib/python/calendarserver/tools
called fix calendardata.py that can be used to scan and possibly fix any issues with the data itself. If that doesn’t not work though, you may be starting over. The script does not give root execute permissions by default and so you will need to chmod it to provide execute and then run it.

If you nuke CalDAV and you nuke OD and then restore them both, the GeneratedUIDs can be mismatched. Use the Directory Editor in the new Directory Utility to browse users and attach the GeneratedUID back to the correct entry in CalDAV. To locate all of the entries in CalDAV, run:

psql -U _postgres caldav -c “select * from calendar_home"

If Profile Manager won’t load it could be one of three issues (in the following order seemingly). The first is the web server, which the first command will fix. Another issue I’ve seen is that Open Directory gets a little messed up. The fix for this is to use Server Admin (not slapconfig) to burn OD down and set it back up. You can then promote replicas and finally restore the archive you did before upgrading the server. The third is to reset the Profile Manager database using wipeDB.sh:

sudo /usr/share/devicemgr/backend/wipeDB.sh

After wiping the data, you can re-run the setup in Server app for the Profile Manager service to restore an empty Profile Manager instance to working order. You can restore data into the empty Profile Manager database using the same commands I showed earlier for CalDAV, just use devicemgrd instead.

Note: I am pretty sure you need sudo for most every command I use on this site, but more specifically you need it with this stuff. So sudo is assumed if not explicitly stated.

Finally, be on the lookout for custom designs in the Wiki interface. OS updates are known to change things, but more specifically when things are not documented they can easily change. Hacking the pages nested within /usr/share/collabd is basically not supported any more. Each OS update to 10.7 has broken some of the hacks we’ve done to collabd, making me wonder whether it’s a good idea any more…

Note2: I have had little issues running these updates in walled gardens. It’s production data that is the problem. It seems that most of the issues are data driven (the opposite of data driven design is not devops driven design).

March 6th, 2012

Posted In: Mac OS X Server

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

To find the GUID for the cedge user, use the following command:

dscl . -read /Users/cedge GeneratedUID

June 15th, 2008

Posted In: Mac OS X, Mac OS X Server, Mac Security

Tags: , , ,