List Preparation for SQL Query

I often need to query a database using a where in filter and a list of items. The items are generally in a list format in an excel or csv file, once copied they are on different lines. This is how I convert to a usable string for an SQL query filter using Notepad++ :

  • Paste list into Notepad++
  • Open the Find and Replace dialog (Ctrl+F)
  • Change search mode to ‘Extended’
  • Find what : \r\n
  • Replace with : ‘,’
  • Add an quote mark at beginning and end of line
Screenshot of Find and Replace dialog in Notepad++

Then you can paste this string into a SQL ‘where in’ clause between the brackets.

Advertisements

Checking SCCM Task Sequence Variables using Powershell

While running an OSD task sequence you can use powershell to check the TS Environment variables.

I added a ‘Pause’ step using this guide. If, like me, you are using an x64 PE image, you need use the ‘serviceui.exe’ from either the Program Files location on the machine with MDT installed, or a MDT package :

%MDTInstallLocation%\Templates\Distribution\Tools\x64\
%MDTPackage%\Tools\x64\

Once you have your Task Sequence paused, you can press F8 (if enabled in the boot image) and start powershell.

$env = New-Object -COMObject Microsoft.SMS.TSEnvironment

This will create an interface to the com object we need to use to interact with the TS environment.

$env.GetVariables() | % {$_ + "=" + $env.Value($_)}

This will dump out all the variables to the screen, this is a bit messy as there are lots of variables – some with quite long xml text as the values.

This will output all the variables to a text file :

 
$env.GetVariables() | % {$_ + "=" + $env.Value($_)} | out-file "variables.txt"

This will display the value of a specific variable (e.g. Architecture):

 
$env.Value("Architecture")

You could adapt this into a script to run automatically, instead of manually if required.

SMBIOS Chassis Type Table

Table I use for reporting on ratio of Desktop / Laptops using Chassis Types. Derived from the DMTF SMBIOS Spec November 2016 : http://www.dmtf.org/sites/default/files/standards/documents/DSP0134_3.1.0.pdf

Hex Value Dec Value Meaning Laptop-Desktop-Other
1 1 Other Other
2 2 Unknown Other
3 3 Desktop Desktop
4 4 Low Profile Desktop Desktop
5 5 Pizza Box Desktop
6 6 Mini Tower Desktop
7 7 Tower Desktop
8 8 Portable Laptop
9 9 Laptop Laptop
A 10 Notebook Laptop
B 11 Hand Held Laptop
C 12 Docking Station Other
D 13 All In One Desktop
E 14 Sub Notebook Laptop
F 15 Space-saving Desktop
10 16 Lunch Box Desktop
11 17 Main Server Chassis Other
12 18 Expansion Chassis Other
13 19 SubChassis Other
14 20 Bus Expansion Chassis Other
15 21 Peripheral Chassis Other
16 22 RAID Chassis Other
17 23 Rack Mount Chassis Other
18 24 Sealed-case PC Desktop
19 25 Multi-system chassis Other
1A 26 Compact PCI Other
1B 27 Advanced TCA Other
1C 28 Blade Other
1D 29 Blade Enclosure Other
1E 30 Tablet Laptop
1F 31 Convertible Laptop
20 32 Detachable Laptop
21 33 IoT Gateway Other
22 34 Embedded PC Desktop
23 35 Mini PC Desktop
24 36 Stick PC Desktop

Creating Offline Repositories for CentOS / RHEL – Part 1

I’ve started doing more and more with linux, I wanted a way to store updates and packages offline – in the same way we do for Windows using WSUS and SCCM etc… This is how to host offline repositories, it may not be the most secure method – but is aimed at LAN hosting where you trust the LAN members!

  • Set up a server to host the RPM content, I used CentOS 7.
  • Install a web server service, I used nginx – my base path is /var/www/html
  • Create a folder for the repo you want to host – in this example I’m going to use “base”
  • Use reposync to download the RPM’s
    • reposync -p /var/www/html -r base -l
  • Create a repo
    • createrepo /var/www/html/base
  • Update the repo after a reposync
    • createrepo –update /var/www/html/base

You can now amend your yum config files on clients to use this repo, I will post another entry sometime with more information on how to automatically update the repo.

Oh-My-Zsh Agnoster Theme Windows 10 Client

