Kaseya Community

General Purpose SQL Queries - Post them here!

  • Hi guys, i am VERY keen to hear what you think about this guide i wrote

    http://www.scribd.com/doc/58509159

    Download it as you please, make suggestions if you will, i have had a few people test the steps and they have found their way to making a custom excel dashboard.

    Let me know how you go



    [edited by: markdavidboyd at 5:28 PM (GMT -7) on 6-22-2011] removed link and readded a new link
  • OK, i am new to kaseya and i am also creating custom dashboards i used the code you gave in power Gadgets and get no data available. am i suppose to adjust the code you supplied or is it plug and play?

  • Hi markdavidboyd,

    Excellent document and it will definately assist a great deal!!! Any idea why I can't connect to my SQL 2008 R2 Kaseya db from my laptop running windows 7 and excel 2007??? I'm getting the following error: "SQL Server does not exist or access denied"

  • This is good stuff.
    If you get the hang of the excel method its only a smallish leap to build it has a webpage using Visual Studio.  The principals are pretty much the same.  You can then have it running as a webpage that anyone can log into from anywhere.  We have ours on a 50" panel on the wall that runs all the time.  it refreshes with live data every 30secs. 

    here is an example of what we are currently running.  It also gets data from ConnectWise.  But you get the idea.
    Why we have to do this stuff ourselves to get data from Kaseya is beyond me.

  • Hi Thinus,

    You have to be inside your network, that's a challenge we had, our vsa is in a data center, had to route between our office and the data center.
    I am using Excel 2010, it should be pretty much the same (I only just recently started using Excel 2010.

    What account are you using to get into you SQL box?
    Make sure you have exposed the views publically from Kaseya.
    Go to your VSA > System > Database Access > Database views > set a password > try again

    That should be all you need. Failing that, Log into your Kserver (exercise caution) and set the rights to the DB for the Kaseyaviews account. 

    It might also be worth nothing that by default, Kaseyaviews doesn't have all the rights to all the tables - you might need to elevate privleges anyway. 

    Consult Kaseya before doing this. I have a fair bit of DB experience and i still don't go in and make changes without consulting the vendor first.

  • And for anyone wondering, that link i posted to my article doesn't work anymore, you can find anything and everything i write at http://simpleit.tumblr.com/ follow me, its new, but in the coming days i will have heaps of stuff up ! :)

  • HI Mark,

    I'm sitting with the exact same issue, server hosted at a datacenter. Your methods works 100% on my test server which is on the LAN.

    I tried using both sa SQL account as well as tried using the Windows Authentication account. Any guidance as to how i'll get it to work being at the datacentre?

    Thanx for all your help, much appreciated.

  • Do you own the firewall at both ends? You need have point to point VPN tunnel open constantly between the data center and your premise. I'd chuck some SSL and protect the traffic with IPSec, you are going to have to get to know your firewalls. You will know you can get to the VSA in your datacenter when you and ping it by its internal DNS name. You might need to add a record to dns too. I will have a look at work tommorrow

    What firewall make and model do you have?

  • I am looking for a query to show the open alarms from the Alarmsummary in Dashboard?

    Thanks,

    Gijs

  • @gvanrijn

    Not sure what you are after really, but is this a starting point for you to start from.

    SELECT TOP 1000 [Machine_GroupID]

         ,[Message]

         ,[AlarmSubject]

         ,[EventTime]

         ,[TicketID]

     FROM [ksubscribers].[dbo].[vMonitorAlarmAlert]

     Order by EventTime desc

  • Select vAL.Machine_GroupID as Machine, applicationName as app

    from vAddRemoveList vAddRem

    JOIN vAgentLabel vAL on vAddRem.agentGuid = vAL.agentGuid

    Where applicationName like 'limewire%'  

    or applicationName like 'frostwire%'  

    or applicationName like 'bearshare%'  

    or applicationName like 'µTorrent%'  

    or applicationName like 'Shareaza%'  

    or applicationName like 'azureus%'  

    or applicationName like 'vuze%'  

    or applicationName like 'turbobt%'  

    or applicationName like 'bitcomet%'  

    or applicationName like 'imesh%'  

    or applicationName like 'Soulseek%'

    and

    (groupName not like 'home.%' and

    groupName not like ' ********** ' and

    groupName not like ' ********** ' and

    groupName not like ' ********** ' )

    and vAL.online < 5

    Detects P2P Sharing apps. Easy to add more application names. Easy to add more exceptions. Group name exceptions are in brackets, so they run as one.

  • @jdvuyk

    Thx for your quick reply!! Your answer is a good start for me.

    To be more specific below i quote what Tinus mentioned on June 22nd. That is what i am looking for.

    Thinus

    Does anyone have the a SQL Script which would return the Standard Dashboard Group Alarm Status results?

    To say it in normal terms: i am looking for an SQL-statement which brings up al the Red dots from the Group Alarm Status. I have attached an image which makes it clear i hope..

    Thanks for you help!!

    Gijs

  • Any way we could click a button or generate a report to show by client who has security licensing that expires in a given month and how many licenses are expiring per client that month?  Does anybody out there have the code written or know how to do this?  We want to put everybody's license on auto-extend but need to know how many clients and how many of their machines will be auto-extending in a given month so we ensure we have enough licenses in the pool.  Trying to look at each client individually is very labor intensive and if we did that, by the time we were done with our client list, machines may have been added or removed since we started making our numbers inaccurate.  We would basically like to be able to easily query Kaseya, 'look at all our clients and tell me which of our clients have security licenses expiring in month 'x' and how many or their licenses are expiring in month 'x'?'  Thanks in advance...

    If anybody understands what I'm trying to do here but has another method to get the same/similar info, I'm all ears!     I'm assuming there are folks out there that have all of their clients licenses on auto-extend, so if you are one of them, please tell me how you are tracking your numbers.  Thanks!

  • HI jdvuyk!

    Your dashboard looks like a dashboard i'll see here! ;-)

    I've tested now some hours with Exchel, but it is not much flexible to design and use the space on screen perfect. Can you paste a code-snipplet for us?

    Greetings, Kai

  • was this made public or is this just an example?