Client Management Suite

 View Only
Expand all | Collapse all

Dynamic filters for site by subnet - sql

  • 1.  Dynamic filters for site by subnet - sql

    Posted Jul 31, 2012 08:21 AM

    Ok so in NS 6 we have many collections... and I have to start thinking outside the box of NS6 and how to do this in 7.1 as we are starting to go there. Dev server is up and running for me to mess with...

     

    now in NS 6 we had code say...
    Corporate Computers

    -- Corporate subnets
    select Guid
    from vResource
    where ResourceTypeGuid in
             
    ( 
              select ResourceTypeGuid 
              from ResourceTypeHierarchy 
              where BaseResourceTypeGuid='493435f7-3b17-4c4c-b07f-c23e7ab7781f') 
              and (
                    Guid in (
                               select [_ResourceGuid]
                               from [Inv_AeX_AC_TCPIP] t0
                               where t0.[Subnet] in(
                                                           '192.168.1.1','192.168.1.2','192.168.1.3','192.168.1.4','192.168.1.5',
                                                             )
                                 )
                      )

    this works great even in 7.1.. however once you convert a filter to RAW SQL you can no longer edit it. We do get new subnets at time so I am trying to figure out the best way in 7.1 to do this.

    Side note..
    I am doing this in our DEV server so I can simply export these out and import them into our TEST then PROD servers...
    I do see that under site maintenance 2 of my sites show up as they are package servers... but not what im looking for.

    help greatly appreciated

     



  • 2.  RE: Dynamic filters for site by subnet - sql

    Posted Jul 31, 2012 08:32 AM

    i know this would take a lot of time to do and not the best approach.. but all i can think of is to create a filter folder called subnets then create a sql code based filter PER subnet.. clumsy and would be time consuming but would work and allow me to do what i need..

    then for the site filter just add those filters.... just seems redundant and over complicated for what i need..



  • 3.  RE: Dynamic filters for site by subnet - sql

    Posted Jul 31, 2012 08:45 AM

    hah i can sit here all day and have a conversation with myself...

    so searching i see sally mentions an automation policy... as seen here

    https://www-secure.symantec.com/connect/forums/altiris-71-filters-and-organizational-views

     

    so now im off to read up on Organizational views but can anyone help me here first on this..
    A computer can only belong to 1 organizational view right? if that is the case you need to really organize it up..then allow the automation policy to add computers to that view....

    then somehow use the filter to target the OV?



  • 4.  RE: Dynamic filters for site by subnet - sql

    Posted Jul 31, 2012 09:22 AM

    ok typed up a long post but my browser locked and closed on me.. maybe shouldnt have that much opened at once and kill my session.. hah..

     

    well i was able to see my code but couldnt edit.. i thought the edit button at the top was for the  name... but turns out you just simply have to click edit then you can change the sql... NICE!!!!!



  • 5.  RE: Dynamic filters for site by subnet - sql

    Posted Aug 01, 2012 09:38 AM

    Can i ask why you're looking for this?

    I was looking at something simular to help with detecting devices and locational based things.

    I got around that by creating some automation policies that move objects to organisational groups based on name and other variables so that they would automatically get policies based on where they are in the tree.

    With the way that the site servers also work we haven't found any other need to worry about where devices are now.

    Just wondering is all.

    Jim



  • 6.  RE: Dynamic filters for site by subnet - sql

    Posted Aug 01, 2012 10:55 AM

    hey no biggie.. i dont mind tellin ya why..

    I know NOTHING about organizational views... or how do do much..

    So I am trying to figure out how to QUICKLY get us to 7.1 off of 6 as 6 will be EOL.

    I work for a company that has very strict fda regulations and the sort so we cannot have regulated computers sitting on a non supported product. We use RS which is EOL soon and going to go to SSR for that..

    so biggie for us is we have 10 sites.
    We cannot do a full discovery as our work computers are on the same network as the labratory computers so we have found it easier to list out by subnets. Even that is a bit tricky.. we cannot discover on those subnets as all computers for that site is there too.. 

     

    I understand what your saying as I did read about automation policies to move computers to Org Views..
    But I am unsure how to target the computers from there.

    I am the Altiris Admin (have 1 other guy and our Boss that can help but I am the MAIN person) and the other guys that use the console are consultants and currently only use the shortcuts tab for reports. I havent been here that long so not sure what they do with the resources tab other than use it to find computers but a report would do that too...

    we do things in 6 that I would like to improve on in 7.1 like

    1. we distribute manual installs via altiris agent window... set it to manual and the techs run it. I want to use Software Portal instead

     

    i know i side tracked WAAAAAAAAAAY OFF!!!!! hah.. but wanted to give you a broader idea...

    from what i understand also is that a computer can only be in one view... 
    Well we will have a computer in say...

    Corporate Benchtops - all Benchtops (benchtops are lab computers to us)
    Corporate Benchtops Regulated - only regulated computers
    Corporate Benchtops Non Regulated - self explanatory

    with that we have many other's.. so it is nice to have the site dictated by subnet as we will setup various software distributions in a manual to different areas where computers could go exist...

    am i just making it that much more difficult?

    i do appreciate your question! 



  • 7.  RE: Dynamic filters for site by subnet - sql

    Posted Aug 01, 2012 07:24 PM

    I use the following SQL query that I run as an automation policy. This checks an Organization Group bu GUID (right click properties on the OG to find it's GUID) and if it isn't in that OG then it check the IP subnet. 

    The automation policy moves the results of this query in the OG.

     

     

    SELECT R.[Guid] as "_ItemGuid"
    
    
    FROM (
    
    
    SELECT [Guid]
    
          ,[Name]
    
          ,[IP Address]
    
          ,SUBSTRING([IP Address],8,3)as SubNet
    
      FROM [Symantec_CMDB].[dbo].[vComputer] C
    
    
     WHERE Guid NOT IN
    
    (
    
    SELECT
    
    v1.Guid
    
    FROM
    
    vComputer v1
    
    JOIN ScopeMembership ON v1.Guid=ScopeMembership.ResourceGuid
    
    JOIN ScopeCollection ON ScopeMembership.ScopeCollectionGuid=ScopeCollection.ScopeCollectionGuid
    
    JOIN Item ON ScopeMembership.ScopeCollectionGuid=Item.SecurityGuid
    
    --Return results only if in the Organizational View
    
    WHERE ScopeCollection.ScopeCollectionGuid = 'fdseeee0-2qaa-4435-a4c3-cef61sdf4947' --insert OG GUID here
    
    )
    
    AND IsManaged = 1
    
    
    ) as R
    
    
    WHERE
    
    R.[SubNet] = '232' OR
    
    R.[SubNet] = '233'   
     


  • 8.  RE: Dynamic filters for site by subnet - sql

    Posted Aug 02, 2012 04:31 AM

    Organisational Views are basically no different to containers in something like AD. Apply something to the top folder and all subfolders get whatever you have applied. Apply something to the deepest subfolder and then only stuff in that specific folder gets what you apply to that folder.

    We use this for our software deployment policies. Our Organisational group is setup in a way so we can affect only specific parts of our business as we are spread over 22 sites with different departments over multiple sites and roaming users across all E.g. our departments like marketing and technical can be on several sites but we apply policies usually at department level but we have some special policies which are site specific by department so we set it up as below:

    Company - TOP Organisational Group
    -> Workstations
    ---> Marketing
    ----->Marketing-Site1
    ----->Marketing-Site2
    ----->Marketing-Site3
    ---> Technical
    ----->Technical-Site1
    ----->Technical-Site3
    -> Laptops
    --->Sales
    ----->Sales-Site2
    ----->Sales-Site4

    We can then use an automation policy that adds the machines to each container and they automatically get the polcies or specific stuff we apply on those containers instead of using filters and applying them that way. In policies its easy for us as we can just apply it to groups in the filters you create there.

    It doesn't matter for us at that point where the users are because the site servers will pick them up on whatever subnet they are on and apply the policies that effect that specific device set based on the filters we create in the policy or Jobs / Tasks. If i apply a policy at Laptops it will effect all machines in all those below subfolders regardless of department and site.  

    Personally from what you are describing i would seperate the devices out like ours in respect of Laptops Workstations but your top level folders would be Labaratory, work and whatever else needs to be segregated off. That way you can have policies that ensure if they are in the labaratory OU they will be forced to compliance.

    if i wanted to apply something to a specific OU adhoc by a job or task i would literally just go to the job / task and press new schedule > i would drop down the Add box and select Target > In the Add target Window click Add Rule > Then select 'Exclude resources not in' from the drop down > Select Group from the next drop down > Then select the OU you want to apply this to.

    In respect of reporting i'm pretty sure you can do reporting on OU though i havent tried personally due to our environment we report a environment level and then filter from there usually.

    I'm currently using the software portal to distribute software we would define as specials and using AD Groups and specific user permissions to allow the request of software. We haven't found any issues with it so far. just ensure the AD Sync is setup and it should start working fine. I dont personally like having to manually get involved after having to do it for too many years without a fully automated managemnet solution its lovely to just tell the user to click on the shortcut and click on the software you want.

    You have to find what

    As above you would have two folders under a top folder so that if you apply a policy to the top level it applys to everything in the sub folders
    >Corporate Benchtops - TOP Folder
    --->Corporate Benchtops Regulated - Subfolder of Corp Benchtops
    --->Corporate Benchtops Non Regulated - Subfolder of Corp Benchtops

    Though i dont use automation policies to migrate my desktops due to the way my desktop team work we do use it for our servers

    Heres what i use:

    SELECT
       [vri1_Computer].[Guid] AS [_ResourceGuid]
    FROM
       [vRM_Computer_Item] AS [vri1_Computer]
          INNER JOIN [Inv_AeX_AC_Identification] AS [dca2_AeX AC Identification]
             ON ([vri1_Computer].[Guid] = [dca2_AeX AC Identification].[_ResourceGuid])
          INNER JOIN [vComputerResource] AS [ajs3_vComputerResource]
             ON ([vri1_Computer].[Guid] = [ajs3_vComputerResource].[Guid])
          LEFT OUTER JOIN [Inv_AeX_AC_TCPIP] AS [dca3_AeX AC TCPIP]
             ON ([vri1_Computer].[Guid] = [dca3_AeX AC TCPIP].[_ResourceGuid])
    
    WHERE
       (
          (
             ([ajs3_vComputerResource].[IsManaged] = 1)
             AND
             ([dca2_AeX AC Identification].[OS Name] LIKE N'%Windows Server%')
             AND
             ([dca2_AeX AC Identification].[OS Name] LIKE N'%20%')
             AND
             ([dca3_AeX AC TCPIP].[IP Address] LIKE N'10.90.%')
          )
       )

    then i run an assign server to organisational group task to move those that fall in to that filter and we have a few of these for our datacenters / servers.

    I know people who still are working purely on filters because of the way they worked on 6 but i was fresh with 7 and this seems to work across the board for us.

    I hope this helps, if you have any questions feel free to give me a shout.

     



  • 9.  RE: Dynamic filters for site by subnet - sql

    Posted Aug 03, 2012 09:56 AM

    ^^ WOW a lot to process.. Thanks!

    I am forced to use our current methods... which I do not want to.. but then can move forward checking out your methods..

    I had a meeting with the rest of the team... since I am now the core guy and in charge.. I still do not have my say in this.. they tell me to take charge but then when I do it is shot down..

     

    example..
    We currently have a ton of software collections that need to be created.. I said why dont we just use the software portal.. they said Ok set that up but also create all these collections... GRRRRRRRRRRR
     



  • 10.  RE: Dynamic filters for site by subnet - sql

    Posted Aug 03, 2012 10:21 AM

    Instead of creating TONNES of collections just create a few precise policies.

    The easiest way i found was to create a policy that i can apply to all my machines across all the companies we support this is our base policy. It contains pretty much every core framework (.net1.1 - 4.0, Java, Flash, Adobe Reader, Visual C++, MSXML, Image Updates e.g. os or appliance etc) and that covers all of our Organisational Groups.

    Then you dont have to worry about creating lots of collections,

    I went through our software catalogue and then by each noted which companies / departments it affected.

    Once i had gone though i started to build up lists from that Like So:

    CORE APPS - Everything and affects all computers regardless of company
    Company based apps and installs - a policy for each Company i deal with that contains applications that affect all devices in that company.

    Once those are done whatever is left in the list gets put in to policies based on device type or department depending on the requirement. e.g.

    Laptops have DVD software and VPN / SSL or custom WIFI settings etc which desktops wouldnt get.
    Sales would get a sales tools
    Warehouse would get warehouse tools.


    Once you have those apply them to the appropriate structures instead of working for collections for each bit of software. it doesnt make sense. let the application do the work for you, the applicability rules will take care of the installs sides...

    Fortunately where i am i do get the final say in alot of these things as i built ours from the ground up. its deifnitely not perfect but its working around bugs and problems to get it to do what i need with the least manual intervention.

     



  • 11.  RE: Dynamic filters for site by subnet - sql

    Posted Aug 03, 2012 10:41 AM

    Another thing, start building it as you go. We migrated our software piece by piece and started building the policies as i went based on the software lists i wrote up.

    Have a good read, sorry if i flooded you with info. I would definitely see if you can get your point across first... or do it and go see it works...

     

    something you might find amusing: http://dilbert.com/strips/comic/2012-02-11/

    to be fair though you could still build your filters for the policies and just apply them if in this and this and this then they get the policy, just seems a long way round to get the same as what the applicability rule and detection rules do.



  • 12.  RE: Dynamic filters for site by subnet - sql

    Posted Aug 07, 2012 11:53 AM

    my point hasnt been taken well..

     

    even had a meeting today... we went through various collections.. i was told we need these.... and we went through them.. whistle I dont agree....

    but if you could just blink and have our flooded NS6 have the NS7.1 support we would do that..

    if NS6 wasnt going EOL we wouldnt be upgrading.. Im told everything has to be ready by day 1 when we start migrating sites in... minimal should be needed to be done... we have to have all collections (i know filters now) setup and pointing to things as NS6 does A LOT of things behind the scenes and we cant miss a beat.... im gonna need another vacation after this one.. hah...

    i do appreciate all your input.. and once we go full 7.1 by end of year.. next year I will be looking at ways to cleanup our new install!!!!!!

     



  • 13.  RE: Dynamic filters for site by subnet - sql

    Posted Aug 07, 2012 03:30 PM

    I definitely feel for you, sounds like a rough couple of meetings. I've been quite lucky with my implementation i've been able to transition from DS as i wanted not with the full move. i would love to say something useful like try the migration tool but that would most likely do more harm that good.

    Definitely going to need a holiday after that one.

    Good luck!