Reporting custom registry values

Home

Forums

Feature Requests

Beta Issues

SysAid Resources

Documentation

Support

Hello Guest,

DOWNLOAD FREE EDITION

Recent Topics

Hottest Topics

Online Members

Member Listing

Advanced Search

Reporting custom registry values

Forum Index » Asset Management

◄ 1 2 Go

Go to page...

Author

Message

23/04/2009 18:35:42

Subject: Re:Reporting custom registry values

techguy
SysAid Mod

Meet me in Vegas - SysAid technology Conference - 28-30/4/2010

Joined: 11/06/2008
Messages: 1451
Location: England
Offline

Sorry it does not display the value of the registry key, all it does is filter the list so it only displays assets that contain that registry key.

Need help? Try the SysAid wiki first! - http://sites.google.com/site/sysaidwiki

05/05/2009 11:24:32

Subject: Re:Reporting custom registry values

EbroIN
Elite SysAider

Joined: 25/03/2009
Messages: 244
Offline

techguy wrote:Sorry it does not display the value of the registry key, all it does is filter the list so it only displays assets that contain that registry key.

Thanks for your follow up (I am sooooo late)...

Well - is there ANY way to display that values? Maybe I can create a view on database level?

Thanks for your feedback!

21/05/2009 07:02:37

Subject: Re:Reporting custom registry values

michalp
SysAider

SysAider from release 5.1

Joined: 18/06/2008
Messages: 26
Location: London, UK
Offline

We have same problem here. I am more than happy to use ODBC driver to database or basically ANY form of extracting that information from the system. Could somebody from SysAid point me/us into right direction, where this information about Key (I believe it is in main XML config file?) and Value (it has to be somewhere on the server?) is stored? It would help us a million, as it is great feature for pro active admins.

22/05/2009 04:05:22

Subject: Re:Reporting custom registry values

michalp
SysAider

SysAider from release 5.1

Joined: 18/06/2008
Messages: 26
Location: London, UK
Offline

I have found crude way of extracting that information using ODBC driver (MySQL in my case with MS QUERY).

Just use this SQL command:

LOCATE finds you position of first letter of Reg Key you are after.
47 is manually calculated length of Reg Key (use small macro TEXT TO COLUMNS in Excel) edit: or obviously you can use SQL LENGTH() function

39 seems to be a distance to value of that Reg Key
3 is manually determined length of value you are after, in most cases it would be constant or predictable

I am pretty sure this code could be better optimized, so please share your opinion/ideas. I used parameters with VBA and Excel in the past, so I think they can be used, to make process very automatic.

This message was edited 2 times. Last update was at 22/05/2009 04:30:40

22/05/2009 05:19:38

Subject: Re:Reporting custom registry values

EbroIN
Elite SysAider

Joined: 25/03/2009
Messages: 244
Offline

Hi Michalp,

yeah, I also found out that the registry values are stored within the column 'inventory_xml' in 'sysaid.computer' like that way:

I dare to say that for SQL SERVER 2000 Users this might be a real pain in the ass (please excuse me). I can not rely on functions like LOCATE because they are not known as SQL2000 statement. But SQL SERVER 2000 can handle XML documents so my way of displaying these Values should be parsing the XML value, dump the relevant components into a temporary table and creating a VIEW that displays the data that I am looking for. Afterwards drop that table.

I'm working on this view right now and will post a valid TSQL Statement if I've finished my work.

@Ilient: Why don't you drop those values into a table?

Best regards - EbroIN

22/05/2009 07:08:24

Subject: Re:Reporting custom registry values

EbroIN
Elite SysAider

Joined: 25/03/2009
Messages: 244
Offline

ok,

this one is a quite DIRTY HACK but this VBS drops all Values to the screen, tabseparated...

We have a SQL Server 2000 here although I expect that this script might also work with mySQL. You might have to adjust the ADVANCED_SETTINGS here (especially the connection string). This script uses your current NT logon credentials, so if you need to pass a user and password, you have to modify the SQL Connection string.

Instructions:
- CopyPaste the code into a textfile like C:\testDB.vbs
- Edit the "CONFIGURE YOUR SERVER SETTINGS HERE" section and enter your servername and DB Name
- run the script with CSCRIPT!

