Jan 112012
 

Hi All

So for this project I’ve been working on recently, I’ve had to write a script to get the names of nodes and the groups they are a part of out of OMw9.
Thought I’d share the SQL query I used to retrieve the data I needed, though this query will only get the parent group of the node and not anything above that.


SELECT
SUBSTRING(a.object_text,CHARINDEX('n = "',a.object_text)+5,(CHARINDEX('Certif',a.object_text) - CHARINDEX('n = "',a.object_text)-9)) as NodeName,
SUBSTRING(c.object_text,CHARINDEX('n = "',c.object_text)+5,(CHARINDEX('Name = "',c.object_text) - CHARINDEX('n = "',c.object_text)-9)) as GroupName
FROM sto_ov_managednode a
LEFT OUTER JOIN
sto_ov_nodegroupmember b
ON
b.partcomponent LIKE '%'+a.name+'%'
LEFT OUTER JOIN
sto_ov_nodegroup c
ON
b.groupcomponent LIKE '%'+c.name+'%'

With that query you can narrow it down with a where clause at the end, for example


WHERE
c.object_text LIKE '%net devices%'

Will get all nodes in the net devices node group.

Share
Dec 302011
 

So I just created a query that will get today’s date dynamically from the database itself, but I’m sure there must be a simpler way of doing it.
the query I’m using now is :


SELECT
count(Incident_ID)
FROM
INCIDENTSM1
WHERE TITLE = 'Password Reset - Windows Login'
AND
CLOSE_TIME > CAST(DATEPART(year,GetDate()) as varchar)+'-'+CAST(DatePart(month,getdate()) as varchar)+'-'+CAST(DatePart(day,getdate()) as varchar)

Just thought I’d throw this out there in case anyone else needs to do something similar in Microsoft SQL.
If anyone has any suggestions on how to improve that query, feel free to post them in the comments.

Share
Jul 022011
 

Hi All,

I have just worked up a short script to search a SQL server using Javascript.
Using ActiveX and the ADoDB.Connection object this enables me to build a little HTA frontend to do what I need.

Firstly, we create the ADODB object.

var objConnection = new ActiveXObject("adodb.connection");

Then, set the options we want for the connection.
I’m connecting to a SQL server, that is hosted on “server1”, and the database i want is “db1”, and I’m connecting with username and passwords.

var strConn = "driver={sql server};server=server1;database=db1;uid=username;password=password";

Then we open the connection with the options specified.

objConnection.Open(strConn);

Once the connection has been opened, we use the ADODB.Recordset object to execute a query and retrieve results from SQL.


var rs = new ActiveXObject("ADODB.Recordset");
var strQuery = "SELECT * FROM users WHERE username = 'user1'";
rs.Open(strQuery,objConnection);

Once the query has been executed, the results will be stored in the recordset.
You will now be able to view the results using the standard recordset functions.


rs.MoveFirst
while(!rs.eof) {
//Do your thing here
rs.movenext;
}

Share