#SCCM Expert #Private Cloud Consultant#SQL Expert#Corporate Trainer #Automation Consultant

Scripts

SCCM report to show disk utilization for all endpoints

Hi There ,

Yesterday i had a requirement to go through the  disk space utilization for all the servers in our environment to keep a track before year end . Now remember we are not looking for a report that shows low disk space which you can readily get through SCCM reports . This report shows you the overall disk space utilization  .

Please find the the query for SQL given below :

SELECT SYS.Name,LDISK.DeviceID0, LDISK.Description0, LDISK.VolumeName0,
LDISK.FreeSpace0 as [Free space (MB)],
LDISK.FreeSpace0/1024 as [Free space (GB)],
LDISK.Size0/1024 as [Total space (GB)],
LDISK.FreeSpace0*100/LDISK.Size0 as C074
FROM v_FullCollectionMembership SYS
join v_GS_LOGICAL_DISK LDISK on SYS.ResourceID = LDISK.ResourceID
JOIN v_R_System RSYS ON SYS.ResourceID = RSYS.ResourceID
WHERE
LDISK.DriveType0 =3 AND
LDISK.Size0 > 0
AND SYS.CollectionID = ‘SMS0001’
ORDER BY SYS.Name, LDISK.DeviceID0

Happy reporting 🙂

SQL query for SCCM to pull out Hostname, Ip Address , OU location and Operating system

Hi everyone ,

A simple sql query that will get you the hostname , IP address , OU location and operating system details from your SCCM database .

Select s.Netbios_Name0,ip.IP_Addresses0,
s.Distinguished_Name0,s.operatingSystem0,
s.Operating_System_Name_and0
from v_RA_System_IPAddresses AS IP
inner join v_R_System AS S
on ip.ResourceID=s.ResourceID
where operatingSystem0 like ‘%Server%’;

My requirement was for servers , but if you want desktops and servers both you can modify the query accordingly .

SQL Query for checking updates installed on workstations or servers in the last 60 days

Hi everyone ,

Please find attached the script which you can use to pull out information for the updates installed on last 60 days on a particular collection by giving the input as collection ID .You can also modify the script accordingly for the number of days in datediff condition .

patchquery

VB script to install all updates targeted to a server or desktop :

We all might have come across some scenario where we have to patch servers which are isolated and are not managed by software update management tools like SCCM . Or if you are managing the server through WSUS but cannot manage it through GPO and want to make sure the server is fully patched . You can just run this script and it will install all the updates required by the server with proper logs generated in C:\Windows by the name of WSUS.log and you can check all the information in the logs .

Note:Copy the content to the text file and rename the file to .vbs to run the script.

installupdatescustom

SQL  Script to get information for distinguished name , System OU name , Host name ,Chasis name , Serial number , domain name , Total physical memory all in one query .

systeminformation

Have fun querying 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Translate

%d bloggers like this: