Welcome Serena Central users! CLICK HERE
The migration of the Serena Central community is currently underway. Be sure to read THIS MESSAGE to get your new login set up to access your account.
Highlighted
Absent Member.. BigED214 Absent Member..
Absent Member..
927 views

Post your SQl code!

I see lots of good SQl code for custom reports as well as plenty of threads with instructions on how to use MySQl or ODBC (all in the manuals BTW). However it is all spread out all over the forum.

 

How about we all post here on this thread any new reports, extracts or SQL statements that we have used to get around the canned reports or enhance our use of the Aggregate data! This si not to post connectivity questions, this assumes you have a ODBC and a MySQL Connection. Let post SQL code here that we can CUT & PASTE!

 

Points and Kudos for the best and most creative?

 

E

Tags (1)
0 Likes
9 Replies
Jeff Sikorski Absent Member.
Absent Member.

Re: Post your SQl code!

attached a zip with a bunch of MySQL DDMI queries.

 

Some of these I haven't touched in a couple years.  You will still need a decent understanding of MySQL / SQL syntax to mold these to fit your environment... but the basics are there!

Enjoy !

~Jeff

Absent Member.. BigED214 Absent Member..
Absent Member..

Re: Post your SQl code!

Cool! I will open and steal all the good bits.

 

I am working on some new SQL for some reports and request. I will compile those and zip up as well. Good Idea and then we can open and use what we need, and comment here.

 

Thanks!

 

E

0 Likes
Absent Member.. BigED214 Absent Member..
Absent Member..

Re: Post your SQl code!

Wow, lots of good ideas. I already modified your hardware report for our OS version. easy cheesy.

 

( d.Device_Tag like '%HP-UX%'or
d.Device_Tag like '%Red Hat%' or
d.Device_Tag like '%ESX%' or
d.Device_Tag like '%AIX%' or
d.Device_Tag like '%Linux%' or
d.Device_Tag like '%SUSE%'
)

 

I may find some of the SAI code to work for us also. Any Generic SQl for find software on a server or servers with "X" software is useful code. I do need some help with finding Network devices whom I have no idea of the device_tag. i.e.

 

I have Cisco Aironet WAP's and these are SNMP managed, so I know the vendor, make/model and details so this is easier to find. However how do I write SQl for a device that I have no idea of the device_tag? Like these Appliances all over our network?

 

0 Likes
Jeff Sikorski Absent Member.
Absent Member.

Re: Post your SQl code!

I haven't fully experimented with the network-only devices like printers and switches, but I did find out recently that you can link up the serialnumber table with the device table and get some good information that way.  Most devices have multiple entries in the serial # table... so I found that grabbing the SerialNumber_Type = 'Chassis' was the one I was actually looking for.  But if you are just starting to play around with it, I would recommend commenting out the 'chassis' line and seeing how it looks in your environment. 

 

I actually very rarely query the DDMI database... I try to keep my reports out of Asset Manager only and only use DDMI data for investigation type stuff.  But if you dont have AM and DDMI is it's own entity, then those queries should get you started !

 

 

select d.device_label

      ,d.device_name
      ,d.device_tag
      ,d.device_preferredipaddress
      ,sn.*


    from
           device d
          ,serialnumber sn

         
    where
         d.device_id = sn.device_id
           and serialNumber_Type = 'chassis'
--          and d.device_tag like '%print%'
   

0 Likes
Absent Member.. Fazlur Absent Member..
Absent Member..

Re: Post your SQl code!

Good to see this thread. I can use Asset manger but my only option to install asset manger in the Same DDMi machine. My understanding it will not work if DDMi and Asset manger in the same machine. Would like to know how rich the reporting in Asset manger?

Can I install Asset manager and Connection manager in a standard laptop and just import the DDMi database to generate reports? any help will be great.

 

I use DDMi mainly to query and generate reports. I need help to generate a query

1) where I will be able to see "unmanaged devices" via CDP only. I am specially interested in Network devices.

In simple, network devices without SNMP and have some information via CDP.

0 Likes
kemalates Absent Member.
Absent Member.

Re: Post your SQl code!

I have never used AM, but I am guessing it has better reporting from DDMI.

you can check HW and OS specs for AM to decide to install it in your laptop.

 

I am not sure what you mean ""unmanaged devices" via CDP".

DDMI reads CDP data from snmp-managed devices mainly for connectivity purpose (if you have Topology license) and for device recognition purpose (in case some devices seen in CDP table are unmanaged). DDMI doesn't discover a device via CDP.

 

The data collected from CDP tables is not imported into Aggregate DB.

0 Likes
Absent Member.. BigED214 Absent Member..
Absent Member..

Re: Post your SQl code!

Reporting as always varies by products. In HP's case it can be drastic depending on where the product originally came from ( Peregrine, Compaq, Mercury etc) and what if any integration has been done with it into the other HP products. especially connect-it or UCMDB (them selves other tools developed outside of HP via vendor and product aqusitions).

 

Reporting like this thread by using SQl direct to the database then removes all these issues of canned reports, out of the box screens or extracts. Now with a basic knowledge of the tables or schema you can extract easily with ODBC using Excel, MS Query or your prefered tools like Toad or like for DDMI's MySQL I use  Oracle's MySQL Workbench...

 

While at Pepsi we use both AM and DDMi with Connect-it and SAM (SACM) we have very little in house expertise with MySQl as we use DB2, Oracle and MS SQl exclusively. So here reporting can be problematic if vendors do not support htese versions.

 

For our future plans we will be extracting all our UCMDB data (Oracle OODB), AM & SM as wellas the DDMi to our Business Objects Universe. There we will have views, reports and self service available for the organization. The future with UD 10 will ease some of our use of MySQL and DDMi issues.

 

E

0 Likes
Absent Member.. Fazlur Absent Member..
Absent Member..

Re: Post your SQl code!

Thank you.

 

Would you please give me the link for AM forum. I always get lost in HP sites. I can see there is another forum for DDMI now in the HPLN portal. https://hpln.hp.com//node/128/og/forum

 

I can see some devices in the databases that the information was collected via CDP from the SNMP managed devices and they were in the aggregate database.

 

I did a quick check from the device table and was able to link the information in Operating system table and Model table.

 

The details about CDP devices you can find from the "Report State" and "Report details" tables.

 

0 Likes
Absent Member.. Fazlur Absent Member..
Absent Member..

Re: Post your SQl code!

Hi Jeff,

 

I found one of your query about McAfee ePolicy Orchestrator Agent to identify version 3 and less. The query is working fine for me. Would like to know why we will generate this output? Is there any problem or issues with McAfee ePolicy Orchestrator Agent 3 or less?

 

If you think there are issues with version 3 or less than any link for more information will be great. Thanks for the sql queries.

 

 

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.