which country user step here?

Tag Cloud

MOSS (47) SharePoint 2007 (37) SharePoint 2013 (23) SharePoint 2010 (22) MOSS admin (17) PowerShell (17) admin (17) developer (16) List (15) WSS (14) sql query (14) MOSS SP2 (13) end user (11) scripting (11) wss V3 (11) Moss issue (8) search (8) permission (7) sql (7) Service Pack (6) database (6) reportadmin (6) workflow (6) Excel (5) client object model (5) CU (4) Client Code (4) Command (4) Cumulative Updates (4) Patch (4) RBS (4) SharePoint designer (4) stsadm (4) ASP.NET (3) Content Database (3) Groove (3) Host Named Site Collections (HNSC) (3) IIS (3) Tutorial (3) alert (3) batch file (3) codeplex (3) error (3) incomming email (3) restore (3) upload (3) Caching (2) Folder (2) Index (2) Internet (2) News (2) People Picker (2) Share Document (2) View (2) Web Development with ASP.NET (2) add user (2) authentication (2) coding (2) column (2) deploy solution (2) domain (2) download (2) enumsites (2) exam (2) export (2) issue (2) june CU (2) mySites (2) network (2) office 365 (2) orphan site (2) performance (2) profile (2) project server (2) query (2) server admin (2) theme (2) timer job (2) training (2) user porfile (2) web master (2) web.config (2) wsp (2) 70-346 (1) 70-630 (1) AAM (1) Anonymous (1) Approval (1) Cerificate (1) Consultants (1) Content Deployment (1) Content Type (1) DOS (1) Document Library (1) Drive Sapce (1) Excel Services (1) Export to Excel (1) Feature (1) GAC (1) Get-SPContentDatabase (1) Get-WmiObject (1) HTML calculated column (1) ISA2006 (1) IT Knowledge (1) ITIL (1) Install (1) Link (1) MCTS (1) Macro (1) Migration (1) My Site Cleanup Job (1) My Sites (1) NLBS (1) Nintex (1) Office (1) Open with Explorer (1) ROIScan.vbs (1) Reporting Services (1) SPDisposeCheck.exe (1) SQL Instance name (1) SSRS (1) SharePoint admin (1) SharePoint farm (1) Shared Services Administration (1) Site Collection Owner (1) Site template (1) Steelhead (1) URLSCAN (1) VLOOKUP (1) WSS SP2 (1) XCOPY (1) admi (1) app (1) application pool (1) aspx (1) audit (1) availabilty (1) backup (1) binding (1) blob (1) branding sharepoint (1) cache (1) calendar (1) connection (1) copy file (1) counter (1) crawl (1) custom list (1) event (1) excel 2013 (1) facebook (1) filter (1) fun (1) group (1) iis log (1) import (1) import list (1) improment (1) interview (1) keberos (1) load balance (1) log in (1) metada (1) migrate (1) mossrap (1) onedrive for business (1) operation (1) process (1) publishing feature (1) resource (1) security (1) send email (1) size (1) sps2003 (1) sql201 (1) sql2012 (1) sub sites (1) system (1) table (1) task list (1) today date (1) vbs (1) video (1) web part (1) widget (1) windows 2008 (1) windows 2012 R2 (1) windows Azura (1) windows account (1) windows2012 (1) wmi (1)

Friday, August 27, 2010

Basic sharing on Code Access Security (CAS)

Controlling access to system resources ( Dir structures and file) 

WSS have 2 security approaches :
  • user identity-based
  • code identity-based [digital certificate or URL or site]
WSS is use .NET Framework , which is using code identity-based approach so call CAS


Levels of trust
  • Full > unmanaged code
  • High > cannot call unmanaged code and enterprise service but can send email using SMTP servers , also can access sockets and file system
  • Medium >  SMTP and SQL but cannot access sockets and registry
  • Low > read only application virtual dir and no network connectivity
  • Minimal > have execute permission only
