SCCM by Davis |
- Rod Trent wrote a new post, Alert Update Connector for OpsMgr 2012
- Maik Koster wrote a new post, Compare Active Directory computer accounts with Configuration Manager resources
- Julius Nieves and Bill Readshaw are now friends
- Anoop C Nair and Bill Readshaw are now friends
- Julien Moreau and Bill Readshaw are now friends
- Enhansoft and Bill Readshaw are now friends
- 10 Mobile Networking and Application Development Myths to Forget
- Windows Server 2008 support extension gives IT time to catch up
- Lawrence Garvin became a registered member
- Enhansoft wrote a new post, VOTE for Free SSRS Report
- njm became a registered member
- KHUSHBOO SETHI became a registered member
- Winston Hinton commented on the post, [Project] – CM12 Automation – beta is coming
- Enhansoft wrote a new post, Last Chance to Get Computer System Enclosure Details Report!
- Thomas Stoehr commented on the post, ConfigMgr 2012 Right Click Tools
- Compare Active Directory computer accounts with Configuration Manager resources
- Rod Trent posted an update in the group The Word at myITforum: Psalms 9:9 The LORD is a stronghold for the oppressed, a […]
- Rod Trent posted an update in the group IT Fit: Diet Foods: Weight Loss Snacks - Prevention.com
- Rod Trent wrote a new post, Adobe code signing certificate revocation on Oct 4
- HP, Cisco Networking Gear Work Together in New Private Cloud Data Center
| Rod Trent wrote a new post, Alert Update Connector for OpsMgr 2012 Posted: 28 Sep 2012 12:40 PM PDT A new utility has been released that allows you to set or override custom field values, owners, and ticket IDs for OpsMgr 2012 alerts. Grab it here: System Center 2012 Operations Manager – Alert Update Connector Comments: 0  |
| Posted: 28 Sep 2012 12:33 PM PDT
Comments: 0  |
| Julius Nieves and Bill Readshaw are now friends Posted: 28 Sep 2012 12:32 PM PDT Comments: 0  |
| Anoop C Nair and Bill Readshaw are now friends Posted: 28 Sep 2012 12:32 PM PDT Comments: 0  |
| Julien Moreau and Bill Readshaw are now friends Posted: 28 Sep 2012 12:32 PM PDT Comments: 0  |
| Enhansoft and Bill Readshaw are now friends Posted: 28 Sep 2012 12:32 PM PDT Comments: 0  |
| 10 Mobile Networking and Application Development Myths to Forget Posted: 28 Sep 2012 11:30 AM PDT Unfortunately, there are still a lot of misconceptions about the best way to establish your mobile strategy within your network.  |
| Windows Server 2008 support extension gives IT time to catch up Posted: 28 Sep 2012 09:34 AM PDT In recognition that server upgrades can be glacially slow, Microsoft extended Windows Server 2008 support as it rolls out the OS' heir apparent.         |
| Lawrence Garvin became a registered member Posted: 28 Sep 2012 11:35 AM PDT Comments: 0  |
| Enhansoft wrote a new post, VOTE for Free SSRS Report Posted: 28 Sep 2012 11:25 AM PDT Will it be Program Action Details OR Hard Drive Stats? VOTE NOW! The winner, which will become October's free SSRS report, will be announced next week. Program Action Details sample screenshot: <span style="font-size: […] Comments: 0  |
| njm became a registered member Posted: 28 Sep 2012 10:45 AM PDT Comments: 0  |
| KHUSHBOO SETHI became a registered member Posted: 28 Sep 2012 10:39 AM PDT Comments: 0  |
| Winston Hinton commented on the post, [Project] – CM12 Automation – beta is coming Posted: 28 Sep 2012 09:07 AM PDT This is awesome! Comments: 0  |
| Enhansoft wrote a new post, Last Chance to Get Computer System Enclosure Details Report! Posted: 28 Sep 2012 07:15 AM PDT
Computer System Enclosure Details Use this report to select the device you want to look […] Comments: 0  |
| Thomas Stoehr commented on the post, ConfigMgr 2012 Right Click Tools Posted: 28 Sep 2012 06:56 AM PDT Hello, very nice Tools. RDP to Console But if i look at the XMLs at ExtensionsActions i can see the Entries for them. Comments: 0  |
| Compare Active Directory computer accounts with Configuration Manager resources Posted: 28 Sep 2012 06:21 AM PDT Despite all discovery methods, Health scripts and other cleanup efforts it might be helpful from time to time to simply compare all the computer account objects that we have in Active Directory with our resources in ConfigMgr. There are a couple posts available, that demonstrate how to integrate information from Active Directory into SQL reports. See e.g. "How to add AD data to ConfigMgr reporting" from Garth Jones. As you can see, in his post, Garth shows already how to compare AD accounts with ConfigMgr, so why this post? Well, they mainly use the ADSI provider as a linked server and the OPENQUERY SQL command to query AD. However most of them suffer from a limitation, that comes from Active Directory itself and will actually hurt most people in a even slightly larger environments. The ADSI provider will only return 1000 rows per query. And there is not way to tell the "OPENQUERY" command to return more rows. There are a couple ways around this problem:
Increase the limit in Active DirectoryNaaaaah. If you don't know exactly what your are doing, don't do this, as it has effect on all queries against AD! Also this is not a long term fix, as the company might grow, which would require to increase the limit again.
Call OPENQUERY in batchesWorks, but is pretty complicated. You would need to create a T-SQL script that selects only a subset of accounts, e.g. based on the first character of the name. Every time it reaches the limit of 1000 objects in this subset it would need to divide it again, by e.g taking also the next character of the name into consideration and so on. If you would like to give this a try, you will find some samples on the internet. I don't supply a link, as none of them has really fulfilled my needs so far.
Adding a CLR procedure to query ADSee this post from Igor Kovalenko for more information. He basically uses a pre-compiled dll that you have to register in your SQL Server. Again, this will work, but I'm not a big fan of registering some custom dll's in my sql server. Especially if it is for such a crucial system like ConfigMgr.
Using LogParser to fill a temporary tableYES! If you don't know LogParser, you might want to have a look on some IIS query examples that give you a pretty good idea on its capabilities. It's been initially created to parse IIS log files and haven't been updated since quite some time (the current download still dates back to 2005), but it's still some of the most powerful and underestimated tools around. Download it here. LogParser uses a SQL like syntax to query from IIS logs (of course), text/XML files, Event logs, the File System, Registry and Active Directory (that's what we need here). It can even parse network captures from Netmon or Windows trace logs. It writes the output to text/xml files, but can also write directly into SQL tables (what we will use in this post). Most importantly, it's command line based, so pretty easy to automate. However, there is a GUI called Log Parser Lizzard GUI available for free, in case you struggle with the syntax. After we installed logparser, we can use something like the following to have it crawl through our AD and return a list of computer with some common attributes we might need (line breaks just added for readability): logparser.exe "SELECT cn, objectpath, operatingSystem, operatingSystemServicePack, LastLogonTimestamp, pwdLastSet FROM 'LDAP://yourdomain.com'" -objClass:computer Logparser will now write a list of computers in bunches of 10 entries to the default output. In our case the command line window. As you can see, we selected the LastLogonTimestamp and PwdLastSet attributes. These attributes give a quite good indication if a computer is still active. But these are Active Directory timestamps so not really handy if we want to upload them to a SQL table for some further investigation, as SQL uses a different date/time format. Now Logparser can also help us here. It doesn't have a native function for the conversion, but if we know, that Active Directory stores date/time values as a number of 100-nanosecond intervals that have elapsed since the 0 hour on January 1, 1601 in GMT, we can use some logparser native functions to do the conversion for us. So to convert the LastLogonTimestamp, we could use the following: TO_TIMESTAMP(ADD(DIV(TO_REAL(LastLogonTimestamp), 10000000.0), TO_REAL(TIMESTAMP('1601','yyyy'))))
Now we add this to the original query and execute this (line breaks just added for readability): logparser.exe "SELECT cn, operatingSystem, operatingSystemServicePack, TO_TIMESTAMP(ADD(DIV(TO_REAL(LastLogonTimestamp), 10000000.0), TO_REAL(TIMESTAMP('1601','yyyy')))) AS [LastLogon], TO_TIMESTAMP(ADD(DIV(TO_REAL(pwdLastSet), 10000000.0), TO_REAL(TIMESTAMP('1601','yyyy')))) AS [PwdLastSet] FROM 'LDAP://yourdomain.com'" -objClass:computer and we get a nicely formatted output. Now it's time to export this information directly into a SQL table. So we need to specify the SQL Server, the Database and the table. We can optionally specify a username and password. We also tell it to create the table if it doesn't exist and to empty it first, before adding new values, in case it already exists (as we might want to execute this regularly (line breaks just added for readability): logparser.exe "SELECT cn, operatingSystem, operatingSystemServicePack, TO_TIMESTAMP(ADD(DIV(TO_REAL(LastLogonTimestamp), 10000000.0), TO_REAL(TIMESTAMP('1601','yyyy')))) AS [LastLogon], TO_TIMESTAMP(ADD(DIV(TO_REAL(pwdLastSet), 10000000.0), TO_REAL(TIMESTAMP('1601','yyyy')))) AS [PwdLastSet] INTO tmp_ADComputers FROM 'LDAP://yourdomain.com'" -objClass:computer -o:SQL -server:YourSQLServer -database:Tempdb -createTable:ON -clearTable:ON After we executed logparser, we can open the SQL Management Studio and check our TempDB Database and will find a new table, filled with all computer accounts from AD.
Compare with ConfigMgrNow we can add a view to our ConfigMgr Database, that correlates the information from this temporary table with the ConfigMgr resources. The view could look like: SELECT [cn] AS 'Computer Name' ,[ObjectPath] AS 'Path' ,[operatingSystem] AS 'OS' ,[operatingSystemServicePack] AS 'SP' ,[LastLogon] ,DATEDIFF(dd, [LastLogon], getdate()) AS 'days LastLogon' ,[PwdLastSet] ,DATEDIFF(dd, [PwdLastSet], getdate()) AS 'days PwdLastSet' FROM [tempdb].[dbo].[tmp_ADComputers] WHERE cn NOT IN (SELECT name0 FROM v_GS_Computer_System) ORDER BY LastLogon
Which will give you a list of computers that are in AD but not in ConfigMgr, ordered by their last logon timestamp. Feel free to take this as a starting point to implement your own, way more advanced queries
Automating the processSo far, this has been a one-time effort and only reflects the AD information from the time we executed the query against AD. Now you probably want to keep this information up to date, which makes it necessary to automate this process. There are several ways to automate it. We could run this as a scheduled task. Just wrap the above command in a batch file and execute it on schedule. Easy. Done. Instead of using the scheduled tasks of the operating system, we could also make use of the SQL Server itself and have the SQL Agent run a job regularly. For demonstration purposes, we make use of another feature of logparser, and that is its capability to be scripted. During the installation it also registers a couple COM components, that we can use from any script language (VBScript, PowerShell, etc). And as we can natively execute scripts in a SQL Agent Job. we have a perfect fit. You need to make sure, that logparser is also installed on the SQL Server. So let's create a new Job in SQL Management Studio and give it a proper name Then we add a new step and select ActiveX Script To make it as easy as possible for you and keep this post a bit shorter, you can download a prepared script from CodePlex (Download from here), that contains all necessary steps to call logparser from VBScript. Click on "Open" and point it to the supplied script (WriteADComputerInfoToSQL.vbs) and the content will pop up in the script window. Now all you need is to adjust some values in the script like giving it the proper domain name, sqlserver name etc. Define a schedule that fits to your needs and from then the view you have created before is always up to date. That's it.
As you have seen, this was just a basic scenario. I would be happy to hear/read from your solutions.   |
| Posted: 28 Sep 2012 05:40 AM PDT Psalms 9:9 The LORD is a stronghold for the oppressed, a stronghold in times of trouble. Comments: 0  |
| Rod Trent posted an update in the group IT Fit: Diet Foods: Weight Loss Snacks - Prevention.com Posted: 28 Sep 2012 05:35 AM PDT |
| Rod Trent wrote a new post, Adobe code signing certificate revocation on Oct 4 Posted: 28 Sep 2012 05:31 AM PDT Something to be aware of... Comments: 0  |
| HP, Cisco Networking Gear Work Together in New Private Cloud Data Center Posted: 28 Sep 2012 05:30 AM PDT Hewlett-Packard gave tech reporters and bloggers a tour Sept. 6 of a new data center it developed for Diversified Agency Services (DAS).  |
| You are subscribed to email updates from SCCM by Davis To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No hay comentarios:
Publicar un comentario