Sysinternals Homepage
Forum Home Forum Home > Sysinternals Utilities > BgInfo
  New Posts New Posts RSS Feed - Querying the BGINFO Database
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Querying the BGINFO Database

 Post Reply Post Reply
Author
Message
Arthur169 View Drop Down
Newbie
Newbie
Avatar

Joined: 14 August 2017
Location: Boston
Status: Offline
Points: 8
Post Options Post Options   Thanks (0) Thanks(0)   Quote Arthur169 Quote  Post ReplyReply Direct Link To This Post Topic: Querying the BGINFO Database
    Posted: 30 October 2017 at 4:38pm

Every morning and at sporadic times otherwise; some basic specs and versions of various programs get written to a database table.   This has worked for a long time and continues to work.  We used to have a way to run a query/report that would show only the first time something got changed.    That process broke.

Example:  WebSvr_XYZ used had 2G of RAM since inception before getting additional RAM allocated a few months later.  Then a year after that, it was given a new IP address.

So in that case there would be 3 records as a result from over a year of daily records.  Output fields being TimeStamp, RAM, IP address

  • First entry
  • RAM Upgrade
  • New IP
I know this is more a Query question, but with the user-base that BGINFO has, I hope I’m not the only one using it this way. 

Can any kind souls help point me down the right path?   I am very surprised that I couldn’t find any type of BGInfo Audit/Change-control solutions already in place.  It’s a godsend!

Back to Top
Arthur169 View Drop Down
Newbie
Newbie
Avatar

Joined: 14 August 2017
Location: Boston
Status: Offline
Points: 8
Post Options Post Options   Thanks (0) Thanks(0)   Quote Arthur169 Quote  Post ReplyReply Direct Link To This Post Posted: 24 November 2017 at 10:01pm
Without any replies, I re-read it, and thought it might be confusing.  Rather than start a new thread, I thought I'd re-write to the comments...

My company uses BGInfo religiously to keep track of ~80 properties on over 200 webservers.   Those fields are a mix of server stats (IP address, OSVer, HyperV host) and versions of various installed software components.   A scheduled task writes this information every day all webservers to a single database (currently >300K records).   We need a query (to eventually be fed into a report) to give us a time of when something has changed on any given webserver.  A sort of automated change control if you will.

Example:  WebSvr_XYZ used had 2G of RAM since inception before getting additional RAM allocated a few months later.  Then a year after that, it was given a new IP address.

Server           Time_stamp       Host       IP                       RAM

WebSvr_XYZ      June 1, 2016       Virt5a    192.168.10.45     2G
WebSvr_XYZ      June 2, 2016       Virt5a    192.168.10.45     2G

WebSvr_XYZ      Aug 20, 2016       Virt5a    192.168.10.45     4G
WebSvr_XYZ      Aug 21, 2016       Virt5a    192.168.10.45     4G

WebSvr_XYZ      July 18, 2017       Virt5a    192.168.20.105  4G
WebSvr_XYZ      July 19, 2017       Virt5a    192.168.20.105  4G
WebSvr_XYZ      July 20, 2017       Virt5a    192.168.20.105  4G 

When running a query against WebSvr_XYZ, the output against over 540 records (6/1/16 -> present) would be

June 1, 2016       Virt5a    192.168.10.45     2G
Aug 20, 2016       Virt5a    192.168.10.45     4G
July 18, 2017       Virt5a    192.168.20.105  4G

I’ve tried the select distinct against the table them Joining it against the full table, Joining on all relevant fields and using a MIN(Timestamp) to get the first.  But I either get bad timestamps or no results at all.

~Alan

 

Back to Top
WindowsStar View Drop Down
Senior Member
Senior Member
Avatar

Joined: 30 June 2010
Status: Offline
Points: 787
Post Options Post Options   Thanks (0) Thanks(0)   Quote WindowsStar Quote  Post ReplyReply Direct Link To This Post Posted: 26 November 2017 at 3:18am
Query is beyond what BGInfo does. If you are writing this to a SQL database then there are a lot of queries you can do from within the management studio, google can help you. If not then I would look for utilities that support your database for queries. Sorry...hope that helps. -WS
Back to Top
Arthur169 View Drop Down
Newbie
Newbie
Avatar

Joined: 14 August 2017
Location: Boston
Status: Offline
Points: 8
Post Options Post Options   Thanks (0) Thanks(0)   Quote Arthur169 Quote  Post ReplyReply Direct Link To This Post Posted: 01 December 2017 at 9:44pm
Clap   Solution found.  Way too simple, and not enough publicity.

From:  https://stackoverflow.com/questions/1021973/select-distinct-from-usercolumn-and-minimum-value-of-dates-for-each-field-in-use

So for my Dumbed Down example:
Select WebServer, min(TimeStamp), HyperVHost, IP_Address, RAM from BGIOnfo_Table
Where WebServer='XYZ'
Group by WebServer, HyperVHost, IP_Address, RAM
Use the same Query in SRSS.  Parameter value on the webserver, Conditional 
formatting for the background color on each field, so when you look at it, 
each CHANGE is highlighted.   The Staff is happy.
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 11.06
Copyright ©2001-2016 Web Wiz Ltd.