WSS have 2 policy ( Virtual server extedned to wss this policy will applied to)
  • wss_minimaltrust.config
  • wss_mediumtrust.config
WSS security setting for CAS:
  • machine.config > c:\windows\micorsoft.net\framwork\V2.0.50727\config
  • web.config

Web.config located

  • top level conetn root > C:\Inetpub\wwwroot > configuration for web server
  • web part resoucces of the global assemy cache
  • CONFIG ….\web server extensions\60\config\ > define for other extending vs
  • ISAPI ….for the /_vti_bin
  • LAYOUTS > for /_layouts
  • ADMIN > SharePoint CA

Thursday, August 26, 2010

How to change the Sharepoint theme template option

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS\1033

 

have one file name SPTHEMES.XML , this is the file control the theme for SharePoint. if you want to add some custom theme make sure here is added

 

1033 is for English version , 1031 is German…if you have more so different language keep at different folder .

Monday, August 23, 2010

Utility to quickly reapply themes to sites collection

After MOSS SP2 Deployment have faced some problem on the theme, so we need to edit the CSS format then to make it take effect on all sites.

now the problem come in …we have so many sites and subsites how going to get it done? as you know we need to reapply the theme then the CSS only take effect.

Luckily have some good utility tool at coplex , is so call retheme.exe.

this help a lot, you can download it from CodePlex.

Below is the coding from the Codeplex reTheme :)


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;

namespace reTheme
{
    class Program
    {
        static void Main(string[] args)
        {
            string siteUrl;
            if (args.Length == 0)
            {
                Console.Write("Site Collection URL:");
                siteUrl = Console.ReadLine();
            }
            else
            {
                siteUrl = args[0];
            }

            try
            {
                using (SPSite site = new SPSite(siteUrl))
                {
                    foreach (SPWeb web in site.AllWebs)
                    {
                        Console.Write(web.ServerRelativeUrl + ": ");
                        try
                        {

                            string themeName = web.Theme;
                            web.ApplyTheme("");
                            web.ApplyTheme(themeName);
                            Console.WriteLine("OK");
                        }
                        catch (Exception e)
                        {
                            Console.WriteLine(e.Message);
                        }
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
        }
    }
}

Tuesday, August 10, 2010

Why There is a Shortage of SharePoint Experts

Copied from here : SharePoint expert are we on the right path ? :D

 

This post started out as a short reply to a discussion on LinkedIn about this post: Why there is a shortage of SharePoint Experts. Once I hit the fourth paragraph I decided it made a better blog post then a comment. :)

There is a shortage of SharePoint experts because the learning curve for the base technologies is large and the learning curve for the breadth of SharePoint functionality is even larger.

Consider SharePoint 2010 Server Enterprise edition. You can deploy the platform as a public facing site with heavy customization and branding, as a team collaboration portal, as a business intelligence and information delivery, as a stand-alone or connected enterprise content management system, as a search appliance, as a business process automation (workflow) platform, and as a basis for custom applications. There are even more uses, but hopefully you get the basic idea – you can use it for lots of unrelated things.

To call yourself an expert, you should understand most or all of these core uses and the related tools used by end users, developers, and administrators. The SharePoint learning curve is honestly measured in years - assuming that you have a few years' experience building either Windows applications or building and administering complex server systems.

Fortunately, most organizations do not need a full-time SharePoint expert to be successful. Most deployments involve a small subset of the functionality. Most people who work in a corporate setting will use a subset of the tools and fulfill a role in a larger team. If you are looking to implement SharePoint, my advice is to spend the money on a good consultant(s) who is an actual SharePoint expert to create a good foundation and focus on hiring or training full time staff to participate in the build-out and maintain the completed system. Once you are up and running, say goodbye for now to the experts and use them on an ongoing basis only as needed.

The multi-year learning curve plus the sharp upward demand curve means that the supply of experts will be well short of demand for several more years. The supply of people who have serious SharePoint experience will obviously increase along with adoption, but there is no reason you should expect someone who worked in a large heavily controlled collaboration environment where the focus was to maximize stability and minimize risk through aggressive governance to know much about building high-quality public facing sites with significant branding and customization (or vice-versa). That's not to say the person could not make the switch, but it is to say that person is not an expert.

For those organizations looking to find a "SharePoint expert" on the cheap - don't hold your breath. If you are offering a rate that is comparable to that earned by a qualified Web developer in your area, ask yourself why someone who is already a qualified to do work that pays the Web developer rate as a Web developer is willing to provide the additional value of their additional 3+ years of learning in the SharePoint domain at no additional cost to work on SharePoint. Instead try to hire to the narrower skill set you actually need.

The pool of people who have solid experience in one of WCM, ECM, BPA, BI, information architecture, system administration, etc. who can be very productive in a SharePoint environment with the right training and mentoring is large. Build your team from this pool. Trying to build a team of SharePoint experts is much harder and more expensive. More importantly, it's probably an unnecessary and poor strategy.

Posted at 11:30 PM by Doug WareNo presence information | Category: Random Whatnot | Permalink | Email this Post | Comments (3)

Comments
great post

I started working with SharePoint back in 2004, was a member of the hosted SharePoint team at Microsoft (now called BPOS-Dedicated), and now work for a SharePoint ISV (echoTechnology, recently acquired by Axceler), and actively write and speak on SharePoitn solutions -- and yet I still feel like a newbie. I know several MVPs and MCMs who are "the" experts on the SharePoint platform who find themselves constantly learning as the platform growns and develops, and especially as partners and customers use it to create new solutions.
But here's my point for responding: If you're looking for SharePoint expertise, you really need to focus on the business problem you're trying to solve rather than find a generalist. Someone with deep vertical experience and some basic SharePoint training is probably a better bet than someone with years of SharePoint but new to your business.

@buckleyplanet at 8/6/2010 1:59 AM

Nice response

The idea that the competency takes years to achieve is an interesting angle I did not consider - I saw it as a skill set developers are not interested in.

Nadir Kamdar at 8/6/2010 11:39 AM

Lack of Investment

As a certified Master, I guess I am one of the *experts*, and I can attest to the enormous learning curve on SharePoint including not only the actual technical skills and APIs, but also the problem domains related to the solutions that SharePoint provides a platform for: Business Intelligence, Knowledge Management, Social Computing, Business Process Automation, etc. These all include significant learning burdens beyond just the technology.
My observations on the lack of SharePoint developers more firmly points a finger a broad problem within the IT industry: Organizations are no longer investing in employees who work in IT favoring to advocate hiring specialists that already have the skills to solve the problem at hand.
- the learning curve is steep and takes a lot of time
- organizations don't yet recognize the learning curve is steep
- organizations don't want to provide the required training
- organizations don't want to pay more for those skills
- organizations don't provide an environment where learning on the job is really tolerated anymore (how can you accurately estimate how long something will take or cost if you have never done it before)
There just isn't a lot of incentive for developers to absorb the learning costs for very little return. IT in general is already suffering from long hours, understaffed projects, and a new technology release cycle that makes it challenging just to keep your skills current, let alone learn significant chunks of new skills. I have seen very few IT organizations in recent years where the IT staff were not *just barely hanging on*.
To take advantage of the SharePoint platform and the considerable features it can provide for a Business Productivity platform, they are simply going to have to change their IT resource management strategies to make appropriate investments in skills development. Until they do, I see the shortage of qualified SharePoint Developers being in short supply for many years to come.

Chris Beckett / SharePoint Bits at 8/6/2010 8:30 PM

The faster way get the enumsites report in excel

Just notice have the easy way for you to get the nice report with the stsadm enumsites command. you can see the top level sites , owner, content db report between one minute!!! cool !!

stsadm -o enumsites -url http://abc.com > sitelist.xml
Then just opened up the xml file with excel2007. (really beginning to like the features in this version)

It warned that there was not an associated schema, would I like to create on from the file, ‘yes Please’!

And now we can sort, filter, sum, etc.

Wednesday, August 4, 2010

Great SharePoint Tutorials site to share

here have some freebies to share from internet, if you like to have some training tutorials for your SharePoint user here is have good one.

 

Video Tutorials > easy to learn :D enjoy ya

 

http://www.fpweb.net/sharepoint-tutorials/