Option Explicit
' #######################################################################
' #
' # getRegValues.vbs - this litte script analyzes RegKeys collected by SysAid and outputs them as TSV array to the screen
' # 
' # BE AWARE TO USE CSCRIPT INSTEAD OF WSCRIPT!
' # 
' # 2009-05-22 - EbroIN
' #
' # This script can be freely modified and used - In no event unless required by applicable law or agreed to in writing will any copyright holder, or any other party who may modify and/or redistribute the program as permitted above, be liable to you for damages, including any general, special, incidental or consequential damages arising out of the use or inability to use the program (including but not limited to loss of data or data being rendered inaccurate or losses sustained by you or third parties or a failure of the program to operate with any other programs), even if such holder or other party has been advised of the possibility of such damages. 
' # 
' #######################################################################

' ################## CONFIGURE YOUR SERVER SETTINGS HERE
CONST DB_SERVER = "<SET_YOUR_SERVER_HERE>"
CONST DB_CATALOG = "<SET_YOUR_DATABASE_HERE>"
' ################## CONFIGURE YOUR SERVER SETTINGS HERE

' ################## ADVANCED CONFIGURATION HERE - USE WITH CAUTION!
DIM SQL_CONNECTION_STRING, SQL_QUERY
SQL_CONNECTION_STRING = "provider=SqlOleDb;Server=" & DB_SERVER & ";Trusted_Connection=Yes;Initial Catalog=" & DB_CATALOG
CONST XML_HEADERMARK_START = "<agen:regName>"
CONST XML_HEADERMARK_END = "</agen:regName>"
CONST XML_VALUEMARK_START = "<agen:regValue>"
CONST XML_VALUEMARK_END = "</agen:regValue>"
CONST DATAFIELD_COMPUTERNAME = "computer_name"
CONST DATAFIELD_INVENTORYXML = "inventory_xml"
SQL_QUERY = "SELECT " & DATAFIELD_COMPUTERNAME & ", " & DATAFIELD_INVENTORYXML & " FROM sysaid.computer"
CONST MAX_REG_VALUES = 5
' ################## ADVANCED CONFIGURATION HERE - USE WITH CAUTION!

' ################## DECLARE VARIABLES
dim objDB_Connection, objDB_Recordset
set objDB_Connection = CreateObject("ADODB.Connection")
set objDB_Recordset = CreateObject("ADODB.Recordset")
' ################## DECLARE VARIABLES

' ################## OPEN CONNECTIONS
objDB_Connection.Open SQL_CONNECTION_STRING
objDB_Recordset.Open SQL_QUERY, objDB_Connection
' ################## OPEN CONNECTIONS

' ################## ITERATE THROUGH RECORDSET
objDB_Recordset.MoveFirst
while not objDB_Recordset.EOF
	wscript.echo objDB_Recordset.Fields(DATAFIELD_COMPUTERNAME) & vbTab & getRegInfo(objDB_Recordset.Fields(DATAFIELD_INVENTORYXML))
	objDB_Recordset.MoveNext
wend
' ################## ITERATE THROUGH RECORDSET

'------------------------------------------------------------------------------
' function: getRegInfo
' this function extracts the custom collected registry values from sysaids inventory_xml 
' argument:	inventoryXmlString - the appropriate record from the database <String>
' returns: 	a tsv delimited string with all values
'------------------------------------------------------------------------------
public function getRegInfo(byVal inventoryXmlString)
	dim sPos, ePos, i, rStr
	rStr = ""
	sPos = 1	
	
	for i = 1 to MAX_REG_VALUES

if sPos > 0 then sPos = InStr(sPos, inventoryXmlString, XML_HEADERMARK_START, vbTextCompare)

if sPos > 0 then
			
			ePos = InStr(sPos, inventoryXmlString, XML_HEADERMARK_END, vbTextCompare)
			if ePos > sPos  then
				if i > 1 then rStr = rStr & vbTab	' there is already a record present, so add a tab...
				
				' now add the "key" value
				rStr = rStr & Mid(inventoryXmlString, sPos + Len(XML_HEADERMARK_START), ePos - (sPos + Len(XML_HEADERMARK_START)))
				rStr = rStr & "='" & getRegValue(inventoryXmlString, sPos) & "'"
				
				sPos = ePos	' # set marker to end to continue or not... 
			end if
		end if

