Skip to content

Welcome to Chaya Digital - questions and solutions are both welcome! Please be as specific as possible when asking a question so that I can answer it accurately and quickly.

Please visit my store!

Script Fix to Expand All Items in SharePoint 2013 Calendar to Show More Than Three Using jQuery

I’m not really sure why Microsoft made the decision to auto-hide when you have more than three items, then make it so difficult to reset (bad Microsoft, no cookie!), but this is a quick workaround I have confirmed works even with a large number of overlay calendars and lots of events.

For testing, I recommend setting the final setTimeout to 2000 to make sure the calendars and events are all loading, then play with your final setting – I found 800 ms worked well for my environment with a bit of wiggle room and not too much lag.

JavaScript for More Than 3 Items in SharePoint Calendar

I like to put this in the Site Collection root Style Library folder or a custom AdvancedContent library folder, then reference it in a Content Editor Web Part on the calendar page where it is needed for reusability. You should also be able to just throw it in a script block, assuming you have jquery files to reference. A similar approach can be used to make a pretty and persistent Expand All button if needed.


<script src="https://sharepoint.com/it/Style Library/jquery.1.11.1.min.js"></script>
<script>
$( document ).ready(function() {
	//$("div").css("color","red");    
    
    
    var pollCount = 0;
    checkCalendar($);

function checkCalendar($) {
	//alert("Checking for Calendar load");
   //stop after 10 seconds
   if (pollCount > 20)
      return;
   //if the calendar hasn't loaded yet, then wait .5 seconds
   //if ($('.ms-acal-title a').length < 1) {
   if ($('.ms-cal-nav').length < 1) {
      pollCount++;
      setTimeout(function(){checkCalendar($);}, 500);  
      //alert("Checking " + pollCount);   
   }
   else {
      //the calendar has loaded, so do work
      	setTimeout(function(){  
      	//alert("Found title, running expand");
         var calendar = SP.UI.ApplicationPages.CalendarInstanceRepository.firstInstance();
         if (calendar && typeof calendar.expandAll === 'function') {calendar.expandAll();}
      	}, 800);
         

      //});
    }
}

    
});
</script>


Have a cup of tea! You’re Done!

PowerShell Active Directory Scripts to Check for Service Account Lockouts and Pull Event Logs From Domain Controllers

So if you’ve ever had users repeatedly locking out a service account, you may know what a nightmare it can be to track down and fix, as well as the problems it can cause with applications or automations. While the best approach is for users to use their own accounts, in some cases (due to legacy or configuration requirements) it is not possible. Also, sometimes users who know better are the culprits, like developers.

Here are two scripts for checking and getting details on the lockouts. I recommend setting them in Task Scheduler to check every 5 minutes and alert your team.

AD_lockoutalert.ps1

 

########## c:\ps_scripts\ad_lockoutalert.ps1

import-module activedirectory

#######variables
$email ="ebruce@thedomain.com,helpdesk@thedomain.com"
$mailfrom ="ebruce@thedomain.com"
$mailsmtp ="mail.thedomain.com"
$Reportname ="[Error] Sensitive Accounts Locked Out in Active Directory"
$alertflag= $False
$filepath = "c:\ps_output\checklocked.txt"

####### users to check - by AD username/samaccountname
$users = @("ServiceAccount1","ServiceAccount2","ServiceAccount3")
write-host $users

########### add headers
"<style> p {font-family:arial;} </style>" | out-file $filepath -append
"<p><b>Sensitive Accounts Locked Out in Active Directory</b></p><hr />" | out-file $filepath -append

########## Check users
foreach ($user in $users){
  $usertocheck = get-aduser $user -prop LockedOut
  $lockedstatus = $usertocheck.LockedOut
  $line = "[" + $lockedstatus + "] <b>"+ $usertocheck.samaccountname + ":</b> AD LockedOut status"
  write-host $line
  write-host ---------------------------------------
    if ($lockedstatus -eq $True){ 
	write-host Locked out has validated to True
	$alertflag = $True
	write-host alertflag is $alertflag
        "<p style="color: red">$line is <b>LOCKED!</b><p></p>" | out-file $filepath -append
    }else{ 
	write-host FALSE
	"<p>$line is <b>UNLOCKED</b></p>" | out-file $filepath -append
    }

  
  }