  • Templates & Navigation
  • Create a SharePoint Group
  • Delete a Group
  • Create a Site Template
  • Create a List Template
  • Change Title Description & Logo
  • Change Quicklist Order
  • Add Quicklist Link
  • Top Link Bar - Change Order
  • Top Link Bar - Delete Item
  • Top Link Bar - Add Item
  • Enable Tree View
  • Explorer View
  • Log out
  • Site & User Administration
  • Add a new User
  • Reset User Password
  • Create a Site Collection
  • Delete a Site Collection
  • SharePoint Designer - Backup Site
  • Check Site Usage
  • Regional Settings
  • Set up Anonymous Access
  • Permissions & Alerts
  • Set Document Permissions
  • Library Permissions
  • List Permissions
  • Create Email Alerts
  • Delete an Email Alert
  • Documents & Tasks
  • Assign Tasks
  • Deassign Tasks
  • Restore Deleted Item
  • Web Parts
  • Add Web Parts
  • Create a Web Part Page
  • Content Editor Web Part

    • SQL query to find "DayLastAccessed" in portal's Site DB

      is time to do clean up for the SharePoint environment , have 2 option i know we can do that. one is manual one is auto :P ha ha ha

       

      Auto one you can setup at CA, Site use confirmation and deletion  /_admin/applications.aspx . not sure how correct is it!! ha ha

       

      but i prefer manual one.so use below query to check day last accessed , so we can guess is still used or not.

       

      SELECT     FullUrl AS 'Site URL', TimeCreated, DATEADD(d,
      DayLastAccessed + 65536, CONVERT(datetime, '1/1/1899', 101)) AS
      lastAccessDate
      FROM Webs
      WHERE (DayLastAccessed <> 0) AND (FullUrl LIKE N'sites/%')
      ORDER BY lastAccessDate

      Useful SQL Queries to Analyze and Monitor SharePoint Portal Solutions Usage

       

      keep track on what i can get from internet about SQL query for SharePoint :

       

      i am looking for last access to sites query, but unable to find..but found some other interesting one.. at below from this link

       

       

      A List of SQL Queries

      • Top 100 documents in terms of size (latest version(s) only):

        Collapse

        SELECT TOP 100 Webs.FullUrl As SiteUrl, 
        Webs.Title 'Document/List Library Title',
        DirName + '/' + LeafName AS 'Document Name',
        CAST((CAST(CAST(Size as decimal(10,2))/1024 As
        decimal(10,2))/1024) AS Decimal(10,2)) AS 'Size in MB'
        FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
        INNER JOIN Sites ON Webs.SiteId = SItes.Id
        WHERE
        Docs.Type <> 1 AND (LeafName NOT LIKE '%.stp')
        AND (LeafName NOT LIKE '%.aspx')
        AND (LeafName NOT LIKE '%.xfp')
        AND (LeafName NOT LIKE '%.dwp')
        AND (LeafName NOT LIKE '%template%')
        AND (LeafName NOT LIKE '%.inf')
        AND (LeafName NOT LIKE '%.css')
        ORDER BY 'Size in MB' DESC



      • Top 100 most versioned documents:

        Collapse



