Skip navigation

Tag Archives: sql

Machines with no SCCM client…

Alright, so if you read my previous post for AD to DB then this post will make more sense.  However if you haven’t; then now would be a good time to… go ahead, I’ll wait.

Now one of the primary purposes I had for this data was to leverage it against my v_r_system view and determine which active assets are missing their client.  Well that’s fine and good, but I had been in the habit of taking that data and then performing a DNS check for the entries using a Powershell script written by Jason Sandys.  Originally I was using a vbscript I wrote to do so, but found his to be far more efficient since it made use for the DNS class in .Net.  But I digress, the end result is that I had quite a few additional steps to determine which machines were active, and ready for remediation either locally by desktop support, or by my remote repair tools.

So, what to do?  Why not query what I need, run the DNS check from a data set, then write the results back to my data warehouse?  This script requires a few things:

  1. Working, integrated credential for querying the SCCM DB; and permission to drop, insert, and create for the DB warehouse.
  2. Table within the data warehouse with the AD data provided with my AD to DB script to join against the SCCM DB data.
  3. Established linked servers between the two databases to perform the join (stored query in the script calls the join from the SCCM DB server, so the link is required there)

 

Now that we have that out of the way lets discuss the variables that need to be modified here.

  • $db – SCCM Database for the primary site server
  • $sqlsrvr – SCCM Database Server Name
  • $db2 – Data Warehouse DB
  • $sqlsrvr2 – Data Warehouse Server Name
  • $table – Data Warehouse AD Table

 

Alright, so there it is, now time for the code; which I want to apologize in advance for it’s wide column width (download):

#We expect errors for hosts we can't find, so running silently
	$ErrorActionPreference = "SilentlyContinue"
#Configuring connection and query variables for the sql client adapter
$db = "sms_abc" 					#sccm database
$sqlsrvr = "SCCMDBServer"  			#sccm db Server Name
$db2 = "DataWarehouse"				#Data Warehouse db
$sqlsrvr2 = "DataWarehouseServer"	#Data Warehouse Server Name
$table = "ADtablefromDatawarehouse"	#The table where AD data is stored
$sqlquery = "select sys.Name0 from $sqlsrvr.$db.dbo.v_r_system as sys join `
			$sqlsrvr2.$db2.dbo.$table as adlist on adlist.ad_machine = sys.name0`
			where DATEDIFF(d,passwordlastset,getdate()) <= 30 and Client0 = 0 or`
			DATEDIFF(d,passwordlastset,getdate()) <= 30 and Client0 is null"
#Performing the query and writing to a data set
$sqlcon = New-Object System.Data.SqlClient.SqlConnection("Data Source=$sqlsrvr;Integrated Security=SSPI;Initial Catalog=$db;")
	$cmd = New-Object System.Data.SqlClient.SqlCommand
		$cmd.CommandText = $SQLQUERY
			$cmd.Connection = $SQLCON
	$sqladapter = New-Object System.Data.SqlClient.SqlDataAdapter
		$sqladapter.SelectCommand = $CMD
			$DS = New-Object System.Data.DataSet; $DS.Tables.Add("SQLQuery")
	[Void]$sqladapter.Fill($DS.Tables["SQLQuery"])
$sqlcon.Close()
#Building new table in SQL for DNSQuery
$table = "NoClientDNSRecord"	
	$sqlcon = New-Object System.Data.SqlClient.SqlConnection("Data Source=$sqlsrvr2;Integrated Security=SSPI;Initial Catalog=$db2;")
$sqlcon.Open()
	$cmd = $sqlcon.CreateCommand()
		$cmd.CommandText = "drop table $table"
			[Void]$cmd.ExecuteNonQuery() 
		$cmd.CommandText = "create table $table (Name varchar(150) not null Primary key,IP varchar(25), Reverse varchar(150), status varchar(50))"
			[Void]$cmd.ExecuteNonQuery() 
#Performing a DNS query against each machine in our SQL data set
foreach($row in $DS.Tables["SQLQuery"].rows){
	$system = $row[0]
	$sys = New-Object PSObject
		$sysname = $system.ToLower().Trim()
			$sys | Add-Member -MemberType NoteProperty -Name Name -Value $sysname
	#Getting IP address for the host name
	$sys | Add-Member -MemberType NoteProperty -Name IP -Value "-"
		$sys.IP = [System.Net.DNS]::GetHostEntry($sysname).AddressList | select -First 1
			$firstOctet = ($sys.IP -split "[.]")[0].Trim()
	#Getting reverse address from dns for the host name
	$sys | Add-Member -MemberType NoteProperty -Name Reverse -Value "-"
		$sys.Reverse = [System.Net.DNS]::GetHostEntry($sys.IP).HostName | select -First 1
			$sys.Reverse = ($sys.Reverse -split "[.]")[0].ToLower().Trim()
				if ($sys.Reverse -eq $firstOctet){$sys.Reverse = "-"}
	#Writing a status for the entry based on  name and reverse lookups.	
	$sys | Add-Member -MemberType NoteProperty -Name Status -Value "-"
		if		($sys.IP -eq "-")			`
		{$sys.Status = "Could not Resolve IP"}
		elseif	($sys.Reverse -eq "-")		`
		{$sys.Status = "IP Address not found in reverse zone"}
		elseif	($sys.Name -ne $sys.Reverse)`
		{$sys.Status = "IP registered to another system"}
		else								`
		{$sys.Status = "OK"}
#Writing values to SQL
$cmd.CommandText = "insert $table values ('$($sys.name)','$($sys.ip)','$($sys.reverse)','$($sys.status)')"; [Void]$cmd.ExecuteNonQuery()}
$sqlcon.close()

 

I’d recommend using PowerGUI for reviewing/modifying this code as it’s by far the best (free) powershell editor available.

Until next time, have a good one!

AD to DB

Ok, so as I stated at the tail end of my last post, here’s your PowerShell to export data from Active Directory to a MSSQL Database.  This code can be easily modified to pull whatever AD info you want and to insert it.  I make use of the System.Data.SqlClient namespace, so it’s worth mentioning that you can also use MySql.Data.MySqlClient or System.Data.OracleClient for your needs.  You can examine what I’m using via the SqlClient namespace and easily correlate it back to your respective need.

 

<#
Script: AD to DB
Author: Daniel Belcher
#>
#$ErrorActionPreference = "silentlycontinue"
$CHECK = Get-Module ActiveDirectory
IF(!$CHECK) {
	Write-Host -ForegroundColor Red `
	"Can't find the ActiveDirectory module, please insure it's installed.`n"
	Break}
ELSE {Import-Module ActiveDirectory}

#\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
<#Variable String values for customizing
	the DB and to write to and OU to lookup against
		as well as the time range in days#>
<#DataBase Name:#>
$DB = "Data"
<#SQL Server Name:#>
$SQLSRVR = "localhost\sqlexpress"
<#Table to Create:#>
$TABLE = "ADExport"	
<#Days old based on activity according to passwordlastset:#>
$DAYS = 180
<#Root OU to Search:#>
$ORGUNIT = "Root OU to Search"
<#Table Create String#>
$CREATE = "CREATE TABLE $TABLE (AD_Machine varchar(150) not null PRIMARY KEY, `
	OU varchar(100),DistinguishedName varchar(max),PasswordLastSet datetime, `
	OS varchar(100),ServicePack varchar(100))"
#/////////////////////////////////////////////////////////
<#Setting up object variables
	to be used for AD lookup#>
	$TIME = [datetime]::today.adddays(-$DAYS)
$LOOKUP = (Get-ADOrganizationalUnit -LDAPFilter "(Name=$ORGUNIT)")
	$ADARRAY = (Get-ADComputer -SearchBase $lookup.DistinguishedName -properties `
			name,passwordlastset,operatingsystem,operatingsystemservicepack `
			-Filter 'passwordlastset -ge $TIME')  
<#Setting up object variables
	to be used for AD lookup#>
	$TIME = [datetime]::today.adddays(-$DAYS)
$LOOKUP = (Get-ADOrganizationalUnit -LDAPFilter "(Name=$ORGUNIT)")
	$ADARRAY = (Get-ADComputer -SearchBase $lookup.DistinguishedName -properties `
			name,passwordlastset,operatingsystem,operatingsystemservicepack `
			-Filter 'passwordlastset -ge $TIME')  
<#Connect and cleanup the AD table
	Connection remains open for writting#>
$SQLCON = New-Object System.Data.SqlClient.SqlConnection("Data Source=$SQLSRVR; `
			Initial Catalog=$DB;Integrated Security=SSPI")
	$SQLCON.open()
		$SQL = $SQLCON.CreateCommand() 
			$SQL.CommandText ="DROP TABLE $TABLE"
				$SQL.ExecuteNonQuery() > $null
		$SQL.CommandText = $CREATE
			$SQL.ExecuteNonQuery() > $null
<#Begin loop through the ADARRAY for
	Variables and begin inserting Rows to table#>
	$X = 0