########## send mail only on Lockout = True found
if($alertflag -eq $True){
send-mailmessage -to "$email" -from "$mailfrom" -smtp "$mailsmtp" -subject "$Reportname" -bodyashtml -body "$(get-content $filepath | out-string)" #-att $filepath
}

###########Delete created file
write-host "deleting $filepath"
Remove-Item "$filepath"

#While(Test-Path $filepath) {Remove-Item $filepath}
#$userslocked = search-adaccount -lockedout
#$userslocked | select samaccountname | write-host

 

Get-EventLogsLockouts.ps1


# c:\ps_scripts\get-eventlogslockouts.ps1
import-module ActiveDirectory

########### variables
$S = "DC1","DC2"
[xml]$a = Get-Content "C:\ps_scripts\getEventLogsLockouts.xml"
$filepath = "c:\ps_output\dev_Get-eventslogslockouts.txt"
$Reportname = "[Error] Event Log Lockout Details"
$email ="ebruce@thedomain.com,helpdesk@thedomain.com"
$mailfrom ="ebruce@thedomain.com"
$mailsmtp ="mail.thedomain.com"

########### Check each dc server for events
foreach ($server in $S){
  Write-host Checking $server ...
  $myevents = Get-WinEvent -FilterXml $a -computername $server 
  write-host EVENTS FROM $server
  "EVENTS FROM $server"| out-file $filepath -append
  $myevents = $myevents| format-list -prop message
  $myevents | out-file $filepath -append
}

########## send email
$content = Get-Content $filepath
Write-host $filepath | out-string
send-mailmessage -to "$email" -from "$mailfrom" -smtp "$mailsmtp" -subject "$Reportname" -body "$(get-content $filepath | out-string)" 

-att $filepath

###########Delete created file
write-host "deleting $filepath"
Remove-Item "$filepath"

The XML file

<querylist>
<query id="0" path="Security">
<select path="Security"> *[System[(EventID=4740)]] and *[EventData[Data[@Name='TargetUserName']='MyServiceAccountName']]</select>
</query>
</querylist>

PowerShell to Get All Windows Servers and Operating Systems From Active Directory

So you need a quick way to inventory the servers in your domain? Here is an easy way to get this with PowerShell.

You would modify for your environment and can tweak the details and properties as needed.

Since most of my scripts require a report to be emailed to end users and/or sharepoint, this includes the step and a cleanup of the file that was created on the local machine, so data does not get duplicated and left on the local system.

In general, this setup works great for my needs and meet management requirements!

——————————-

######## TO USE ##################
## c:\ps_scripts\get-allservers.ps1 -email “ebruce@mydomain.com” -reportname “All_Windows_Servers”
#############Define input parameters – must be at top
[CmdletBinding()]
Param(
[Parameter(Mandatory=$true)]
[string]$reportname,

[Parameter(Mandatory=$True,Position=2)]
[string]$email

)

########### Load AD module for powershell, if run in  PowerShell, not AD powershell
import-module activedirectory

############# Set Variables
$a = Get-Date -format yyyy-MM-dd-HH-mm
$filepath = “c:\ps_output\”+$reportname+”_”+$a+”.csv”$mailsubject = “$reportname Report from AD”
$mailfrom = “bounceNotice@mydomain.com”  #this should be an email address you check for bad email addresses and bouncebacks – for example if an end user changes the SharePoint library email, or a user leaves the company

$smtp = “mail.mydomain.com”
$mailsubject = “$reportname Report from AD”

##############create file
Get-ADComputer -Filter {OperatingSystem -like “*Server*”} -Properties OperatingSystem |
Select Name,OperatingSystem |
Sort Name,OperatingSystem |
Export-csv $filepath -notypeinformation

#############Email
write-host “Mailing $filepath”
$body = “Report attached – $Reportname – contact ebruce@mydomain.com if there are any questions”
send-mailmessage -to “$email” -from “$mailfrom” -smtpserver “$smtp” -subject “$mailsubject” -body “$body” -attachments “$filepath”

###########Delete created csv file
write-host “deleting $filepath”
Remove-Item “$filepath”
#While(Test-Path $filepath) {Remove-Item $filepath}

 

ENJOY!