        SELECT TOP 100
        Webs.FullUrl As SiteUrl,
        Webs.Title 'Document/List Library Title',
        DirName + '/' + LeafName AS 'Document Name',
        COUNT(Docversions.version)AS 'Total Version',
        SUM(CAST((CAST(CAST(Docversions.Size as decimal(10,2))/1024 As
        decimal(10,2))/1024) AS Decimal(10,2)) ) AS 'Total Document Size (MB)',
        CAST((CAST(CAST(AVG(Docversions.Size) as decimal(10,2))/1024 As
        decimal(10,2))/1024) AS Decimal(10,2)) AS 'Avg Document Size (MB)'
        FROM Docs INNER JOIN DocVersions ON Docs.Id = DocVersions.Id
        INNER JOIN Webs On Docs.WebId = Webs.Id
        INNER JOIN Sites ON Webs.SiteId = SItes.Id
        WHERE
        Docs.Type <> 1
        AND (LeafName NOT LIKE '%.stp')
        AND (LeafName NOT LIKE '%.aspx')
        AND (LeafName NOT LIKE '%.xfp')
        AND (LeafName NOT LIKE '%.dwp')
        AND (LeafName NOT LIKE '%template%')
        AND (LeafName NOT LIKE '%.inf')
        AND (LeafName NOT LIKE '%.css')
        GROUP BY Webs.FullUrl, Webs.Title, DirName + '/' + LeafName
        ORDER BY 'Total Version' desc, 'Total Document Size (MB)' desc



      • List of unhosted pages in the SharePoint solution:

        Collapse



        select Webs.FullUrl As SiteUrl, 
        case when [dirname] = ''
        then '/'+[leafname]
        else '/'+[dirname]+'/'+[leafname]
        end as [Page Url],
        CAST((CAST(CAST(Size as decimal(10,2))/1024 As
        decimal(10,2))/1024) AS Decimal(10,2)) AS 'File Size in MB'
        FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
        where [type]=0
        and [leafname] like ('%.aspx')
        and [dirname] not like ('%_catalogs/%')
        and [dirname] not like ('%/Forms')
        and [content] is not null
        and [dirname] not like ('%Lists/%')
        and [setuppath] is not null
        order by [Page Url];



      • List of top level WSS sites and their total size, including child sites in the portal:

        Collapse



        select FullUrl As SiteUrl,  
        CAST((CAST(CAST(DiskUsed as decimal(10,2))/1024 As
        decimal(10,2))/1024) AS Decimal(10,2)) AS 'Total Size in MB'
        from sites
        Where FullUrl LIKE '%sites%' AND
        fullUrl <> 'MySite' AND fullUrl <> 'personal'



      • List of portal area and total number of users:

        Collapse



        select webs.FullUrl, Webs.Title, 
        COUNT(WebMembers.UserId) As 'Total User'
        from Webs INNER JOIN WebMembers
        ON Webs.Id = WebMembers.WebId
        Where fullurl NOT like '%sites%' AND
        fullUrl <> 'MySite' AND fullUrl <> 'personal'
        Group BY webs.FullUrl, Webs.Title
        Order By 'Total User' desc



      • List of top level and sub sites in the portal and the number of users:

        Collapse



        select  webs.FullUrl ,Webs.Title, COUNT(WebMembers.UserId) As 'Total User'
        from Webs INNER JOIN WebMembers
        ON Webs.Id = WebMembers.WebId
        where fullurl like '%sites%' AND fullUrl <> 'MySite' AND fullUrl <> 'personal'
        Group BY webs.FullUrl, Webs.Title
        Order By 'Total User' desc



      • List of all portal area:

        Collapse



        select Webs.FullUrl As [Site Url], 
        Title AS [Area Title]
        from Webs
        Where fullurl NOT like '%sites%' AND fullUrl <>
        'MySite' AND fullUrl <> 'personal'



      • List of the total portal area:

        Collapse



        select COUNT(*)from Webs 
        Where fullurl NOT like '%sites%' AND
        fullUrl <> 'MySite' AND fullUrl <> 'personal'



      • List of all top level and sub sites in the portal:

        Collapse



        select Webs.FullUrl As [Site Url], 
        Title AS [WSS Site Title]
        from webs
        where fullurl like '%sites%' AND fullUrl <>
        'MySite' AND fullUrl <> 'personal'



      • List of the total top level and sub sites in the portal:

        Collapse



        select COUNT(*) from webs
        where fullurl like '%sites%' AND fullUrl <>
        'MySite' AND fullUrl <> 'personal'



      • List of all list/document libraries and total items:

        Collapse



        select  
        case when webs.fullurl = ''
        then 'Portal Site'
        else webs.fullurl
        end as [Site Relative Url],
        webs.Title As [Site Title],
        case tp_servertemplate
        when 104 then 'Announcement'
        when 105 then 'Contacts'
        When 108 then 'Discussion Boards'
        when 101 then 'Docuemnt Library'
        when 106 then 'Events'
        when 100 then 'Generic List'
        when 1100 then 'Issue List'
        when 103 then 'Links List'
        when 109 then 'Image Library'
        when 115 then 'InfoPath Form Library'
        when 102 then 'Survey'
        when 107 then 'Task List'
        else 'Other' end as Type,
        tp_title 'Title',
        tp_description As Description,
        tp_itemcount As [Total Item]
        from lists inner join webs ON lists.tp_webid = webs.Id
        Where tp_servertemplate IN (104,105,108,101,
        106,100,1100,103,109,115,102,107,120)
        order by tp_itemcount desc


        Note: the tp_servertemplate field can have the following values:




        • 104  = Announcement


        • 105 = Contacts List


        • 108 = Discussion Boards


        • 101 = Document Library


        • 106 = Events


        • 100 = Generic List


        • 1100 = Issue List


        • 103 = Links List


        • 109 = Image Library


        • 115 = InfoPath Form Library


        • 102 = Survey List


        • 107 = Task List




      • List of document libraries and total items:

        Collapse



        select  
        case when webs.fullurl = ''
        then 'Portal Site'
        else webs.fullurl
        end as [Site Relative Url],
        webs.Title As [Site Title],
        lists.tp_title As Title,
        tp_description As Description,
        tp_itemcount As [Total Item]
        from lists inner join webs ON lists.tp_webid = webs.Id
        Where tp_servertemplate = 101
        order by tp_itemcount desc



      • List of image libraries and total items:

        Collapse



        select case when webs.fullurl = '' 
        then 'Portal Site'
        else webs.fullurl
        end as [Site Relative Url],
        webs.Title As [Site Title],
        lists.tp_title As Title,
        tp_description As Description,
        tp_itemcount As [Total Item]
        from lists inner join webs ON lists.tp_webid = webs.Id
        Where tp_servertemplate = 109 -- Image Library

        order by tp_itemcount desc



      • List of announcement list and total items:

        Collapse



        select case when webs.fullurl = '' 
        then 'Portal Site'
        else webs.fullurl
        end as [Site Relative Url],
        webs.Title As [Site Title],
        lists.tp_title As Title,
        tp_description As Description,
        tp_itemcount As [Total Item]
        from lists inner join webs ON lists.tp_webid = webs.Id
        Where tp_servertemplate = 104 -- Announcement List

        order by tp_itemcount desc



      • List of contact list and total items:

        Collapse



        select case when webs.fullurl = '' 
        then 'Portal Site'
        else webs.fullurl
        end as [Site Relative Url],
        webs.Title As [Site Title],
        lists.tp_title As Title,
        tp_description As Description,
        tp_itemcount As [Total Item]
        from lists inner join webs ON lists.tp_webid = webs.Id
        Where tp_servertemplate = 105 -- Contact List

        order by tp_itemcount desc



      • List of event list and total items:

        Collapse



