Ontario Small Businesses


http://paultrailers.com/

Automating Network Computers to a GPO

Effectively Move computers to the right OU based on Operating System or locations via GPO:

This script will help you to move computer accounts from current locations into target OU.
You can schedule the Power shell script using the server task schedule.

Code
 $test=Get-ADComputer -Filter * -searchbase "cn=computers,dc=domain,dc=com"  -properties operatingsystem  | where-object {$_.operatingsystem -eq "Windows 10 Pro"}

foreach ($obj in $test)
{move-adobject $obj -targetpath "ou=WSUS Computers,dc=domain,dc=com" }



$test2=Get-ADComputer -Filter * -searchbase "cn=computers,dc=domain,dc=com"  -properties operatingsystem  | where-object {$_.operatingsystem -eq "Windows 7 Professional"}

foreach ($obj in $test2)
{move-adobject $obj -targetpath "ou=WDG Computers,dc=domain,dc=com" }





$test3=Get-ADComputer -Filter * -searchbase "cn=computers,dc=domain,dc=com"  -properties operatingsystem  | where-object {$_.operatingsystem -like "Windows XP Professional"}

foreach ($obj in $test3)
{move-adobject $obj -targetpath "ou=WDG Computers,dc=domain,dc=com" }

Reference

https://gallery.technet.microsoft.com/scriptcenter/Move-AD-Computer-Object-4ed2c5f8

Manage Engine - Reporting (Custom Queries)

Category List 

SELECT CategoryDefinition.CATEGORYNAME "Category Name",CategoryDefinition.CATEGORYDESCRIPTION "Category Description" ,SubCategoryDefinition.NAME "Sub Category Name" ,SubCategoryDefinition.DESCRIPTION "Sub Category Description",  ItemDefinition.NAME"Item Name",ItemDefinition.DESCRIPTION "Item Description" FROM CategoryDefinition left join SubCategoryDefinition on  SubCategoryDefinition.CATEGORYID=CategoryDefinition.CATEGORYID  left join ItemDefinition on ItemDefinition.SUBCATEGORYID = SubCategoryDefinition.SUBCATEGORYID ORDER BY 1,3,5

Category List with Count

SELECT cd.CATEGORYNAME "Category",scd.NAME "Subcategory",icd.NAME "Item", count(wo.WORKORDERID) "Request Count" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID WHERE (wo.ISPARENT='1') GROUP BY cd.CATEGORYNAME ,scd.NAME ,icd.NAME
order by 1

Asset Assignment

SELECT max(productType.COMPONENTTYPENAME) "Product Type" ,max(res.RESOURCENAME) "Workstation Name" ,LONGTODATE(max(currenthist.STARTTIME)) "Changed Date" ,max(aaa.FIRST_NAME) "Changed By" ,max(AaaUser.FIRST_NAME) "Assigned User" ,( SELECT MAX(olduser.FIRST_NAME) OldUser FROM ResourceownerHistory oldhist LEFT JOIN SDUser oldsduser ON oldhist.USERID = oldsduser.USERID LEFT JOIN AaaUser olduser ON oldsduser.USERID = olduser.USER_ID WHERE oldhist.ENDTIME / 1000 = currenthist.STARTTIME / 1000 AND oldhist.RESOURCEID = currenthist.RESOURCEID ) "Last Assgined User" ,max(rstate.DISPLAYSTATE) "Previous State" ,max(ResourceState.DISPLAYSTATE) "Current State" ,max(DepartmentDefinition.DEPTNAME) "Assigned Dept", max(product.COMPONENTNAME) "Product" , max(Resources.RESOURCENAME) "Resource Name", max(res.SERIALNO) "Serial Number", max(systemInfo.SERVICETAG) "Service Tag"
  FROM ResourceStateHistory LEFT JOIN ResourceOwnerHistory currenthist ON ResourceStateHistory.STATEHISTORYID = currenthist.STATEHISTORYID LEFT JOIN ResourceState ON ResourceStateHistory.RESOURCESTATEID = ResourceState.RESOURCESTATEID LEFT JOIN ResourceAssociationHistory ON currenthist.RESOURCEOWNERID = ResourceAssociationHistory.RESOURCEOWNERID LEFT JOIN Resources ON ResourceAssociationHistory.ASSTTORESOURCEID = Resources.RESOURCEID LEFT JOIN SDUser ON currenthist.USERID = SDUser.USERID LEFT JOIN AaaUser ON SDUser.USERID = AaaUser.USER_ID LEFT JOIN DepartmentDefinition ON currenthist.DEPTID = DepartmentDefinition.DEPTID LEFT JOIN Resources res ON ResourceStateHistory.RESOURCEID = res.RESOURCEID LEFT JOIN ResourceState rstate ON ResourceStateHistory.PREVRESOURCESTATEID = rstate.RESOURCESTATEID LEFT JOIN AaaUser aaa ON ResourceStateHistory.USERID = aaa.USER_ID LEFT JOIN ComponentDefinition product ON res.COMPONENTID = product.COMPONENTID LEFT JOIN ComponentType productType ON product.COMPONENTTYPEID = productType.COMPONENTTYPEID 
LEFT JOIN SystemInfo systemInfo ON res.resourceid=SystemInfo.workstationid
WHERE productType.COMPONENTTYPENAME != 'Software' GROUP BY currenthist.RESOURCEID ,currenthist.STARTTIME ORDER BY 2 ,3


All Asset 

SELECT aaaUser.FIRST_NAME "User",aaaUser.FIRST_NAME "User",productType.COMPONENTTYPENAME "Product Type",product.COMPONENTNAME "Product",resource.RESOURCENAME "Resource Name",resource.SERIALNO "Serial Number",resource.ASSETTAG "Asset Tag",systemInfo.SERVICETAG "Service Tag" FROM Resources resource LEFT JOIN ComponentDefinition product ON resource.COMPONENTID=product.COMPONENTID LEFT JOIN ComponentType productType ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID LEFT JOIN ResourceOwner rOwner ON resource.RESOURCEID=rOwner.RESOURCEID LEFT JOIN ResourceAssociation rToAsset ON rOwner.RESOURCEOWNERID=rToAsset.RESOURCEOWNERID LEFT JOIN SDUser sdUser ON rOwner.USERID=sdUser.USERID LEFT JOIN AaaUser aaaUser ON sdUser.USERID=aaaUser.USER_ID LEFT JOIN SystemInfo systemInfo ON resource.resourceid=SystemInfo.workstationid WHERE  ((((((productType.COMPONENTTYPENAME = 'Headsets') OR (productType.COMPONENTTYPENAME = 'Monitors')) OR (productType.COMPONENTTYPENAME = 'Printer')) OR (productType.COMPONENTTYPENAME = 'Workstation')) OR (productType.COMPONENTTYPENAME = 'Scanner')) AND (aaaUser.FIRST_NAME IS NOT NULL))  ORDER BY 1, 3

Support Tickets

SELECT wo.WORKORDERID "Request ID",mdd.MODENAME "Request Mode",aau.FIRST_NAME "Requester",AaaContactInfo.EMAILID "Email",sdu.JOBTITLE "Job Title",wo.TITLE "Subject",rtdef.NAME "Request Type",cd.CATEGORYNAME "Category",scd.NAME "Subcategory",icd.NAME "Item",qd.QUEUENAME "Group",ti.FIRST_NAME "Technician",std.STATUSNAME "Request Status",
longtodate(wo.CREATEDTIME) "Created Time",
longtodate(wo.COMPLETEDTIME) "Completed Time" FROM WorkOrder wo LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN AaaUserContactInfo ON aau.USER_ID=AaaUserContactInfo.USER_ID 
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID  WHERE (wo.ISPARENT='1')

Support Tickets (Processed in Current Week)

SELECT ti.FIRST_NAME AS "Technician", ti.FIRST_NAME AS "Technician", wo.TITLE AS "Subject", wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", longtodate(wo.CREATEDTIME) AS "Created Time", std.STATUSNAME AS "Request Status" FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE  (((((ti.FIRST_NAME = 'Barbara Hayward') OR (ti.FIRST_NAME = 'Ehsan Ullah')) OR (ti.FIRST_NAME = 'Paul Kouptsov')) OR (ti.FIRST_NAME = 'Yordy Napoles')) AND (((longtodate(wo.CREATEDTIME) >= 1565496000000) AND ((longtodate(wo.CREATEDTIME) != 0) AND (longtodate(wo.CREATEDTIME) IS NOT NULL))) AND ((longtodate(wo.CREATEDTIME) <= 1566100799000) AND (((longtodate(wo.CREATEDTIME) != 0) AND (longtodate(wo.CREATEDTIME) IS NOT NULL)) AND (longtodate(wo.CREATEDTIME) != -1)))))  AND wo.ISPARENT='1'  ORDER BY 1 NULLS FIRST , 7 NULLS FIRST