Grant Permissions to a Database for Windows Active Directory User or Group in Microsoft SQL Server

Your users may get errors performing certain functions required by their job, but not covered in the standard roles, such as DB_DataReader.

Error: SHOWPLAN permission denied in database “mydatabasename”

This is just an example, but often happens when users need to write and then evaluate the plan for queries, ie. do some optimization evaluations. Once you have identified the permission needed, and what it is needed to, it is just a matter of using the proper syntax!

SQL User

use MyDatabaseName
go
grant showplan to [mysqluser]
go

Windows Active Directory User

use MyDatabaseName
go
grant showplan to [mydomain\myusername]
go

Windows Active Directory Group

use MyDatabaseName
go
grant showplan to [mydomain\mygroup_r]
go

The users should now be able to use the additional features your have enabled!

 

Setup Database Mail in SQL Server 2012 and 2014

Server Email Settings

  1. SSMS > connect to SQL instance
  2. Management > Database Mail > Right click > Configure Database Mailimage
  3. First time, you may get an “intro” screen you can check to hide in future > next
  4. Set up database mail…. radio button > nextimage
  5. Fill out database mail for your company settingsimage

 

 

Set Default Operator and Agent Settings

  1. Set up sql server agent > operators > new > create operator
  2. Select Notifications for this operator or come back later (multiple operators and schedules can be createdimageimage
  3. Enable Mail Profile on Sql Server Agent > properties > alert system (set default Operator also and token replacement if needed)image
  4. Set the Database Mail profile Security settings to public instead of private
    1. Database Mail right click > configure database mail > Manage Profile Security
    2. Set default profile *** don’t forget this dropdown or alerts will not send!

image

NOTE: Restart sql server instance (machine does not need restart, just instance via ssms) to register changes (alerts will not send, but maintenance plan notification tasks and test will still execute and send mail before restart).

Related Articles:

SQL Server Alerts and Notices Best Practices >

Hide the Show More Link and Show All Data in SharePoint 2013 Task List

image

The easiest way to do this is to go through the SP Web Site:

  1. Task List > List tab > List Settings
  2. Content Types > click on tehe Task content type
  3. Change the order of anything (including moving a custom column)
  4. Both Edit and View forms will now be updated

NOTE: Hiding may also do this, as you have effectively changed the default sharepoint form template.

Yeah!

How to Delete Metalogix Content Matrix Job History

Sometimes the tool times out when trying to delete job history on Microsoft SQL Server.

Solution

  1. click on job
  2. Click on “export to excel”image
  3. in the popup, select the GUID (the part after the DB name)image
  4. replace the GUID in the script below

--export to excel - GUID After "DB"

-- ex: e9bb6013-a732-410c-9892-912b62aae4f9 delete FROM [Metalogix_JobHistoryDB].[dbo].[JobLogItems] where jobid = '8ed55aec-8da3-4234-a901-06d92aeb6b36' delete FROM [Metalogix_JobHistoryDB].[dbo].[JobHistory] where jobid = '8ed55aec-8da3-4234-a901-06d92aeb6b36'

or just view job history:

SELECT * FROM [Metalogix_JobHistoryDB].[dbo].[JobLogItems]

  where jobid = ‘3cea22b9-90d0-44c2-922d-25ae46ff9c49’

  select * FROM [Metalogix_JobHistoryDB].[dbo].[JobHistory]

  where jobid = ‘3cea22b9-90d0-44c2-922d-25ae46ff9c49’

Enable Server Side Include Processing in IIS 7.5

Enable processing of HTML files for SSI

NOTE: I recommend you do this only for specific sites which need it, but it can be done at the site or server level, just click on the proper location when looking for the Handler Mapping icon.

  1. Enable the IIS Server side Include (SSI) feature on the server via add/remove features
  2. Set up the file handler for HTML – ssi are only processed on HTML files by default
    1. open IIS7 > click on website > In view pane, click on icon for IIS > Handler Mappings
    2. Under actions on right > click on "Add Module Mapping”
    3. Enter the following informationn
      1. Request Path: *.html
      2. Module: ServerSideIncludeModule
      3. Executable: blank
      4. Name: SSI-htm (or whatever you choose to name it)clip_image002
    4. You should now see a new Mapping entryclip_image004
    5. Click Ok and test
      1. do IIS reset if not working
      2. restart machine if not working

Best Practice Alerts & Notifications with SQL Script

I got tired of doing this by hand, and had a moment so I scripted out the SQL Alerts And Notifications

tSQL Query

USE msdb ;
GO
----------------LOW
EXEC dbo.sp_add_alert
   @name = N'Severity Level 014: Insufficient Permissions - Low',
   --@message_id = 55001, 
   @severity = 14, 
   @notification_message = N'Description: Non-serious errors. Often is considered user correctable.', 
   @delay_between_responses =5,
   @include_event_description_in = 1
   --,@job_name = N'Back up the AdventureWorks2012 Database' ;
GO
---------------MEDIUM
EXEC dbo.sp_add_alert
   @name = N'Severity Level 017: Insufficient Resources - Medium',
   --@message_id = 55001, 
   @severity = 17, 
   @notification_message = N'Description: Severity levels from 17 through 19 will require 
   intervention from a DBA, they are not as serious as 20-25 but the DBA needs to be alerted.', 
   @delay_between_responses =5,
   @include_event_description_in = 1
   --,@job_name = N'Back up the AdventureWorks2012 Database' ;
GO
EXEC dbo.sp_add_alert
   @name = N'Severity Level 018: Nonfatal Internal Error - Medium',
   --@message_id = 55001, 
   @severity = 18, 
   @notification_message = N'Description: Severity levels from 17 through 19 will require 
   intervention from a DBA, they are not as serious as 20-25 but the DBA needs to be alerted.', 
   @delay_between_responses =5,
   @include_event_description_in = 1
   --,@job_name = N'Back up the AdventureWorks2012 Database' ;
GO
EXEC dbo.sp_add_alert
   @name = N'Severity Level 019: Error In Resources - Medium',
   --@message_id = 55001, 
   @severity = 19, 
   @notification_message = N'Description: Severity levels from 17 through 19 will require 
   intervention from a DBA, they are not as serious as 20-25 but the DBA needs to be alerted.', 
   @delay_between_responses =5,
   @include_event_description_in = 1
   --,@job_name = N'Back up the AdventureWorks2012 Database' ;
GO
------------HIGH
EXEC dbo.sp_add_alert
   @name = N'Severity Level 020: Error In Current Process - High',
   --@message_id = 55001, 
   @severity = 20, 
   @notification_message = N'Description: Severity Levels 20 through 25 are serious errors 
   that mean SQL Server is no longer working, notify the DBA immediately.', 
   @delay_between_responses =5,
   @include_event_description_in = 1
   --,@job_name = N'Back up the AdventureWorks2012 Database' ;
GO
EXEC dbo.sp_add_alert
   @name = N'Severity Level 021: Fatal Error In DB DBID Processes - High',
   --@message_id = 55001, 
   @severity = 21, 
   @notification_message = N'Description: Severity Levels 20 through 25 are serious errors 
   that mean SQL Server is no longer working, notify the DBA immediately.', 
   @delay_between_responses =5,
   @include_event_description_in = 1
   --,@job_name = N'Back up the AdventureWorks2012 Database' ;
GO
EXEC dbo.sp_add_alert
   @name = N'Severity Level 022: Fatal Error Table Integrity Suspect - High',
   --@message_id = 55001, 
   @severity = 22, 
   @notification_message = N'Description: Severity Levels 20 through 25 are serious errors 
   that mean SQL Server is no longer working, notify the DBA immediately.', 
   @delay_between_responses =5,
   @include_event_description_in = 1
   --,@job_name = N'Back up the AdventureWorks2012 Database' ;
GO
EXEC dbo.sp_add_alert
   @name = N'Severity Level 023: Fatal Error DB Integrity Suspect - High',
   --@message_id = 55001, 
   @severity = 23, 
   @notification_message = N'Description: Severity Levels 20 through 25 are serious errors 
   that mean SQL Server is no longer working, notify the DBA immediately.', 
   @delay_between_responses =5,
   @include_event_description_in = 1
   --,@job_name = N'Back up the AdventureWorks2012 Database' ;
GO
EXEC dbo.sp_add_alert
   @name = N'Severity Level 024: Fatal Error Hardware – High',
   --@message_id = 55001, 
   @severity = 24, 
   @notification_message = N'Description: Severity Levels 20 through 25 are serious errors 
   that mean SQL Server is no longer working, notify the DBA immediately.', 
   @delay_between_responses =5,
   @include_event_description_in = 1
   --,@job_name = N'Back up the AdventureWorks2012 Database' ;
GO
EXEC dbo.sp_add_alert
   @name = N'Severity Level 025: Fatal Error Hardware – High',
   --@message_id = 55001, 
   @severity = 25, 
   @notification_message = N'Description: Severity Levels 20 through 25 are serious errors 
   that mean SQL Server is no longer working, notify the DBA immediately.', 
   @delay_between_responses =5,
   @include_event_description_in = 1
   --,@job_name = N'Back up the AdventureWorks2012 Database' ;
GO
-------------Create Operator
EXEC dbo.sp_add_operator
    @name = N'Ebruce',
    @enabled = 1,
    @email_address = N'ebruce@mydomain.com'
    --@pager_address = N'5551290AW@pager.Adventure-Works.com',
    --@weekday_pager_start_time = 080000,
    --@weekday_pager_end_time = 170000,
    --@pager_days = 62 ;
GO
------------Assign Alerts to Operator
EXEC dbo.sp_add_notification N'Severity Level 014: Insufficient Permissions - Low', N'Ebruce', 1 ;
EXEC dbo.sp_add_notification N'Severity Level 017: Insufficient Resources - Medium', N'Ebruce', 1 ;
EXEC dbo.sp_add_notification N'Severity Level 018: Nonfatal Internal Error - Medium', N'Ebruce', 1 ;
EXEC dbo.sp_add_notification N'Severity Level 019: Error In Resources - Medium', N'Ebruce', 1 ;
EXEC dbo.sp_add_notification N'Severity Level 020: Error In Current Process - High', N'Ebruce', 1 ;
EXEC dbo.sp_add_notification N'Severity Level 021: Fatal Error In DB DBID Processes - High', N'Ebruce', 1 ;
EXEC dbo.sp_add_notification N'Severity Level 022: Fatal Error Table Integrity Suspect - High', N'Ebruce', 1 ;
EXEC dbo.sp_add_notification N'Severity Level 023: Fatal Error DB Integrity Suspect - High', N'Ebruce', 1 ;
EXEC dbo.sp_add_notification N'Severity Level 024: Fatal Error Hardware – High', N'Ebruce', 1 ;
EXEC dbo.sp_add_notification N'Severity Level 025: Fatal Error Hardware – High', N'Ebruce', 1 ;

Great Plains EFT Batch Recovery Frozen Error

When running an EFT batch, sometimes you will find a $0 dollar check can cause and error and Batch Recovery does not work.

Errors

Report says: “Duplicate check numbers are not allowed. This transaction contains errors. It won’t be posted”

GP Menu > Tools > Routines > Batch Recovery

clip_image002

Cause

GP marks all zero dollar checks with the first number of the batch and the prefix REMIT

· First check in batch REMIT00006789

· All $0.00 checks in batch REMIT00006789

It is recommended you change the checkbook prefix from REMIT to EFT to avoid any conflicts

· First check in batch FT00006789

· All $0.00 checks in batch REMIT00006789

You could also allow duplicate checks, but that is often not feasible

Solution

Only zero ($0.00) dollar checks should not go through, confirm this. You will need to edit or recreate and possibly rerun the failing checks as another batch – resolve as needed.

Dynamics Database

    1. Sys00800 – should not have any entries
    2. Sy00801 – should not have any entries (GL_Normal is general ledger – ignore)

Company Database

    1. UPDATE SY00500 SET MKDTOPST=0, BCHSTTUS=0 where BACHNUMB=’yourbatchnumerhere’

Consider changing the checkbook prefix to EFT to avoid future issues

  1. Cards menu in left column > Financial > checkbook > select checkbook
  2. EFT button > Payables Options button
  3. EFT Payment Numbers > change just the prefix REMIT to EFT
  4. Ok > Ok > Save

 

Resources:

https://support2.microsoft.com/kb/2620452/en-us?sd=rss&spid=12629

Follow

Get every new post delivered to your Inbox.

Join 270 other followers