ForEach($OBJECT in $ADARRAY){
	
	$NAME = $OBJECT.Name
		$OU = $OBJECT.DistinguishedName.ToString().Substring($OBJECT.Name.ToString().Length+4)
			$DN = $OBJECT.DistinguishedName
				$PWDLS = $OBJECT.PasswordLastSet
					$OS = $OBJECT.OperatingSystem
						$SP = $OBJECT.OperatingSystemServicePack
						
#\\\\Any Table Data to be written goes here:\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
	$INSERT = "INSERT $TABLE VALUES ('$NAME','$OU','$DN','$PWDLS','$OS','$SP')"
	$SQL.CommandText = $INSERT
#////////////////////////////////////////////////////////////////////////////////////////	
		$SQL.ExecuteNonQuery() > $null
$X = $X + 1			
}
"$X Records written to $TABLE in database $DB on server $SQLSRVR"
<#Cleanup variables and close connections#>
$SQLCON.Close()

 

Overview: 

We have 5 key variables to work from here:

  1. $DB – Name of Database to write to
  2. $SQLSRVR – Name/IP of the SQL server
  3. $TABLE – Table to build this data to
  4. $DAYS – Age of machines to pull from AD
  5. $ORGUNIT – The base OU to search through recursively

 

These of course are set to preference or need.

Now what the script does is, it figures out the date using the datetime function in .net factored by the number of days back you declare.

Then it looks up and stores the base OU we will search through then builds the object with the AD Objects it finds.

Next it instantiates the SqlClient object and makes a connection to the server and database we specified in our variables.  We drop the table specified, then recreate the table.

Now the real work begins, we loop through the AD Objects one by one and cherry pick the specific properties out that we want from the object (an area that PowerShell excels at I might add) and declare them as variables to be finally written to our database table.

And for good measure we count each loop and store that so we can see how many records were found and attempted, then cleanup all our variables used during the script.

Building custom SQL collections in SCCM…

Ok, so as I discussed in my previous post I built multiple collections that determine client inventory health.

So the end result is the ability to build collections based off of machines who haven’t reported to one or more inventory types for X number of days and have the script deployed to them auto-magically with the corresponding inventory type.

This is one of the areas where SCCM (from the console level) becomes pretty restrictive.  In MSSQL you have access to a couple of functions that make determining time frames very simple.  In this case DATEDIFF and GETDATE which combined provide a nice simple statement for determining a fixed time frame.

DATEDIFF(d, column-to-check, GETDATE()) >= 30

 

Ok, great.  So what’s the problem?  Well when building collections in SCCM you have to use WQL, and it doesn’t have such a robust function set.  Fortunately there is a way around this, and I’m going to give a few SQL queries you can use to build some inventory health collections.

Start by building a new empty collection, and during the creation process make a standard query rule and save it.  Now right click and go to properties of your new collection and copy it’s Collection ID.

Now, open up your sql management studio and connect to your site server DB.  Make sure you are connected to the proper DB and run the following query:

select QueryExpression from v_CollectionRuleQuery
where CollectionID = '(pasted collection id)'

 

What you should see is the SQL being used on the back end to present to your console.  Now what you need to do is update this column to match whatever query you need.  Bear in mind, when you are redefining things inside this query they need to match the WQL schema for the console to get the information it needs.  I’ll show you examples later.  I also want to point out, this is not supported by Microsoft, and if you don’t properly structure your Update statement you could end up hosing all your collections, so be safe.  Now here is how you do it.

Update v_CollectionRuleQuery
Set QueryExpression = ‘whatever query statement you want’
where CollectionID = ‘(pasted collection id)’

 

Now when you update membership and refresh that collection you should see results to match your query.  Ok, now here’s the kicker.  Never modify this collections query properties from inside SCCM, the changes you made will be overwritten if you do.

Now, about those queries I promised you.

--No Inventory for 30 days
SELECT SMS_R_System.ItemKey, SMS_R_System.DiscArchKey, SMS_R_System.Name0, 
   SMS_R_System.SMS_Unique_Identifier0,SMS_R_System.Resource_Domain_OR_Workgr0,
			SMS_R_System.Client0 
			FROM
				System_DISC AS SMS_R_System 
					JOIN softwareinventorystatus AS sw 
						ON 
					SMS_R_System.ItemKey = sw.ClientId 
						JOIN WorkstationStatus_DATA AS wks 
							ON
						wks.MachineID = sw.ClientId 
			WHERE
				DATEDIFF(dd,sw.LastUpdateDate,GETDATE()) > 30 
					AND
				DATEDIFF(dd,wks.LastHWScan,GETDATE()) > 30