next
	
	getRegInfo = rStr
end function

'------------------------------------------------------------------------------
' function: getRegValue
' assuming after the xml header follows the value, this function just strips the next following value after the position sPos
' argument:	inventoryXmlString - the appropriate record from the database <String>
' argument:	sPos - the position right after the header has been found <Integer>
' returns: 	this simple value or an emtpy string
'------------------------------------------------------------------------------
public function getRegValue(byVal inventoryXmlString, byVal sPos)
	dim ePos, i
	getRegValue = ""
	if sPos > 0 then sPos = InStr(sPos, inventoryXmlString, XML_VALUEMARK_START, vbTextCompare)
	if sPos > 0 then
		ePos = InStr(sPos, inventoryXmlString, XML_VALUEMARK_END, vbTextCompare)
		if ePos > sPos  then
			getRegValue = Mid(inventoryXmlString, sPos + Len(XML_VALUEMARK_START), ePos - (sPos + Len(XML_VALUEMARK_START)))
		end if
	end if
end function

This hack is quite dirty but works good for me... I do not parse the xml file because I search the string quite featherbrained to locate the entries. This hack was quicker than implementing MSXMLDOM. Scripting.Dictionary might also do a good job especially dropping large tables directly to Excel files. I do not need such detailed output but there shouldn't be any problem to generate an excel file with VBScript.

But it would be even better if custom registry values are entered directly in the DBMS and not in this XML ntext column.

Hope this helps somebody out...

best regards,

EbroIN

This message was edited 1 time. Last update was at 22/05/2009 07:12:44

10/09/2011 19:03:00

Subject: Re:Reporting custom registry values

ctprobate
SysAider

SysAider from release 7.5

Joined: 06/09/2011
Messages: 2
Offline

Here is the SQL (SQL 200

:

DECLARE @computer VARCHAR(20)
DECLARE @xml XML

DECLARE @Registry TABLE (
ComputerName VARCHAR(20),
regName VARCHAR(500),
regValue VARCHAR(100)
)

DECLARE computer_cursor CURSOR FOR
SELECT computer_name FROM computer

OPEN computer_cursor
FETCH NEXT FROM computer_cursor
INTO @computer

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @xml = REPLACE(CAST(inventory_xml AS VARCHAR(MAX)),'agen:','') FROM dbo.computer c WHERE computer_name = @computer

INSERT @Registry
(ComputerName,regName,regValue)
SELECT
@computer,
doc.col.value('regName[1]', 'varchar(500)') [regName],
doc.col.value('regValue[1]', 'varchar(100)') [regValue]
FROM @xml.nodes('/inventory/registryValue') doc(col)

FETCH NEXT FROM computer_cursor
INTO @computer
END

CLOSE computer_cursor
DEALLOCATE computer_cursor

SELECT * FROM @Registry

01/05/2012 18:53:45

Subject: Re:Reporting custom registry values

pdlp
Super SysAider

Joined: 25/11/2009
Messages: 83
Offline

This would be really useful....

Is it possible to use iReports for this?

This message was edited 1 time. Last update was at 01/05/2012 19:02:35

Forum Index » Asset Management

◄ 1 2 Go

Go to page...

Go to:

Help Desk Software
Free Help Desk Software
Free Asset Management Software
SysAid Helpdesk Software
Web Based Help Desk Software
SysAid Help Desk Forum
General IT Discussion Forum
SysAid CSS Customer Service Software
Customer Support Software

   SysAid Technologies Ltd.
   Toll-Free phone center (U.S.): 1-800-686-7047
   Offices - U.S.617-231-0124
   Israel:+972-3-533-3675
   Skype account:ilient
   Email:helpdesk@sysaid.com
   Optimized by SEO Israel
   SysAid logos and other SysAid Technologies marks
   are trademarks or registered trademarks of
   SysAid Technologies Ltd.
   All Rights Reserved by SysAid Technologies Ltd.
   2002-2011

   Live Support Hours
   07:00 AM - 09:30 PM (UK)
   03:00 AM - 05:30 PM (EDT)

   We provide worldwide services, and we do our best
   to match the working times of customers from
   different time zones.

   SysAid Help Desk Software and Asset Management Software