Connectwise SQL Query -> Get resent invoiced customer contact list for Microsoft Partner Survey

Do we have any Connectwise Sql hackers out there that might need a SQL script to grab a list of all primary company contacts for all companies invoiced over the last 6 months so you can submit that to Microsoft Partner Portal for the survey Microsoft expects you to send out twice a year?  You know the one where you submit a excel spreadsheet of those customers and Microsoft send them a survey on your behalf.

Well here is a quick SQL Query that will produce that very list for you. Just export to a tab delimited text file.

 

SELECT [dbo].[v_Company_Contact].[Company_Name], [dbo].[v_Company_Contact].[First_Name], [dbo].[v_Company_Contact].[Last_Name], [dbo].[v_Contact_Communication_Type].[Description] AS Email,[dbo].[Contact_Communication].[Description] AS Phone
FROM [Your Database Name].[dbo].[v_Company_Contact]
JOIN [dbo].[v_Contact_Communication_Type] ON [dbo].[v_Contact_Communication_Type].[Contact_RecID] = [dbo].[v_Company_Contact].[Contact_RecID]
JOIN [dbo].[Contact_Communication] ON [dbo].[Contact_Communication].[Contact_RecID] = [dbo].[v_Company_Contact].[Contact_RecID]
WHERE Company_RecID IN (SELECT Company_RecID
FROM [Your Database Name].[dbo].[v_rpt_Invoices]
WHERE Date_Invoice between (‘01/01/2012‘) and (‘07/01/2012‘)
Group By Company_RecID)
and [dbo].[v_Company_Contact].[Default_Flag] = ‘1’
and [dbo].[v_Contact_Communication_Type].[Communication_Type_RecID] = ‘1’
and [dbo].[Contact_Communication].[Communication_Type_RecID] = ‘2’

 