--No Hinv 30 days
SELECT SMS_R_System.ItemKey, SMS_R_System.DiscArchKey, SMS_R_System.Name0, 
		SMS_R_System.SMS_Unique_Identifier0,
			SMS_R_System.Resource_Domain_OR_Workgr0, SMS_R_System.Client0 
		FROM System_DISC AS SMS_R_System 
		JOIN WorkstationStatus_DATA AS wks 
				ON 
			 wks.MachineID = sms_r_system.ItemKey
		WHERE 
			DATEDIFF(dd,wks.LastHWScan,GETDATE()) > 30

--No Sinv 30 days
SELECT SMS_R_System.ItemKey, SMS_R_System.DiscArchKey, SMS_R_System.Name0, 
		SMS_R_System.SMS_Unique_Identifier0, 
			SMS_R_System.Resource_Domain_OR_Workgr0, SMS_R_System.Client0 
		FROM 
		System_DISC AS SMS_R_System 
			JOIN softwareinventorystatus AS sw 
				ON
			SMS_R_System.ItemKey = sw.ClientId 
		WHERE 
			DATEDIFF(dd,sw.LastUpdateDate,GETDATE()) > 30

--Client with no DDR
SELECT SMS_R_System.ItemKey, SMS_R_System.DiscArchKey, SMS_R_System.Name0, 
		SMS_R_System.SMS_Unique_Identifier0,
			SMS_R_System.Resource_Domain_OR_Workgr0, SMS_R_System.Client0 
			FROM System_DISC AS SMS_R_System 
				JOIN softwareinventorystatus AS sw 
					ON
				SMS_R_System.ItemKey = sw.ClientId
					JOIN WorkstationStatus_DATA AS wks 
						ON 
					wks.MachineID = sw.ClientId 
			WHERE 
				DATEDIFF(dd,sw.LastUpdateDate,GETDATE()) > 5 
					AND 
				DATEDIFF(dd,wks.LastHWScan,getdate()) > 5 
					AND 
				SMS_R_System.Client0 = 0

If you need to specify a collection for exclusion (which if you build all of these you may want to exclude machines in the NO inventory collection) append this to the end of your query.

						and
					SMS_R_System.ItemKey not in (select ResourceID from v_cm_res_coll_CollectionID)

 

And there you have it.  When writing your update line I would change these sql queries to be one line and remove the comment obviously.  There is also a LimitToCollectionID column you might want to apply any specific restrictions to.

Hopefully these past two posts help a few people looking to take an automated stance to proactively resolving inventory reporting issues on clients.  For anyone else:

  1. Hopefully you learned something about sql?
  2. You got a nice refresher on the collection query views name?

 

Have a good one.

Select more, cool, stuff from SQL…

Alright, so last time I closed out my(sql) post I promised some more advanced queries etc.  Well let’s first start by playing around with some basic query syntax.

Think back to what I wrote about it being a logical grouping of data like a spreadsheet.  So surely we can pull specific data right?  Of course, this is where sql shines, and it couldn’t be simpler.

select user_id0 from temp.test_users;

 

Pretty simple right?  Ok, well let’s grab user id’s and their location, that should also be simple enough.

select user_id0, location from temp.test_users;

 

Now how about just the user’s located in Birmingham, al?

select user_id0, location from temp.test_users 
	where location like 'birmingham%';

 

Ok, let’s take a moment now and examine what we’ve just covered here in such rapid succession. These are simple select statements, and the primary data retrieval method in SQL.

select <column, names> from <server.database.table> 
	[where <conditions of the search>]

 

You can use * for column names to select them all, and using where is not required for your statements, but advised for a precise query.  Couldn’t be easier could it?  A cool little tidbit too is using as in your query to modify your column names, using our previous select for user id’s and location lets do it again and rename our columns User ID and Place:

select user_id0 as 'User ID', location as 'Place' 
	from temp.test_users 
where location like 'birmingham%';

 

What?  That’s it?  Pretty nifty huh?  Ok, let’s turn the difficulty up a little bit now and join tables for a cross reference.  Ok, I built a bogus phonebook table with similar data to our test_users table.  In that table I have some phone numbers, but not the full name of the users.  I want to pull the users full name and phone number, here’s how I could do that:

select test.last_name, test.first_name, phone.number 
	from test_users as test 
left outer join phonebook as phone 
	on 
phone.userid = test.user_id0;

 

Ok, you can put the paper sack away, and breathe easy.  That’s not nearly as complex as it looks or sounds.  Let’s break it down piece by piece and examine it real fast.  First we will look at the output:

+-----------+------------+--------------+
| last_name | first_name | number       |
+-----------+------------+--------------+
| belcher   | daniel     | 205-111-1111 |
| rich      | derrek     | 813-111-1113 |
| george    | amanda     | 205-111-1112 |
+-----------+------------+--------------+

 

