Custom SQL Query

Custom SQL Query

I apologize if this is somewhere in the forum. I searched but didn’t get anything back that answered my question.

 

Each month I manually have to enter data into a spreadsheet based on the 95% percentile for in and out traffic.  Now that I have 50 or so IP groups and multiple sites this is taking me quite some time to do.  The consolidated report doesn’t display that nor is there a CSV file for the scheduled report for me to manually calculate the 95%. This means I’ll need to query the data directly from a script to enter it in the spreadsheet.    I’ve read through the data-dictionary.xml file and can open the database to do a query but I’m having a hard time understanding where to pull the data from.  I can pull data from the 10min – weekly conversations per IP_Groups  but I’m trying to do this for entire month so I’m not sure exactly how to go about it.  Can I do this with a BETWEEN function in the conversationweekly for IP_GROUP_ID or is there another table I’m not seeing to get this data? 

 

What it comes down to is this.

 

I have 10 sites each with a IP group named after each site.  Each IP group is named XXX-YYYYY  XXX is a country code or city code and YYYYY is a client code. Each XXX is tied to a specific router and I will have anywhere from 10-40 clients at one XXX site.  (BTW Nested treeview IP groups would be wonderful)  Every month I have to go into the website and manually run a report for each IP groups for the month and get the 95% and put that in a custom excel sheet that is used for a report to finance, IT and Telecom.   I’m trying to automate as much of this as possible.  My original thought was to email a scheduled report with the csv file save it to a directory and script it so pull the data from the directory.  That is possible but that report is only available to be manually run (no csv option for consolidated emailed reports) and download it for each group that would take the same amount of time if not longer.   I did look at the billing report and it does give me 95% but either merged calculated or separate with highest being considered.  Our billing is done based on a 100% recovery for the line cost so a $ amount for Kbps doesn’t work as I can’t calculate that until after the end of the month based on the usage.   My other option is to try script each page with a query to pull the data directly in and make the 95% calculation.  Something along the lines of SELECT * FROM UnknownTable WHERE IP_Group_ID = x  AND SomeDateTable between ‘1/1/2010’ AND ‘1/31/2010’;

                New to ADSelfService Plus?