Accessing JSON Data and Powershell Objects within Strings

Yesterday we discussed getting JSON data from a SqLite3 database.

Using that data in Powershell means turning it into an object. Referencing it often means working with strings.

First, lets get our data back, and then convert it to an object

PS A:\> $MyData = Get-Content $TempCsv | ConvertFrom-Csv | ConvertTo-Json
PS A:\> $MyJson = $MyData | ConvertFrom-Json
PS A:\> $MyJson

name            twitter        url
----            -------        ---
David           dbsteimle      rhymeswithtimely.blogspot.com
Commander Candy codingComander codingcommanders.com

I want to turn this data into usable HTML. I could use the ConvertTo-HTML command:

PS A:\> $MyJson | ConvertTo-Html
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"

  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>HTML TABLE</title>
</head><body>
<table>
<colgroup><col/><col/><col/></colgroup>
<tr><th>name</th><th>twitter</th><th>url</th></tr>
<tr><td>David</td><td>dbsteimle</td><td>rhymeswithtimely.blogspot.com</td></tr>
<tr><td>Commander Candy</td><td>codingComander</td><td>codingcommanders.com</td></tr>
</table>
</body></html>

Which results in:

name	twitter	url
David	dbsteimle	rhymeswithtimely.blogspot.com
Commander Candy	codingComander	codingcommanders.com

Some of that data is for link generation, though. My users shouldn't have to cut and paste a URL from an HTML document. Instead, let's treat Powershell like it were PHP, and loop through our object and create a proper table.

PS A:\> $OutPut = $null
PS A:\> $Output += "<table>`n<tr>`n<th>Name</th>`n<th>Twitter</th>`n<th>URL</th>`n</tr>`n"
PS A:\>
PS A:\> foreach($Record in $MyJson){
>> $OutPut += "<tr>`n"
>> $OutPut += "<th>$($Record.name)</th>`n"
>> $Output += "<td><a href='https://twitter.com/$($Record.twitter)'>@$($Record.twitter)</a></td>`n"
>> $Output += "<td><a href='http://$($Record.url)'>$($Record.url)</a></td>`n"
>> $OutPut += "</tr>`n"
>> }
PS A:\> $OutPut += "</table>"
PS A:\> $OutPut
<table>
<tr>
<th>Name</th>
<th>Twitter</th>
<th>URL</th>
</tr>
<tr>
<th>David</th>
<th><a href='https://twitter.com/dbsteimle'>@dbsteimle</a></th>
<th><a href='http://rhymeswithtimely.blogspot.com'>rhymeswithtimely.blogspot.com</a></th>
</tr>
<tr>
<th>Commander Candy</th>
<th><a href='https://twitter.com/codingComander'>@codingComander</a></th>
<th><a href='http://codingcommanders.com'>codingcommanders.com</a></th>
</tr>
</table>

Since $Record has properties we want to reference withing a string, we need to isolate it, as in $($Record.name) to allow the .name to parse properly.

Now, our resultant table has both twitter and webpage links, making the HTML more useful for our readers.

Name	Twitter	URL
David	@dbsteimle	rhymeswithtimely.blogspot.com
Commander Candy	@codingComander	codingcommanders.com

Sqlite3 Export to JSON

I create custom scripted Detection Methods for SCCM utilizing JSON. Powershell handles JSON nicely, particularly if converting from an object. Powershell will do the necessary escaping of characters, which can be missed when manually creating JSON, or when you know there will be many characters to escape. For example, here is some data which would need escaping, and its resultant JSON:

PS A:\> $Example = @"
>> localpath,registry,share,text
>> C:\Temp,HKLM:\SOFTWARE,\\thatshare\me,use a`ttab
>> "@
PS A:\> $Example
localpath,registry,share,text
C:\Temp,HKLM:\SOFTWARE,\\thatshare\me,use a     tab
PS A:\> $Example | ConvertFrom-Csv | ConvertTo-Json
{
    "localpath":  "C:\\Temp",
    "registry":  "HKLM:\\SOFTWARE",
    "share":  "\\\\thatshare\\me",
    "text":  "use a\ttab"
}

My Detection Methods will often look for file checksums, which means a path to the file and the expected checksum.To avoid mistakes, and to make it easier on me, I create a SqLite3 database with the items for my JSON. Exporting from SqLite3 can be sent to a CSV file, and then parsed into JSON. A simple database example:

PS A:\> sqlite3 .\example.db
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
sqlite> .mode line
sqlite> SELECT * FROM example;
   name = David
twitter = dbsteimle
    url = rhymeswithtimely.blogspot.com

   name = Commander Candy
twitter = codingComander
    url = codingcommanders.com
sqlite> .quit

Now, a new trick to me it utilizing temporary files. I am using the dot NET method, which is usable in Linux Powershell as well (your mileage may vary). You can create a temp file with [System.IO.Path]::GetTempFileName(). To use that file, you want to assign it to a variable.

PS A:\> $TempCsv = [System.IO.Path]::GetTempFileName()
PS A:\> $TempCsv
C:/Users/david/AppData/Local/Temp/tmp47AD.tmp

Next, because SqLite3 does not use \ character in its paths, we need to change them to /.

PS A:\> $TempCsv = $TempCsv.Replace("\","/")
PS A:\> $TempCsv
C:/Users/david/AppData/Local/Temp/tmp47AD.tmp

Now we can create our SQL commands. This could also be a file, but I will use a here-string instead.

PS A:\> $TempSql = @"
>> .headers on
>> .mode csv
>> .output $TempCsv
>> SELECT name,
>>        twitter,
>>        url
>> FROM example;
>> .quit
>> "@
PS A:\> $TempSql
.headers on
.mode csv
.output C:/Users/david/AppData/Local/Temp/tmp47AD.tmp
SELECT name,
       twitter,
       url
FROM example;
.quit

Notice our converted $TempCsv value is in the $TempSql here-string.

Now, pipe the $TempSql into SqLite3:

PS A:\> $TempSql | sqlite3 .\example.db

Our $TempCsv frile will now have the output from SqLite3 in CSV format.

PS A:\> gc $TempCsv
name,twitter,url
David,dbsteimle,rhymeswithtimely.blogspot.com
"Commander Candy",codingComander,codingcommanders.com

We can now use this CSV formatted data, but must convert it to a Powershell Object.

PS A:\> Get-Content $TempCsv | ConvertFrom-Csv | ConvertTo-Json
[
    {
        "name":  "David",
        "twitter":  "dbsteimle",
        "url":  "rhymeswithtimely.blogspot.com"
    },
    {
        "name":  "Commander Candy",
        "twitter":  "codingComander",
        "url":  "codingcommanders.com"
    }
]

Powershell on Linux

Process from https://websiteforstudents.com/install-microsoft-powershell-core-for-linux-on-ubuntu-16-04-18-04/

The only process that worked for me, however, was the snapd approach:

sudo apt update
sudo apt install snapd
sudo snap install powershell --classic


One issue, though, is that I must sudo powershell to launch.

EDIT: to use powershell as a normal user, with the snap install, you need to add /snap/bin to your path

david@mybox ~ $ sudo which pwsh
/snap/bin/pwsh
david@mybox ~ $ PATH=$PATH:/snap/bin

---

Distributor ID: LinuxMint
Description: Linux Mint 18.3 Sylvia
Release: 18.3
Codename: sylvia

Name                           Value
----                           -----
PSVersion                      6.1.3
PSEdition                      Core
GitCommitId                    6.1.3
OS                             Linux 4.15.0-46-generic #49~16.04.1-Ubuntu SMP Tue Feb 12 17:45:24 UTC 2019
Platform                       Unix
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

Using AVCONV to Extract Frames

I seem to always refer back to this page when I want to extract images from a video, either fir animation or thumbnails:

https://dototot.com/how-to-extract-images-from-a-video-with-avconv-on-linux/

Security by Dior

I have been reading Dior by Dior, which is a great book for project management (particularly the discussion of creating the season's line). His discussion of security and piracy were very interesting, and smacks a lot of IT security.

Note that the term 'model', when used by Dior here, indicates a dress/suit, rather than a person wearing it.

There are five classical methods by which dresses are copied, of which the most distasteful is naturally that which originates with the treachery of a member of the staff.

This is, of course the classic insider-threat; disgruntled employees, employees who have been compromised in some way. They are providing information to either competitors or counterfeiters.

The second method of copying takes place at a much later stage when the collection is shown to the Press. In most cases, what takes place is really a regrettable abuse of the details of the collection which are released, rather than proper copying.

Dior defines how the press are sworn to non-disclosure at the season launch. Naturally, the line must be shown to the press to stir interest. He has agents on the floor watching for sketch artists. "Write all you like, but don’t draw!" is the rule

The third method of copying, and probably the most common, is the work of our clients, who thus show little regard for the rules of the profession. They sketch secretly some of the models which have been shown to them, pretending that they are trying to memorize the general look of a dress, whose name they did not catch. When they are discovered, we either make them buy the disputed model, or confiscate their sketches and keep their caution money.

The 'caution money' is further described as a trust against clients (in this case retailers) who view the line, but make no purchases.

[The fourth method] The 'model renters' had their heyday in the years just after the war, and it was not until 1948 that their ringleader, a particularly cunning woman, was detected. She had bought through several intermediaries – generally private clients – the best models from the great Parisian couture houses. On her return to New York, she organized a miniature show of her own.

The model renters would rent garments from couture houses, often several of them, for the purposes of selling to retailers and manufacturers under false pretenses, copying the model to provide a lesser-quality version.

[The fifth method] The ‘model renter’ was succeeded by the publisher of albums of sketches. The prosperity of this form of fraud – the most widespread which has ever been practised – is partly explained by the difference in legislation between France and America – the laws of the latter country being much looser as regards the artistic reproduction of commercial property.

This one is either the most amazing, or least understood by Dior (and he certainly knew his business). As with the second method, these people are monitored to prevent them from making sketches (and he discusses how other clients act as enforcers, for they do not wish to buy a model which will potentially be pirated). They are either memorizing models with amazing accuracy, or in some cases using technology. Pirates had been caught with miniature cameras.

Counter piracy measures were interesting, such as the use of invisible ink with serial numbers and secret stitching markers to aid in discovering pirates. At some of the fake shows mentioned in methods fourth and fifth, agents of couture houses will attend in the guise of pirates, purchase/examine the item offered for copy, and then trace the sale to the original buyer.

There are numerous other instances where the book provides good information for either working with creatives, or working under a manager as a creative. In an IT perspective, Dior is a developer who does not develop, but has a team of trusted agents who are experts at what they do. His relationship with them is important to his success. Well worth reading.

Using Powershell to Get Objects from Sqlite3

As discussed in SQLite3 and Powershell Redirection, we do not need to add any special Powershell tools to access sqlite3 if we utilize piping.

Versioning on my system:

SQLite version 3.24.0 2018-06-04 19:24:41
PSVersion 5.1.14393.2791

Note: I create an Alias for sqlite3:

PS A:\sqlite_to_csv> Set-Alias -Name sqlite3 -Value A:\sqlite3\sqlite3.exe


Creating an Object in Powershell is generally the best way to utilize data, and in my case I need an easy way to generate JSON files, which Powershell can be quite helpful at.

First, let's make a sample database:

PS A:\sqlite_to_csv> sqlite3 sample.db
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
sqlite> CREATE TABLE sample (
   ...> id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...> name TEXT,
   ...> twitter TEXT,
   ...> blog TEXT
   ...> );
sqlite> INSERT INTO sample
   ...> (name,twitter,blog)
   ...> VALUES
   ...> ('David Steimle','dbsteimle','rhymeswithtimely.blogspot.com');
sqlite> SELECT * FROM sample;
1|David Steimle|dbsteimle|rhymeswithtimely.blogspot.com


Now we have something to query.

First, we want to create the header, which will provide output in CSV mode with headers. For this I use a Here-String.

PS A:\sqlite_to_csv> $QuerySetup = @"
>> .mode csv
>> .headers on
>>
>> "@


Then we can define our query; we will do a simple SELECT only, for now, and combine it with our query setup.

PS A:\sqlite_to_csv> $Query = "SELECT * FROM sample;"
PS A:\sqlite_to_csv> $MySql = "$QuerySetup$Query"
PS A:\sqlite_to_csv> Write-Host $MySql
.mode csv
.headers on
SELECT * FROM sample;


We then assign our result to an Object with pipes:

PS A:\sqlite_to_csv> $MyObject = $MySql | sqlite3 .\sample.db | ConvertFrom-Csv
PS A:\sqlite_to_csv> $MyObject | Format-List

id      : 1
name    : David Steimle
twitter : dbsteimle
blog    : rhymeswithtimely.blogspot.com


This Object is now usable in all the normal ways, such as turning it into JSON (I will output to a file, as that would be my end goal):

PS A:\sqlite_to_csv> $MyObject | ConvertTo-Json | Set-Content sample.json
PS A:\sqlite_to_csv> Get-Content .\sample.json
{
    "id":  "1",
    "name":  "David Steimle",
    "twitter":  "dbsteimle",
    "blog":  "rhymeswithtimely.blogspot.com"
}


While in a basic sense this works for me, I do need the ability to have sub-objects and arrays in my JSON. This means additional tables. The obvious place to start in this example would be with Social Media. First, we will make a table of social media sites:

sqlite> CREATE TABLE socialmedium (
   ...> id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...> name TEXT,
   ...> url TEXT
   ...> );
sqlite> INSERT INTO socialmedium (name,url) VALUES
   ...> ('Twitter','https://twitter.com/');
sqlite> INSERT INTO socialmedium (name,url) VALUES
   ...> ('Instagram','https://instagram.com/');
sqlite> SELECT * FROM socialmedium;
id          name        url
----------  ----------  --------------------
1           Twitter     https://twitter.com/
2           Instagram   https://instagram.co


Then a lookup table with user details:

sqlite> CREATE TABLE socialuser (
   ...> id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...> user INTEGER,
   ...> site INTEGER,
   ...> username TEXT
   ...> );
sqlite> INSERT INTO socialuser (user,site,username) VALUES
...> (1,1,'dbsteimle');
sqlite> SELECT * FROM socialuser;
id user site username
---------- ---------- ---------- ----------
1 1 1 dbsteimle

These tables use the PRIMARY KEY from the sample table as a reference point. To work with out current Object, we need to add a new Property, and assign it as an empty hash table:

PS A:\sqlite_to_csv> $MyObject | Add-Member -NotePropertyName SocialMedia -NotePropertyValue @{}
PS A:\sqlite_to_csv> $MyObject

id          : 1
name        : David Steimle
twitter     : dbsteimle
blog        : rhymeswithtimely.blogspot.com
SocialMedia : {}


We need to use an INNER JOIN in this scenario, since we are looking up values in two lookup tables:

PS A:\sqlite_to_csv> $Query = @"
>> SELECT user,name,url,username
>> FROM socialuser
>> INNER JOIN socialmedium ON socialmedium.id=socialuser.site
>> WHERE user=$($MyObject.id);
>> "@
PS A:\sqlite_to_csv> $MySql = "$QuerySetup$Query"
PS A:\sqlite_to_csv> Write-Host $MySql
.mode csv
.headers on
SELECT user,name,url,username
FROM socialuser
INNER JOIN socialmedium ON socialmedium.id=socialuser.site
WHERE user=1;


Now, pipe through sqlite3:

PS A:\sqlite_to_csv> $MyObject.SocialMedia = $MySql | sqlite3 .\sample.db | ConvertFrom-Csv
PS A:\sqlite_to_csv> $MyObject.SocialMedia

user name    url                  username
---- ----    ---                  --------
1    Twitter https://twitter.com/ dbsteimle


And Conversion to JSON:

PS A:\sqlite_to_csv> $MyObject | ConvertTo-Json | Set-Content sample.json
PS A:\sqlite_to_csv> Get-Content .\sample.json
{
    "id":  "1",
    "name":  "David Steimle",
    "twitter":  "dbsteimle",
    "blog":  "rhymeswithtimely.blogspot.com",
    "SocialMedia":  {
                        "user":  "1",
                        "name":  "Twitter",
                        "url":  "https://twitter.com/",
                        "username":  "dbsteimle"
                    }
}


To best utilize this, we would want a function where we could utilize a known value as a parameter. We may not know that id 1 matches name 'David Steimle', but if we know the database, we might know to look for name="David Steimle"

function Get-MyObject {
param( [string]$Name )

$QuerySetup = @"
.mode csv
.headers on

"@

$Query = "SELECT * FROM sample WHERE name='$Name';"
$MySql = "$QuerySetup$Query"
$Temp = $MySql | sqlite3 .\sample.db | ConvertFrom-Csv

$Temp | Add-Member -NotePropertyName SocialMedia -NotePropertyValue @{}

$Query = @"
SELECT user,name,url,username
FROM socialuser
INNER JOIN socialmedium ON socialmedium.id=socialuser.site
WHERE user=$($Temp.id);
"@
$MySql = "$QuerySetup$Query"
$Temp.SocialMedia = $MySql | sqlite3 .\sample.db | ConvertFrom-Csv

$Temp | ConvertTo-Json | Set-Content ".\$Name.json"

return $Temp
}

PS A:\sqlite_to_csv> Get-MyObject -Name "David Steimle"

id          : 1
name        : David Steimle
twitter     : dbsteimle
blog        : rhymeswithtimely.blogspot.com
SocialMedia : @{user=1; name=Twitter; url=https://twitter.com/; username=dbsteimle}

PS A:\sqlite_to_csv> Get-Content '.\David Steimle.json'
{
    "id":  "1",
    "name":  "David Steimle",
    "twitter":  "dbsteimle",
    "blog":  "rhymeswithtimely.blogspot.com",
    "SocialMedia":  {
                        "user":  "1",
                        "name":  "Twitter",
                        "url":  "https://twitter.com/",
                        "username":  "dbsteimle"
                    }
}

Or, assign the result to a variable:

PS A:\sqlite_to_csv> $DavidSteimle = Get-MyObject -Name 'David Steimle'
PS A:\sqlite_to_csv> $DavidSteimle

id : 1
name : David Steimle
twitter : dbsteimle
blog : rhymeswithtimely.blogspot.com
SocialMedia : @{user=1; name=Twitter; url=https://twitter.com/; username=dbsteimle}


SQLite3 Output to CSV

Info from: http://www.sqlitetutorial.net/sqlite-tutorial/sqlite-export-csv/

Run below in sqlite3, or create myexport.sql

.headers on
.mode csv
.output myexport.csv
SELECT * FROM mytable;
.quit

If myexport.sql, pipe through Powershell:

gc .\myexport.sql | sqlite3 mydatabase.db

If you want to look at the CSV as an object, execute:

$MyObject = gc myexport.csv | ConvertFrom-Csv

More about Powershell and SQLite3 inegration here.