I wanted to try out Oh-My-Zsh, I needed to set up a new CentOS box to run some stuff and decided now was the time give it a go.

It was easy to get working on the server side, but to get the rendering right on Windows client took a bit of work!

  • Install git, curl and zsh
  • Install oh-my-zsh using the curl commandline from here.

This went fine and worked as expected (I was connected via mobaxterm from Windows 10) But I wanted something a bit more fancy, so I edited the file ~/.zshrc and changed the theme to “agnoster”. As stated on the themes page, I needed to install some patched fonts to get the nice icons – this is where it got tricky. I followed the powerline instructions and installed the fonts on the CentOS box, but this didn’t work. Then I installed some of the fonts in Windows and switched the MobaXTerm font to one of these – didnt work.

It turns out that some of the powerline fonts work better in Windows than others. I messed around trying to get some of the fonts working in MobaXTerm, then I gave up and tried in Putty. The one I found which worked was DejaVu Sans Mono – thanks to this blog.

I had only just started using MobaXTerm, and quite liked it – but as I couldn’t get this working, I’ll probably go back to just using Putty Tray (a putty fork, i like!)

I found this page – which has instructions of enabling the nice Solarized theme in Putty-Tray 🙂

More External Themes to try : https://github.com/robbyrussell/oh-my-zsh/wiki/External-themes

CentOS on Windows Subsytem for Linux (WSL)

I read about the Windows Subsystem for Linux a while back, and thought it was only available using Ubuntu. Today I wanted to play around with it and fish shell, but I prefer centos to ubuntu. A quick google found this github project – WSL Distribution Switcher –

Excellent! It allows you to switch from ubuntu to other linux distro’s. I’m not going to pretend I have taken the time to understand how WSL works ‘under the hood’ but as WSL Switcher gets docker layers / images, I’m guessing it based on docker in some shape or form?

I enabled WSL on my W10 1703 (Creators Update) build, started bash and created myself a user. Installed Python 3 (a requirement for WSL Switcher). Then I cloned the WSL Switcher git project and used the following commands to switch to centos and confirm it worked :

  • get-prebuilt.py centos:latest
  • install.py centos:latest
  • bash
  • cat /etc/centos-release

Everything seemed fine, but then I tried to run yum (the centos package manager). It wouldn’t run as a non root user, fine – tried “sudo yum”. Command not found – grrrrrr

Then I tried su – got prompted for password and thought I had made progress – entered the password for my linux user… nope!

Luckily, I’m not the first person to run into this problem 🙂 I found an issue in the WSL Distro Switcher github with the information I needed. I needed to switch the default user that WSL used, enter WSL / bash, change the passwd, then change the default user back to my non root user.

  • lxrun /setdefaultuser root
  • Found UNIX user: root
    Default UNIX user set to: root
  • bash
  • passwd
    • (enter your desired root password)
  • exit
  • lxrun /setdefaultuser *username*

Now when I went into WSL, I was logged in as my normal user – I could “su” and run commands as root – perfect! However, I like to sudo rather than switch to root using su – more to be done…

I thought the next bits would be easy – but alas no. yum wont work, even now I have access to root user – looks like a network issue. Should be simple to figure out right? I wish, no ifconfig, so i tried “ip addr” – neither work. Why do people insist on taking so much stuff out of an OS as to make it almost useless!

 

I thought that the networking was messed up – it was fine, but DNS resolution was messed up – I ‘resolved’ this by editing resolv.conf, which had a really random IP set for the nameserver! I fixed this and then moved onto to sorting out sudo!

  • yum install sudo
  • exit (to exit su shell back to normal user)
  • sudo yum
    • user is not in the sudoers file. This incident will be reported.
    • so we need to add to the sudoers file
  • su
  • vi /etc/sudoers
    • add a line near the bottom
    • username ALL=(ALL) ALL
  • exit (exit su shell)
  • sudo yum (testing)
    • It works!

Now onto fish commandline…

Its not in the main repos – so I had to first install epel

  • sudo yum install epel-release
  • sudo yum install fish

Now I can enter fish shell after entering WSL bash – i tried a few things to try and set fish as the default shell – but none of them worked. Probably something to do with docker / wsl. I will update this post if i can figure out how to go straight into fish…