David Steimle: json

Showing posts with label json. Show all posts

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, November 14, 2018

PHP: Passing Variables on the Command Line

In my work I need to generate documentation, and sadly I work in an all Windows environment and have no Apache server available to me. Using PHP to generate content is pretty natural to, but not using a web server creates some challenges.

If you have PHP installed*, it is easy enough to turn a document into an html document via redirection:

php .\mypage.php > mypage.html

However, one of the great things about PHP is passing $_GET data. I use JSON config files which contain data my documentation needs. If I want to point to a particular json file, I do not need to code it into my script, if I pass the file location as an argument.

<?php
parse_str(implode('&', array_slice($argv, 1)), $_GET);
$json = $_GET['json'];

include($json);

?>

So, my command line would be:

php .\mypage.php json=mypage.json > mypage.html

* PHP can be drop-installed on Windows, meaning that if the executable is present you can run php. I set up an alias in my Powershell $PROFILE to make it available easily:

Set-Alias -Name php -Value C:\mybin\php\php.exe