Find the line in the query “WHERE Date_Invoice between (’01/01/2012′) and (’07/01/2012′)”  and change the dates for any range you want to include also change the name of the database to reflect your CW database. WordPress changes ( ‘ ) in to ( ` ) when you copy and paste SQL query so you may need to go through query and revert them back to ( ‘ ). See dates and any thing that has a “=” before it.

 

I hope my SQL Foo helps some one out there in Connectwise land

 

Cubert 😎

PFSense 2.0 – Limiting users Upload and Download Speeds by Limiting Bandwidth.

Manage your Bandwidth

PFSense is by far one of the best gateway solutions out there in my opinion.  I am going to show you one of the many reasons why I think this product is best of breed and that is the Bandwidth limiter. PFSense uses Free BSD as it base, it has included the DummyNet software project which allows you to  simulate/enforce queues and bandwidth limitations, delays, packet losses, and multipath effects, it also implements a variant of Weighted Fair Queueing called WF2Q+. Now that may sound like a lot of reading to get up to speed with but I assure you it is quite easy to set up and maintain, let me show you how.

 

First off I will take a bandwidth test  to see what I get as unregulated bandwidth. I have a cable service from Brighthouse that is 50Mb download and 5 Mb upload.

The image tells the tale, I am getting 50/5 as and result of my speed test using Brighthouse’s Speed test. Now let’s get started with our limiting, we will need to create a Upload and a Download limit we want to apply to each system on the LAN then create one Firewall rule to force the systems on the LAN to follow the limits.

 

Lets get started:

First we need to find our Limiter Web GUI area in PFSense.   This is located under the Firewall Tab -> Traffic Shaper, the 3rd tab is called Limiter.

Next we are going to select Create new limiter , Let’s call it LimitUPLan

  1. Click Enable
  2. Set name to LimitUpLan
  3. Set Bandwidth allowed  (1mb)
  4. Mask Source Address
  5. Give a description
  6. Save
Now lets create another Limiter, Let’s call it LimitDownLAN
  1. Click Enable
  2. Set name to LimitDownLan
  3. Set Bandwidth allowed (3Mb)
  4. Mask Source Addresses
  5. Add Description
  6. Save
Now we should have 2 Limiters available to us when we go to make the Firewall rule. We set in the limiters to mask the source address. This tells PFSense to create a unique queue for each Source address on the LAN side. If we left this set to none then we would have just 1 queue limited to 3Mb for all users on the LAN thus taking a 50Mb service and making it a 3Mb service. That is not what we want here, we want to limit each user to 3 MB each so no one person can abuse our 50 Mb service. We could have 10 users all streaming 3Mb each for a total of 30 Mb of download per second and giving us 20 Mb still available.
Now lets start limiting our LAN systems.
We need to make this rule above any allow rule that allows TCP and/or UDP. If we place it below an allow rule then that rule will take precedence over the rule we want to force our limits on. In this example I have placed rule on my LAN tab as rule #2 above the bottom rule which allows all my traffic out.
Let’s add a new rule:
  1. Action Pass
  2. Interface LAN
  3. Protocol Any
  4. Source LAN Subnet
  5. Destination Any
  6. Description
Now edit the advanced section:

Select the Advance button under the In/Out feature, from the drop down menus select the 2 queues you created (In = Uploads) and (Out = Download). Save and apply the firewall rule and re-test your bandwidth usage.

Bandwidth Now Limited

 

That’s the whole process from start to finish. At this point you should have a rate limit of 3mb per LAN user downstream and 1 MB per LAN user upstream.

 

That is part of the power built-in to PFSense and the BSD platform. Now go off and limit someone today!

 

Cubert  😎

 

 

[Solved] – Network Location in no longer available or Error 59 on Windows XP system

You may have gotten a pop up on Windows that says your network resource is no longer available after a password change or password expired notice and while using Network Neighborhood. During troubleshooting you find that others can access share, the server is pingable but you can not access any share on system and it feels like XP thinks the server is not there.

I have found that this was caused by Windows Credentials being stored in the system and that the credentials  were not correct.    This issue happens in both Windows 7 and XP.

In order to fix something like this you will need to go into the Windows Credential Manager and remove the stored password.

 

To do this:

 

Windows 7: Control Panel – Windows Credential Manager

Windows XP – User Accounts – Username – Stored Network Passwords (Top left link)

 

 

Once removed retry to access share, you should now see share respond and pop up a user password windows. Place in the correct credentials and you’re in.

 

 

I hope this helps someone out there..

 

Cubert  😎

Windows 2003 – Event ID 333 – An I/O operation initiated by the registry failed unrecoverable, The registry could not read in, write out, or flush, one of the files that contain the system’s image of the registry

Event ID 333 description is An I/O operation initiated by the Registry failed unrecoverable. The Registry could not read in, write out, or flush, one of the files that contain the system’s image of the Registry.

 This means that the image of the registry held in memory could not be written to disk. Windows uses what is called the lazy writer to periodically write modified pages of memory to disk. When the lazy writer fails, an event ID 333 is recorded in the System event log.

The symptoms that might accompany the event ID 333 error include:

  • Server hangs: Your server may completely stop responding to keyboard or mouse movements and appears completely locked up, requiring a hard reboot.
  • Server sluggishness: The server is extremely slow to respond at the console, and processing information is significantly delayed.
  • Delayed Terminal Services connections: Users trying to log on to a terminal server could experience slow or delayed logons. Once they log on, they may be able to work without a slow experience; however, the logon takes several minutes instead of a few seconds.

Generally, event ID 333 can be classified into one of three categories:

  • Memory resource depletion: At the time the lazy writer attempted to write the modified pages in cache to disk, there weren’t enough resources to complete the operation. Often this type of problem is accompanied by either an event ID 2020 or 2019.
  • Disk was too busy or inaccessible: Sometimes a busy disk might not respond quickly enough to handle the lazy writer’s request to commit modified pages of memory to disk.
  • Registry bloat: The registry suddenly grows in size, which makes it increasingly difficult for the lazy writer to commit the changes to disk, commonly occurring on terminal servers.

Especially frustrating is how the events continue to flood the System event log (many times per minute) until the server is rebooted. All it takes is one time for the lazy writer to fail for the event flooding to begin. Although the condition that caused the lazy writer to fail might have been brief (such as a short spike in memory usage), event ID 333 continues to be logged even during normal memory utilization. The event is still logged because the system recognizes that a failure to sync the registry has occurred and the registry version contained in memory is out of sync with the version on disk. As a result, the number and frequency of event ID 333 messages isn’t a good indicator of the problem’s severity. By default the lazy writer tries to flush to disk every five seconds.

Event ID 333 Troubleshooting
When troubleshooting event ID 333 errors, first you must determine which general category the error falls into. It’s useful to check the System event log for other event IDs that accompany the 333 error, such as event ID 2020, which indicates a lack of paged pool memory, or event ID 2019, which indicates a leak in nonpaged pool memory.

Use these tools to assist in further diagnosing event ID 333 messages:

  • Performance Monitor: The counters to track are the system, memory, disk, and process objects.
    • Memory object: Look for a rise in nonpaged or paged memory.
    • Process object: Look for continuous rises in a process’s handles just prior to the event ID 333’s being logged.
    • System object: The %Registry Quota In Use counter can be useful in displaying how much of the allowed registry quota is being utilized. The higher the percentage, the more likely that the problem is related to a growing registry.
    • Physical disk: Look for increases in the Avg Disk Queue Length counter, which tracks the average number of read and write requests to the selected disk. If this counter spikes during the problem, start investigating the filter drivers (i.e., antivirus or backup software) on your system.
  • Poolmon.exe: This tool, which is included in the Windows Debugging Tools, is used to track kernel pool memory usage by pool-allocation tag name. Using poolmon.exe can halve your troubleshooting time by enabling you to find the tag that’s leaking memory.
  • Dureg.exe: This tool lets you view the size of the entire registry per hive. Dureg.exe is great for finding which registry hive is consuming the most space, which helps to determine what software might be causing the problem.

[Solved] – Unable to take VSS Snapshot and transfer data for ‘C:’ Unable to take VSS Snapshot of volume(s) because the VSS subsystem is in a bad state.

App Asure Replay CoreAppAssure Replay backup reports Errors, ‘Unable to take VSS Snapshot of volume(s) because the VSS subsystem is in a bad state.’ or ‘Error taking snapshot of volume(s): AddToSnapshotSet failed when trying to add volume ‘C:’ – VSS Provider is not registered’

 

I found this to be a fairly common issue when deploying new Replay agents out to Windows 2003 servers. There are several things that should be addressed when deploying Replay agents to Windows 2003 Servers including repairing LSP, refreshing and / or verifying VSS states and removing conflicting software. I have had great success resolving these types of issues by following these simple steps.

#1  Remove Offending Software Conflicts

Offending Software:

Acronis Backup & Recovery 10 Agent

Symantec Backup Exec Remote Agent for Windows

any 3rd party backup software

 

 

#2 Reset VSS Writers

I use a simple bat file that re-registeres all my VSS writers and then I follow that up with a reboot. Below is the bat script commands, copy them to a file, name the file vss.bat, then run the file. It should shutdown a few services and re-register the VSS writers.  reboot is needed after this has completed.

net stop “System Event Notification”
net stop “COM+ Event System”
net stop “Microsoft Software Shadow Copy Provider”
net stop “Volume Shadow Copy”
cd /d %windir%\system32
net stop vss
net stop swprv
regsvr32 /s ole32.dll
regsvr32 /s oleaut32.dll
regsvr32 /s Vss_ps.dll
vssvc /register
regsvr32 /s /i swprv.dll
regsvr32 /s /i eventcls.dll
regsvr32 /s es.dll
regsvr32 /s stdprov.dll
regsvr32 /s vssui.dll
regsvr32 /s msxml.dll
regsvr32 /s msxml3.dll
regsvr32 /s msxml4.dll
net start “COM+ Event System”

 

#3 Repair the Layered Service Providers

Layered Service Provider (LSP) is a feature of the Microsoft Windows Winsock 2 Service Provider Interface (SPI). A Layered Service Provider is a DLL that uses Winsock APIs to insert itself into the TCP/IP protocol stack. Once in the stack, a Layered Service Provider can intercept and modify inbound and outbound Internet traffic. It allows processing of all the TCP/IP traffic taking place between the Internet and the applications that are accessing the Internet (such as a web browser,  email client or Replay agent.).

This is a common problem in Windows 2003 especially if you use Kaseya agents on these systems. The kaseyasp.dll causes some issues with transferring the file across the network. Luckily this is a really easy fix. Download and run LSPFix.exe on the Windows 2003 Server system. It will find the Kaseya Dll issue and repair it for you.

 

#4 Verify no Disk Errors are present

This often overlooked but a simple “CHKDSK C:” at the command prompt will scan your disk and verify if there are any errors on your disk. Using the “/F” switch you can fix the errors. Make sure you have clean disks.

 

By following these steps you should resolve 98% of your common backup issues with Replay clients failing snapshots or not transferring the snapshot once it has been taken.

 

I hope this helps fellow AppAssure users out there in cybertown.

 

Enjoy

Cubert 😎

[Solved] – Always getting message “Windows can’t update important files and services while the system is using them”

 

Everytime you try to run the Windows Updater you always get the message  “Windows can’t update important files and services while the system is using them.  Save any open files and restart the computer, and try to check for new updates.“   This is caused by a Registry Key that has not been removed after a previous update.

There is a simple solution for some:

1. Open regedit.exe

2. Navigate to the Registry Key  “HKLM/Software/Microsoft/Windows/CurrentVersion/WindowsUpdate/Auto Update

3. Look for Registry Key “RebootRequired”.

4. Delete the Registry Key. Do not look for a value, there will be a KEY(folder) called “RebootRequired”

5. Reboot the Machine .

6. Run Windows update again.

If this fails to resolve issues you can try the following:

Empty your temporary Internet Files Folder
Open Run Command (Windows key + R)
type: cd %temp%
Empty the contents of that folder (del *.*)
Restart your computer

You can also give this a try:
1. Click Start, type msconfig in the Start Search box, and then press ENTER.
User Account Control permission
If you are prompted for an administrator password or for a confirmation, type the password, or click Continue.
2. On the General tab, click Selective Startup.
3. Under Selective Startup, click to clear the Load Startup Items check box.
4. Click the Services tab, click to select the Hide All Microsoft Services check box, and then click Disable All.
5. Click OK.
6. When you are prompted, click Restart.
7. After the computer starts, check whether the problem is resolved.

This should solve your issue and allow you to Windows Update to install the next round of patches.

Good Luck!

 

Cubert

😎

New website development – www.hogtrek.com – Where to go when you want to ride!

The place to go when you want to ride motorcycles

 

 

 

 

Squidworks.net starts development on a National Harley Davidson Riders Community.

Whether it’s a joyride or a long haul, cruising the U.S Highways on a Hog is an activity that only a few get to do in their lifetime. The friends and family you make on the trails are like no other.  Hogtrek.com brings groups of biker together to promote the brotherhood that is created when you bring two bikes together.

 Hogtrek.com is a free membership based web community that caters to the Harley Davidson Motorcycle Rider. The website provides access to a large database of Harley dealerships, motorcycle riding clubs and parts and paints from across the United States. The Harley Davidson owners one stop shop for fun, maintenance, performance  and biker bling. With community and group forums, motorcycle club group hosting and part and paint venders.  You have only one place to go to get all you need for the ride ahead. We sponsor motorcycle ride routes in our monthly riders choice cycle routes. See our selection of motorcycle routes and ride maps, members can upload new Google Map routes to be previewed by the community or browse the library of motorcycle routes uploaded from our membership. The next time you need a motorcycle route for you club to ride come to Hogtrek.com.

Come check out the “whats going on’s” at Hogtrek.com

 

How-to get SNMP Data from VMWare ESXi 5 with Dell OpenManage, Cacti, Nagios, Munin or Ganglia

 

So you want to access your VMware ESX 5 host over SNMP? Here is the quick steps to get Dell OpenManage and SNMP on your ESX 5 host up and running fast.  You will need the VIB’s from Dell, also will need to download and install the VMware CLI client for accessing ESX hosts from remote. For the SNMP setups below we are using a generic password inside of quotes(“password”) you should remove the quotes and place the root password of the esx host where you see the quoted password text. We also are setting up SNMP to send traps to 192.168.24.120, you will need to change this to any host you want to receive traps. You will need to replace the server IP address with the address of your ESX5 host. If you use network monitoring applications like Cacti, Nagios, Munin or Ganglia then this is just what you need to start capturing network statistics and graphs.

 

Steps  to install OpenManage.

  1. Downloaded OpenManage VIB from Dell http://www.dell.com/support/drivers/us/en/g_5/DriverDetails/DriverFileFormats?DriverId=N3R0H&FileId=2773322235
  2. Uploaded VIB to ESX host and placed in /var/log/vmware/
  3. SSH to host as root
  4. ran command esxcli software vib install -d /var/log/vmware/OM-SrvAdmin-Dell-Web-6.5.0-542907.VIB-ESX50i_A02.zip
  5. After software install, I rebooted the host.

Steps to configure SNMP

  1. Started vmWare CLI
  2. vicfg-snmp.pl –server 192.168.24.10 –username root –password “password” -c public
  3. vicfg-snmp.pl –server 192.168.24.10 –username root –password “password” -t 192.168.24.120@161/public
  4. vicfg-snmp.pl –server 192.168.24.10 –username root –password “password” -E
    Current SNMP agent settings:
    Enabled  : 1
    UDP port : 161
    Communities :
    public
    Notification targets :
    192.168.24.120@161/public
    Options :
    EnvEventSource=indications

Openmanage Web Interface:

  1. Download OpenManage Server Administrator Managed Node http://www.dell.com/support/drivers/us/en/555/DriverDetails/DriverFileFormats?DriverId=R300521
  2. Navigate to https://localhost:1311, type in ESX host IP address, login and password.
  3. You should be able to view the all the good stuff like “Main System Chassis”, Software, Storage

 

I hope this helps someone out there in cyber space!

Cubert 😎

Free Digium SwitchVox ConnectWise Switchboard Widget aka (ConnectWise Callers)

From the skunk works here at Squidworks.net comes the ConnectWise Callers widget for the Digium SwitchVox PBX switchboard.  The ConnectWise Callers Widget queries your Connectwise database for information about inbound callers calling in to your SwitchVox PBX. When a phone number is picked up from the caller ID it is then used to query the contact and company tables inside Connectwise. If it finds a match then it queries for key information about the caller including what company they are associated with and any personal information kept in the contact database.

 

The widget collects:

  1. Callers full name
  2. Callers Title
  3. Company Name
  4. Nick Name
  5. Married Flag
  6. Spouse’s Name
  7. Children Flag
  8. Children Info
  9. Anniversary Date
  10. Hobbies

Of course this all relies on your database being complete and accurate, ConnectWise provides the table and the ability to add and edit this information within your company contacts area. Using this widget to query this information as fast as you can pick up the phone allows you to be heads up on your caller, making calls more personal and closing sales.  The best part is it’s free!

Download ConnectWiseCallers here –> Connectwise-Callers-switchboard

The download zip file contains a single php file and 2 images. Extract all files to the same directory inside a web server that is accessible from your SwitchVox PBX .  Your web server can be Windows or Linux but must be able to use (mssql_connect) to access Microsoft SQL servers (ConnectWise Database). If you are using Linux and need some help getting MSSQL working with Apache and PHP then see my how-to post https://squidworks.net/2011/07/how-to-setup-ubuntu-and-php-to-use-mssql-microsoft-sql-server/  and you should be up and running within minutes. You will need to edit the ConnectwiseCallers.php file between line 48 and 52. Edit the following lines to reflect your ConnectWise Database and user access to database.

//Connectwise Database access
$cwuser = “ConnectwiseUser”;             <— Your Connectwise SQL User
$cwpass = “ConnectWisePass”;             <— Your SQL Password
$cwdb = “cwwebapp_companyDB”;   <— Your Connectwise Database name
$cwserver = “cw.yourdomain.com”;  <— Your SQL server address

You will need to log into the SwitchVox Admin area and add the Custom panel. Goto the Tools menu and select Switchboard Panels, You should see the following:

Select the Custom Switchboard Panels tab from the admin and then select Create Custom Panel button.

 

  1. Panel Name = ConnectWise Callers
  2. Panel Description = Queries inbound callers for Connectwise information
  3. Panel URL = http://yourWebServerAddress/switchboard/ConnectwiseCallers.php?NAME=%CALLER_ID_NAME%&NUMBER=%CALLER_ID_NUMBER%&EXT=%EXTENSION%&LOAD=%FIRST_LOAD%
  4. Select the extensions you want to allow access to widget

 

Save Custom panel and launch your Switchboard, once the switchboard loads select your new panel from the add panel menu. You should get a ConnectWise Callers Image in you panel if it is accessible and your URL is correctly addressed. The panel should start out looking like this:

 

This is version 1 of the widget, we will be looking at expanding the widget in the next versions to allow you to edit the information so you can update you customers contacts on the fly. If you have ideas on data or features you would like to see in the next versions of the widget feel free to post your comments here and we will see if we can add them into the widget.

Enjoy

Cubert 😎

Kaseya Agent Procedure – Local Admin Audit

Cubert has created another simple auditing script for Kaseya Agent procedures. This script grabs all the users and groups in the Local Administrators Group of any Windows system and saves then to a file on the Kserver. Every time the script runs it will upload and compare the files for differences. If it finds that a user or group was added or removed it will alert the Kserver. If you have a GetFile Alarm rule setup in the Monitor Menu -> Alerts -> GetFile  to create a ticket or email you then you will receive an alert anytime the script is run and changes are seen.

 

Download and unzip the XML file and import it in to Kaseya Agent Procedures. There is no scripts or extra files needed with this guy so you only need the XML file inside the zip.

Download here -> Procedure-LocalAdminAudit

 

Enjoy

Cubert 😎