        select case when webs.fullurl = '' 
        then 'Portal Site'
        else webs.fullurl
        end as [Site Relative Url],
        webs.Title As [Site Title],
        lists.tp_title As Title,
        tp_description As Description,
        tp_itemcount As [Total Item]
        from lists inner join webs ON lists.tp_webid = webs.Id
        Where tp_servertemplate = 106 -- Event List

        order by tp_itemcount desc



      • List of all tasks and total items:

        Collapse



        select  
        case when webs.fullurl = ''
        then 'Portal Site'
        else webs.fullurl
        end as [Site Relative Url],
        webs.Title As [Site Title],
        lists.tp_title As Title,
        tp_description As Description,
        tp_itemcount As [Total Item]
        from lists inner join webs ON lists.tp_webid = webs.Id
        Where tp_servertemplate = 107 -- Task List

        order by tp_itemcount desc



      • List of all InfoPath form library and total items:

        Collapse



        select  
        case when webs.fullurl = ''
        then 'Portal Site'
        else webs.fullurl
        end as [Site Relative Url],
        webs.Title As [Site Title],
        lists.tp_title As Title,
        tp_description As Description,
        tp_itemcount As [Total Item]
        from lists inner join webs ON lists.tp_webid = webs.Id
        Where tp_servertemplate = 115 -- Infopath Library

        order by tp_itemcount desc



      • List of generic list and total items:

        Collapse



        select  
        case when webs.fullurl = ''
        then 'Portal Site'
        else webs.fullurl
        end as [Site Relative Url],
        webs.Title As [Site Title],
        lists.tp_title As Title,
        tp_description As Description,
        tp_itemcount As [Total Item]
        from lists inner join webs ON lists.tp_webid = webs.Id
        Where tp_servertemplate = 100 -- Generic List

        order by tp_itemcount desc



      • Total number of documents:

        Collapse



        SELECT COUNT(*)
        FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
        INNER JOIN Sites ON Webs.SiteId = SItes.Id
        WHERE
        Docs.Type <> 1 AND (LeafName NOT LIKE '%.stp')
        AND (LeafName NOT LIKE '%.aspx')
        AND (LeafName NOT LIKE '%.xfp')
        AND (LeafName NOT LIKE '%.dwp')
        AND (LeafName NOT LIKE '%template%')
        AND (LeafName NOT LIKE '%.inf')
        AND (LeafName NOT LIKE '%.css')



      • Total MS Word documents:

        Collapse



        SELECT count(*)
        FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
        INNER JOIN Sites ON Webs.SiteId = SItes.Id
        WHERE
        Docs.Type <> 1 AND (LeafName LIKE '%.doc')
        AND (LeafName NOT LIKE '%template%')



      • Total MS Excel documents:

        Collapse



        SELECT count(*)
        FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
        INNER JOIN Sites ON Webs.SiteId = SItes.Id
        WHERE
        Docs.Type <> 1 AND (LeafName LIKE '%.xls')
        AND (LeafName NOT LIKE '%template%')



      • Total MS PowerPoint documents:

        Collapse



        SELECT count(*)
        FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
        INNER JOIN Sites ON Webs.SiteId = SItes.Id
        WHERE
        Docs.Type <> 1 AND (LeafName LIKE '%.ppt')
        AND (LeafName NOT LIKE '%template%')



      • Total TXT documents:

        Collapse



        SELECT count(*)
        FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
        INNER JOIN Sites ON Webs.SiteId = SItes.Id
        WHERE
        Docs.Type <> 1 AND (LeafName LIKE '%.txt')
        AND (LeafName NOT LIKE '%template%')



      • Total Zip files:

        Collapse



        SELECT count(*)
        FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
        INNER JOIN Sites ON Webs.SiteId = SItes.Id
        WHERE
        Docs.Type <> 1 AND (LeafName LIKE '%.zip')
        AND (LeafName NOT LIKE '%template%')



      • Total PDF files:

        Collapse



