Deployment Solution

Hello, all..

I know you can get the list of workstations using DS, and I know you can run SQL queries from a DS job, but I just can't seem to put the two together in my head..

I basically just want to get a list of all of the workstations listed on DS.

I know from looking at other forums that you can WRITE to the SQL server via a DS job, but I'm wanting to pipe the results of a query to a text file.

For example:
Export a filter from DS query (http://www.altirigos.com/vbulletin/deployment-server/9163-export-filter-ds-query.html)


so I'm assuming I just need the first bit (SELECT DISTINCT dbo.computer.name
FROM dbo.application)


and this thread:
Finding blank Computer Product Name? (http://www.altirigos.com/vbulletin/deployment-server/11162-finding-blank-computer-product-name.html)

has a post from Nick on editing the SQL directly..

However, I can't find one which exports the results to a file..


Any chance someone can help me with this?

Kind regards,
Gerard

If I'm understanding, you want a list of workstations in DS.

Deployment Solution 6.9 and later lets you export computers to XML.  File, Import/Export, Export Computers.  What version of the DS are you using?

Here you go Gerard, save this as a .VBS file and run it (at your own risk ;) ).
YOU NEED TO RUN IT WITH AN ACCOUNT THAT HAS RIGHTS TO QUERY THE DB. That last bit is very important in case you couldn't tell :)

(I've also included some REMmed out code that allows you to connect using no explicit username and password for security reasons. See caveat in upper case above if you do this. Also, it assumes you've named your DS database as 'express' and that you have a directory called 'c:\temp' available)

See what you think
R
------------------------------------------
Const adOpenStatic = 3
Const adLockOptimistic = 3

Dim objFSO, objConnection, objRecordSet, objLog

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

Set objLog = objFSO.CreateTextFile("C:\temp\scripts\dscompnames.log")
objConnection.Open _
    "Provider=SQLOLEDB;Data Source=<servernamehere>;" & _
        "Trusted_Connection=Yes;Initial Catalog=express;" & _
             "User ID=<DOMAIN\Usernamehere>;<passwordhere>;"

' objConnection.Open _
'     "Provider=SQLOLEDB;Data Source=<servernamehere>;" & _
'         "Trusted_Connection=Yes;Initial Catalog=Express;"

objRecordSet.Open "SELECT Name FROM dbo.computer", _
        objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.RecordCount
    objLog.WriteLine objRecordSet.Fields("Name")
    objRecordSet.movenext
Loop

objRecordSet.Close
objLog.Close

Set objRecordSet = Nothing
Set objConnection = Nothing
Set objFSO = Nothing

----------------------------------------------

I'm using 6.9.

The export function does work - up to a point - it produces a monumentally huge XML file (we have about 4500 PCs on our DS).
While I could write a small script to extract the details, I was hoping there was some easier way of doing it.
Which it appears some kind soul has just posted :)

Sir, you just rocked my world!

That worked fantastically... Thank you so much!!

Regards,
Gerard

And now I get to look like a prize leech!!

The powers that be have now asked me to try to prepare another report, namely one which includes the IP address of the computer - GAH!

I know that the DS token for the IP address is NIC1IPADDR, so would that make the query something like:

SELECT NIC1IPADDR from dbo.somethingorother
where Name like

Stop laughing, you SQL folk! :)

Any help you can provide would be invaluable!

Regards,
Gerard

Ok,
you say your superiors in the High Command have asked you for the Ip address of each box. Fine, except....of course, each box can have more than one.
So what I'll provide you with initially is what gets displayed on screen in DS (DS best guess at "main" IP address)

I noticed a small error in there as well. I left the "Trusted Connection = Yes" set which means "ignore the user id and password and connect with whatever I'm currently signed on as". Change this to "No" if you want to specify something different.

Let me know if this works ok, Gerard would you? If it does, could you also set the status of the thred to "Solved" please?
if not, we can look at getting more than one IP in there but it starts getting a bit more complicated then...

thx
R


-------------------------------------------
Const adOpenStatic = 3
Const adLockOptimistic = 3

Dim objFSO, objConnection, objRecordSet, objLog

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

