krypted.com

Tiny Deathstars of Foulness

The JSS has the ability to upload multiple .vpptokens, and using those, you can upload separate tokens for sites and then provide App Store apps to different sites based on each having some autonomy by having their own token. This is a pretty cool feature. And using the GUI, you can see when each token expires. You can also see a list of tokens using the API. To see a full list of all the tokens, we’ll just use a basic curl command here:

curl -s -u myuser:mypassword https://kryptedjamf.jamfcloud.com/JSSResource/vppaccounts

This provides an array of output that has the number of tokens in <size> and the id of each along with their name in <id> and <name> respectively, as follows

<?xml version="1.0" encoding="UTF-8"?><vpp_accounts><size>2</size><vpp_account><id>2</id><name>test</name></vpp_account><vpp_account><id>3</id><name>test2</name></vpp_account></vpp_accounts>

Once you know the id of a token, you can pull a bunch of information about that token using the following command:

curl -s -u myuser:mypassword https://kryptedjamf.jamfcloud.com/JSSResource/vppaccounts/id/2

The output would be as follows, with the expiration_date indicated:

<?xml version="1.0" encoding="UTF-8"?><vpp_account><id>2</id><name>test</name><contact/><service_token>xxxxxxxxxxyyyyyyyyyyyzzzzzzzzzaaaaaaaabbbbbbbbbbccccccc</service_token><account_name>krypted</account_name><expiration_date>2017/06/30</expiration_date><country>US</country><apple_id/><site><id>-1</id><name>None</name></site><populate_catalog_from_vpp_content>true</populate_catalog_from_vpp_content><notify_disassociation>true</notify_disassociation></vpp_account>

Or to limit the output to just the expiration date of the token, we’ll use sed to constrain:

curl -s -u myuser:mypassword https://kryptedjamf.jamfcloud.com/JSSResource/vppaccounts/id/2 | sed -n -e 's/.*<expiration_date>\(.*\)<\/expiration_date>.*/\1/p'

The output should just be a standard date, as follows:

2017/06/30

You can then loop through the output of the vppaccounts, build an IFS array, and display the dates for each, listing sites that are about to expire. For anyone that has a lot of sites with individual tokens, this might come in handy. Enjoy.

Hat tip: I thought I’d have to do this using a database query, but it turns out that the field where the stoken  is stored contains encrypted data different than the initially encoded base64, which I showed how to decrypt at What’s Really In A VPP Token File from Apple’s VPP?. This is to keep that data private. Instead, hat tip to Christian Dooley, who figured out that this is actually available in the API instead, and therefore I didn’t have to hit the database directly to write this article.

June 30th, 2016

Posted In: JAMF

Tags: , , , , , ,

Sometimes you have data in a MySQL database that you just don’t need. You can delete tables and records pretty easily. In fact, it’s almost too easy. And there’s no undo. So be careful. And backup. And then backup again. And then snapshot again, before tinkerating with anything in this article.

In this article we’ll look at using the SQL DELETE statement to delete rows in a table. To do so, we’ll follow this basic syntax, which includes a WHERE clause to narrow the scope of the DELETE by specifying which records will be removed:

DELETE FROM table
WHERE column=value;

In this article, we’ll use the same “Customers” table from our first articles:

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

Let’s not run this next command, but note that you can omit the WHERE statement to remove all data from a database. To do so, you would simply delete all rows in a table without deleting the table itself. I do this a lot with test databases. It leaves the schema/table structure, attributes, and indexes will be intact:

When I’m doing this, I usually specify the wildcard (*):

DELETE * FROM table;

In the following, we’re going to go ahead and remove the Microsoft record. To do so, we’ll run the DELETE and specify the table to delete data from. Then, we’ll use the WHERE to specify that we want to remove the record where the Site is Microsoft AND the Contact is Satya Nadella:

DELETE FROM Customers
WHERE Site='Microsoft' AND Contact='Satya Nadella';

The “Customers” table then looks like this:

ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US

February 6th, 2016

Posted In: Mac OS X, SQL

Tags: , , , , ,

The ORDER BY keyword in a SQL SELECT statement is used to sort a given result-set based on the contents of one or more columns of data. By default, results are in ascending order, but you can use either ASC or DESC to indicate that you’d like results sorted in ascending or descending order, respectively.

Below is a selection from the “Customers” table that we will use 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 syntax we’ll use for these commands is:

SELECT column_name, column_name
FROM table
ORDER BY column ASC|DESC, column ASC|DESC;

Here is a basic SQL statement that selects all of the customers from the “Customers” table and sorts the results in ascending order (as it’s the default operation) based on the contents of the “Country” column:

SELECT * FROM Customers
ORDER BY Country;

Now we’ll flip that into descending order:

SELECT * FROM Customers
ORDER BY Country DESC;

Then ascending based on first the country, then the Site:

SELECT * FROM Customers
ORDER BY Country, Site;

Finally, you can do ascending for the country and descending for the site:

SELECT * FROM Customers
ORDER BY Country ASC, Site DESC;

January 31st, 2016

Posted In: SQL

Tags: , , , , , , ,

There is a new archives page. This allows you to view the entire history of the site by date or quickly search through titles. Well, I guess I shouldn’t say quickly; given the number of posts the archives page takes a good bit of time to load…

Oh and in case I forgot to mention this earlier, I totally changed the color scheme of the whole site, converted most of the graphics to png (so they should load faster) and disabled a few “features” that I had created that were bogging it down. Running faster and I think possibly looking less vampiric.

December 22nd, 2009

Posted In: sites

Tags: ,

I added new pages for Google Gadgets and YouTube Videos to the navbar at the top of the screen. The Gadgets are the common gadgets I use and the Videos is a rendition of the krypted318 channel. Hope you find these useful. Look for additional applications to be added to the fat client stuff shortly.

December 5th, 2009

Posted In: sites

Tags: , , ,

Pardon my dust while there’s a little reconstruction and moving to a new host going on…

October 15th, 2009

Posted In: sites

Tags:

A few months ago I noticed that a pretty hefty percentage of my visitors came from an iPhone. Looking at the site from an iPhone was pretty darn painful, so I added a skin (Plugin) to the site that is specific to users of mobile devices.  While I have slightly modified it for my own purposes (testing in the Simulator every step of the way), I must admit that I didn’t write it.  I’ve received a few comments about it so to give credit where it’s due, it’s the WPTouch Plugin from Brave New Code. It’s a really small Plugin, easy to install (drop into your plugins directory and then refresh the plugins page) and looks really nice (I think). So if you’re running WordPress and want to give iPhone, Android and Blackberry Storm users a good experience on your site then check it out. Also, if you have any recommendations for making it look better for my site specifically, let me know – I aim to please.

April 6th, 2009

Posted In: sites

Tags: , , , , ,

You are right – I have neglected my blogging duties.  So for the next year I’ll do a posting a day to make up for it.  🙂

September 17th, 2007

Posted In: personal, sites, WordPress

Tags: , ,