philsh
New Member.
1260 views

SQL query to return the following

Looking for a SQl query that can return the network IP address for all servers both netbios and FQDN

For example,

ServerA Network Interface IPAddress.



Also a sql query that can return the servername and KS's that are running on that server and what is their severity?

I integrate AM 9.1 to HP and using the netiq HP adapter forward over any critical events of severity 6 to our notification application.

So looking for any server and KS that meets this?
0 Likes
1 Reply
andy_doran Absent Member.
Absent Member.

Re: SQL query to return the following

pshastany;2452811 wrote:
Looking for a SQl query that can return the network IP address for all servers both netbios and FQDN

For example,

ServerA Network Interface IPAddress.



Also a sql query that can return the servername and KS's that are running on that server and what is their severity?

I integrate AM 9.1 to HP and using the netiq HP adapter forward over any critical events of severity 6 to our notification application.

So looking for any server and KS that meets this?


For the first one - 2 queries below. One uses the discovered data which may be out of date (if an IP address changes and no new discovery is made, or if maybe DHCP is used and the address changes). The other one uses the address that is used when the agent updates the MS to tel lit what the "active" IP address is. Run the queries in the QDB.

For the 2nd one it is not clear what you mean. There is a report already (JobInfo) that runs which can identify all active jobs per server and what their parameters are. If you are wanting to know what jobs are running and what values are set for the parameters that control event severity that would be the one to use.

There is no standard name for a parameter used for setting the event severity in a KS - so there is no simple query that could be used. Some KSs have multiple events they can raise, each with a different severity. So there is no way to query just for event severity parameters (generally speaking most parameters for event severity have a property of the value - "unit" which gets set to "sev" or "severity" but that's not a cast iron guarantee).

Or do you mean you want to know what jobs are running and tie that to the events that have already been generated to see the jobs with events and their severity?

IP Address queries:-

--SQL START
select
ServerName = O.Name,
OS = O.String1,
DiscoveredIPAddress = case when N.String4 is null
then N.String2
else N.String4 end
from
dbo.Object O with (nolock)
inner join dbo.fnListAllMachineType(1) T
on T.TypeID = O.TypeID
inner join dbo.Object N with (nolock)
on N.RootMachineObjID = O.ObjID
inner join dbo.ObjectType NT
on NT.TypeID = N.TypeID
where
NT.Name in ('NT_NetLanObj', 'UNIX_InterfaceObj')
and N.String2 != ''
order by
O.Name

select
ServerName = O.Name,
OS = O.String1,
RegisteredIPAddress = I.Agent_IP
from
dbo.Object O with (nolock)
inner join dbo.fnListAllMachineType(1) T
on T.TypeID = O.TypeID
left join dbo.Agent_IPAddress I
on I.Agent_Id = O.ObjID
where
O.String1 is not null
order by
O.Name
--SQL END
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.