Set objLog = objFSO.CreateTextFile("C:\temp\scripts\dscompnames.log")
objConnection.Open _
    "Provider=SQLOLEDB;Data Source=<servernamehere>;" & _
        "Trusted_Connection=Yes;Initial Catalog=express;" & _
             "User ID=<DOMAIN\Usernamehere>;<passwordhere>;"

' objConnection.Open _
'     "Provider=SQLOLEDB;Data Source=<servernamehere>;" & _
'         "Trusted_Connection=Yes;Initial Catalog=Express;"

objRecordSet.Open "Select dc.[name], dcd.ip_address from dbo.computer dc INNER JOIN dbo.computer_display dcd on dc.computer_id = dcd.computer_id", _
        objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordSet.EOF
    Wscript.Echo objRecordSet.RecordCount
    objLog.WriteLine objRecordSet.Fields("Name")
    objLog.WriteLine objRecordSet.Fields("IP_Address")
    objRecordSet.movenext
Loop

objRecordSet.Close
objLog.Close

Set objRecordSet = Nothing
Set objConnection = Nothing
Set objFSO = Nothing
---------------------------------------------

My thanks,and apologies..

Thanks for getting back to me.

Apologies - firstly because I hadn't realised the etiquette here - I will most definitely mark it as Solved.

And second apologies because the script runs so far, and then fails with "Type mismatch: 'WriteLine'"

In an effort to not look like a complete sponger, I've taken a look at the line above it, and the computer in question is a pre-defined computer account, so my theory is that because it won't have an IP address, it's returning an empty value, and throwing up the error.

I changed this line:
objLog.WriteLine objRecordSet.Fields("Name") & ":" & objRecordSet.Fields("IP_Address")

and that seems to solve the problem - presumably the writeline is failing when you try to write a new line of nothing, but it doesn't seem to mind appending it.


I will mark this as solved, but I have one additional question so didn't want to in case it makes it read only (should you wish to respond, of course heh)..


May I ask - is there a PDF/similar online where I might find the assorted database fields that you're using for your matches? I could find such information for NS, but that doesn't apply to our environment.

I ask because I might use this VERY simple exporting method for quick reports on things like Add/Remove Programs.

Kind regards (again!),
Gerard

Well spotted on the Null value mismatch - yes it doesn't like writing "NULL" but it can always write nothing....
Unfortunately, there isn't a DB map. It's just good old blood, sweat and tears...
There are certainly some "interesting" tables: "dbo.computer", "dbo.computer_display", "dbo.nics" to name a few. Your best bet is to browse through using SQL Management Studio and keep an eye open for columns of interest.

For apps in DS, you might want to look at "dbo.application". This gives you .e.g. (only one sample result here)

computer_id    app_id     name              description              Publisher                                                   
5000002          169            Wireshark      Wireshark 1.0.2     The Wireshark developer community,http://www.wireshark.org 
Version
1.0.2  0

DS doesn't inventory Add/Remove programs per se, you'll need to go back to NS for that one but "dbo.application" will give you a quick and dirty inventory.
If you wanted to link that back in to the script, it'd be something like:

"Select dc.[name], dcd.ip_address, dca.[name] as 'AppName', dca.[Description], dca.publisher, dca.version from dbo.computer dc INNER JOIN dbo.computer_display dcd on dc.computer_id = dcd.computer_id INNER JOIN dbo.application dca on dcd.computer_id = dca.computer_id "

You'd then need to add in objlog.writeline for each column name
e.g. objRecordSet.Fields("AppName")
objRecordSet.Fields("Description")

etc etc
The "Name" field could be interesting as it's the same for servername and appname hence the reason I returned the app name as "AppName".

hth
r

Thanks for the interesting information..

It's unfortunate Altiris (cough, Symantec)  haven't taken a few minutes to compile this information anywhere. It seems remarkably remiss of them to provide info on one product (NS), but not the other (DS) - are any of you folks reading this? :)

I'm hoping I can install SQL Management Studio (or similar) on a workstation someplace, as I can't get direct access to the main SQL server console. If so, I'll most definitely be investigating the tables.

I'd like to sincerely thank you for your time and patience with helping me out with this - this script has quite literally saved me countless hours of work. It's also given me the incentive to perhaps look at using it with AutoIt as I'm more familiar with that than VB. A quick look at the forums suggests folk are using that to do SQL things, so fingers crossed.

Kind regards,
Gerard