        SELECT count(*)
        FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
        INNER JOIN Sites ON Webs.SiteId = SItes.Id
        WHERE
        Docs.Type <> 1 AND (LeafName LIKE '%.pdf')
        AND (LeafName NOT LIKE '%template%')



      • Total JPG files:

        Collapse



        SELECT count(*)
        FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
        INNER JOIN Sites ON Webs.SiteId = SItes.Id
        WHERE
        Docs.Type <> 1 AND (LeafName LIKE '%.jpg')
        AND (LeafName NOT LIKE '%template%')



      • Total GIF files:

        Collapse



        SELECT count(*)
        FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
        INNER JOIN Sites ON Webs.SiteId = SItes.Id
        WHERE
        Docs.Type <> 1 AND (LeafName LIKE '%.gif')
        AND (LeafName NOT LIKE '%template%')



      • Total files other than DOC, PDF, XLS, PPT, TXT, Zip, ASPX, DEWP, STP, CSS, JPG, GIF:

        Collapse



        SELECT count(*)
        FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
        INNER JOIN Sites ON Webs.SiteId = SItes.Id
        WHERE
        Docs.Type <> 1 AND (LeafName NOT LIKE '%.pdf')
        AND (LeafName NOT LIKE '%template%')
        AND (LeafName NOT LIKE '%.doc')
        AND (LeafName NOT LIKE '%.xls')
        AND (LeafName NOT LIKE '%.ppt')
        AND (LeafName NOT LIKE '%.txt')
        AND (LeafName NOT LIKE '%.zip')
        AND (LeafName NOT LIKE '%.aspx')
        AND (LeafName NOT LIKE '%.dwp')
        AND (LeafName NOT LIKE '%.stp')
        AND (LeafName NOT LIKE '%.css')
        AND (LeafName NOT LIKE '%.jpg')
        AND (LeafName NOT LIKE '%.gif')
        AND (LeafName <>'_webpartpage.htm')



      • Total size of all documents:

        Collapse



        SELECT SUM(CAST((CAST(CAST(Size as decimal(10,2))/1024 
        As decimal(10,2))/1024) AS Decimal(10,2)))
        AS 'Total Size in MB'
        FROM Docs INNER JOIN Webs On Docs.WebId = Webs.Id
        INNER JOIN Sites ON Webs.SiteId = SItes.Id
        WHERE
        Docs.Type <> 1 AND (LeafName NOT LIKE '%.stp')
        AND (LeafName NOT LIKE '%.aspx')
        AND (LeafName NOT LIKE '%.xfp')
        AND (LeafName NOT LIKE '%.dwp')
        AND (LeafName NOT LIKE '%template%')
        AND (LeafName NOT LIKE '%.inf')
        AND (LeafName NOT LIKE '%.css')
        AND (LeafName <>'_webpartpage.htm')


      Sunday, August 1, 2010

      Shorting the content DB from enumlist.txt

      this morning try to get the URL and Database from enumlist for orphan object checking , but after import to excel with delimited but look like the information is not good enough. ha ha, then learnt some excel function to short it out. so want to keep track new excel command i learnt :

       

      <Site Url=http://abc.abc.com Owner="abc\abc" ContentDatabase="MOSS_Content_DB1" StorageUsedMB="28.7" StorageWarningMB="80" StorageMaxMB="100" />

       

      this command is trim up the front part

      =RIGHT(A1,LEN(A1)-FIND("e=",A1))

       

      Results is :

      ="MOSS_Content_DB1" StorageUsedMB="28.7" StorageWarningMB="80" StorageMaxMB="100" />

       

      Another column  is trim out the left over

      =LEFT(B2,(FIND("St",B2)))

       

      Results is:

      ="MOSS_Content_DB1" S

       

      At last replace the “ and “ S with space, then you will get the DB name :)

       

      Maybe look stupid , but i get what i want!! i think will have more easy way , but i unable to think out yet. so i just use the faster skill i know to get my information i think this is fair.

       

      please do share with me the easy way , if you have :)