David Steimle: March 2019

Friday, March 29, 2019

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

Thursday, March 28, 2019

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"
    }
]

Wednesday, March 27, 2019

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

Tuesday, March 26, 2019

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/

Tuesday, March 19, 2019

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.