Kaseya Community

SQL query language help needed

  • I am trying to do a custom report in Access from the raw Kaseya data, and I need a function that will extract the group from the Machine_GroupID field. For example:

    function(machine1.group1)= group1

    function(machine2.group1) = group1

    function(m1.longgroupname1) = longgroupname1

    Because there's no fixed length to the machine or group name, I can't just trim the first or last n characters from the string. I'm sure there's an easy way to say "give me everything to the right of the period," but after searching through Access help I've come up empty so far.

    Anyone know how to do this?


    Legacy Forum Name: SQL query language help needed,
    Legacy Posted By Username: David_Schrag
  • Are you using VBA? Try InStr(string,character) to find the index of the dot. Then Mid(string,start,length) to get what you need. Let me know if that helps.

    Legacy Forum Name: Server,
    Legacy Posted By Username: seangoss
  • Ah, yes, it was the Mid function I was missing. The function is ugly but it seems to work:

    Mid([Machine_GroupID],(InStr([Machine_GroupID],"."))+1)

    Thanks!

    P.S. As I noted in the Reports forum, a workaround is to use a join to the vMachine table, but since joins can mess up some queries this is good to have.


    Legacy Forum Name: Server,
    Legacy Posted By Username: David_Schrag
  • For some reason, when this post got e-mailed to me, a bunch of stuff within brackets got stripped from the formula. Trust me, it makes sense when you see it on the forum itself.

    Legacy Forum Name: Server,
    Legacy Posted By Username: David_Schrag
  • Corollary: To extract the MachineID from the Machine_GroupID, use the Left function in place of the Mid function, and use -1 instead of +1 to get rid of the dot.

    Legacy Forum Name: Server,
    Legacy Posted By Username: David_Schrag