In our select statement we see column last_name, first_name, and number.  We examined earlier about using ‘as’ to define a name, and that’s what we have done during that select statement.  We’ve named the table test_users as test and phonebook table as phone.

So, before defining the column name we are selecting, we have to identify FROM which table.  In this case; test.last_name, test.first_name, phone.number

Now, let’s examine the data I put into phonebook to better understand how this join is working.

+--------+---------+----------------+--------------+
| userid | last    | location       | number       |
+--------+---------+----------------+--------------+
| dbel   | belcher | birmingham, al | 205-111-1111 |
| ageo   | george  | birmingham, al | 205-111-1112 |
| drich  | rich    | tampa, fl      | 813-111-1113 |
+--------+---------+----------------+--------------+

 

Ok, as you can see, the userid column is identical to our user_id0 from test_users which makes it a prime anchor point for our join. So we perform a left outer join on test.user_id0 and phone.userid.

Now wasn’t that easy?

Finally we are going to look at a subquery.

select user_id0, last_name, first_name 
	from test_users 
where location in ('birmingham, al') 
order by user_id0;

 

This should prove very simple to read.  If the data stored under location matches birmingham, al then it is pulled.  You could easily add more criteria to the subquery by adding it along with a comma to separate the value.  So; where location in (‘birmingham, al’,’tampa, fl’)

There are other ways to achieve similar outcomes using OR and AND, but this is a much cleaner and shorter method for doing it.

That’s it for now, perhaps I’ll have another sql post with even more interesting sql techniques in the future.

Select * from SQL …

Alright, so lately at work I’ve been up to my eyeballs in SQL.  More specifically MSSQL, but SQL none the less.  So, I wanted to take some time to discuss it and some basic stuff that, even home users, might find useful.  Considering this is a wordpress blog and it uses mysql to store it’s post and configuration data, I’ll use mySQL examples for my code snippets.  Now let’s get started by creating our database.

create database temp;

 

Now let’s create a table for users.

create table temp.test_users
(User_ID0 char(50),
last_name char(50),
first_name char(50),
location char(50));

 

Let’s stop and break this down for a second.  We just created our first database and named it temp.  Now inside of that database we’ve just created a table with 4 columns with a limit of 50 chars per entry.

So what is that exactly? 

Well, a database is just a logical grouping of data.  One easy way to think of a database is to think of a spreadsheet.  We’ll use excel as our example.

The database is your workbook, and a table is a worksheet.

So we’ve got a table now, lets insert some data to play with in there.

insert into temp.test_users
(User_id0,last_name,first_name,location)
VALUES
('dbel','Belcher','Daniel','Birmingham, AL');
insert into temp.test_users
(User_id0,last_name,first_name,location)
VALUES
('drich','Rich','Derrek','Tampa, FL');
insert into temp.test_users
(User_id0,last_name,first_name,location)
VALUES
('ageo','George','Amanda','Birmingham, AL');

 

Lets see how that data looks?

select * from temp.test_users;

 

+----------+-----------+------------+----------------+

| User_ID0 | last_name | first_name | location       |

+----------+-----------+------------+----------------+

| dbel     | belcher   | daniel     | birmingham, al |

| drich    | rich      | derrek     | tampa, fl      |

| ageo     | george    | amanda     | birmingham, al |

+----------+-----------+------------+----------------+

 

Hmm, it would be great if we had a date stamp along with this data for a creation data footer.

alter table temp.test_users
add date0 char(50);

 

Now, how are we going to update these entries?  I mean we could

delete from temp.test_users;

 

And recreate all those previous insertions with a new date column.  But that just isn’t efficient, especially if we had an even larger table than this.  It would be nice if we could look through our selects and just append that data.  But wait, we can…

update temp.test_users
SET date0 = CURDATE()
where date0 is null;

 

Pretty neat huh?  Well, maybe, let’s see how our data looks now.

select * from temp.test_users;

 

+----------+-----------+------------+----------------+------------+

| User_ID0 | last_name | first_name | location       | date0      |

+----------+-----------+------------+----------------+------------+

| dbel     | belcher   | daniel     | birmingham, al | 2011-08-31 |

| drich    | rich      | derrek     | tampa, fl      | 2011-08-31 |

| ageo     | george    | amanda     | birmingham, al | 2011-08-31 |

+----------+-----------+------------+----------------+------------+

Alright, now there goes the basics of building, updating, and viewing a (my)SQL database and table.  Really simple huh?  I’ll try and post some slightly more advanced stuff in the next week.  Like joining tables, and creating subqueries.

I guess until then you can RTFM (read the flippin manual).