SOLVED

Office 365 license consumption alert

%3CLINGO-SUB%20id%3D%22lingo-sub-56036%22%20slang%3D%22en-US%22%3EOffice%20365%20license%20consumption%20alert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-56036%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20Microsoft%20provides%20any%20alerting%20on%20the%20case%20that%20availabe%20licenses%20to%20assign%20are%5Cnear%20to%20be%200%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20already%20see%20that%20several%20blogs%20provide%20details%20for%20creating%20your%20own%20alerting%5Creport%20for%20license%20consumption%20using%20powershell%2C%20but%20I'm%20interested%20to%20confirm%20if%20Microsoft%20has%20something%20out%20of%20the%20box.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-56036%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-206990%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20license%20consumption%20alert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-206990%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20issue%2C%20the%20lack%20of%20an%20Office%20365%20license%20consumption%20alert%2C%20is%20still%20unresolved%20(06%2F22%2F2018).%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20a%20feedback%20forum%20post%20for%20the%20task%3B%26nbsp%3B%3CA%20title%3D%22Get%20'low%20license%20count'%20notification%22%20href%3D%22https%3A%2F%2Ffeedback.azure.com%2Fforums%2F169401-azure-active-directory%2Fsuggestions%2F33768322-get-low-license-count-notification%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EAzure%20AD%20feedback%3A%20Get%20'low%20license%20count'%20notification%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20vote%20it%20up%20if%20you%20really%20want%20to%20see%20the%20change.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20as%20others%20have%20noted%2C%20you%20can%20set%20up%20a%20scheduled%20task%20to%20PowerShell%20an%20email%20to%20your%20relevant%20crew%20who%20may%20see%20going%20to%20the%20portal%20as%20an%20inconvenience.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20included%20my%20script%20below%20with%20%3CDESCRIPTIONS%3E%20of%20items%20that%20need%20filled%20in.%26nbsp%3B%3C%2FDESCRIPTIONS%3E%3C%2FP%3E%3CP%3EIf%20you%20need%20help%20getting%20keys%20and%20such%20to%20encrypt%20passwords%2C%20try%20Share-gate%20or%20KeePass%20who%20provide%20good%20MS%20Online%20compatible%20encryption%20tools.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%23%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%23%20Send_O365LicensingReport.ps1%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%23%3C%2FP%3E%3CP%3E%24key%20%3D%20(%3CINTEGER%20array%3D%22%22%3E)%3B%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%24secureString%20%3D%20%22%3CENCRYPTED%20password%3D%22%22%3E%22%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%24PWord%20%3D%20ConvertTo-SecureString%20%24secureString%20-key%20%24key%3B%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%24User%20%3D%20%22%3CGLOBALADMIN%3E%40%3CCOMPANY%20domain%3D%22%22%20or%3D%22%22%20.microsoftonline.com%3D%22%22%3E%22%3B%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%24Credential%20%3D%20New-Object%20%E2%80%93TypeName%20%22System.Management.Automation.PSCredential%22%20%E2%80%93ArgumentList%20%24User%2C%20%24PWord%3C%2FCOMPANY%3E%3C%2FGLOBALADMIN%3E%3C%2FENCRYPTED%3E%3C%2FINTEGER%3E%3C%2FP%3E%3CP%3E%23Get%20the%20Office365%20tenant%20credentials%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3E%24Office365credentials%20%3D%20Get-Credential%20%24Credential%3B%3C%2FP%3E%3CP%3EConnect-MsolService%20-Credential%20%24Office365credentials%3B%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CBR%20%2F%3Ewrite-host%20%22Connecting%20to%20Microsoft%20SharePoint%20Online%20Service%20...%22%3C%2FP%3E%3CP%3E%5BString%5D%24SkuReport1%20%3D%20Get-msolAccountSku%20%7C%20Select%20%40%7BN%3D'Product%20Name'%3BE%3D%7Bswitch%20(%24_.AccountSkuId)%20%7B%20'%3CCOMPANY%20domain%3D%22%22%3E%3AENTERPRISEPREMIUM'%20%7B'Office%20365%20Enterprise%20E5'%7D%20'%3CCOMPANY%20domain%3D%22%22%3E%3APOWER_BI_PRO'%20%7B'Power%20BI%20Pro'%7D%20'%3CCOMPANY%20domain%3D%22%22%3E%3AENTERPRISEPACK'%20%7B'Office%20365%20Enterprise%20E3'%7D%20'%3CCOMPANY%20domain%3D%22%22%3E%3ASHAREPOINTSTORAGE'%20%7B'SharePoint%20Online%20Storage'%7D%20'%3CCOMPANY%20domain%3D%22%22%3E%3APOWER_BI_STANDARD'%20%7B'Power%20BI%20(free)'%7D%20'%3CCOMPANY%20domain%3D%22%22%3E%3AEMS'%20%7B'Enterprise%20Mobility%20%2B%20Security%20E3'%7D%20'%3CCOMPANY%20domain%3D%22%22%3E%3AMCOMEETADV'%20%7B'Audio%20Conferencing'%7D%20'%3CCOMPANY%20domain%3D%22%22%3E%3ADYN365_ENTERPRISE_SALES'%20%7B'Dynamics%20365%20for%20Sales%20Enterprise%20Edition'%7D%20'%3CCOMPANY%20domain%3D%22%22%3E%3ADYN365_ENTERPRISE_TEAM_MEMBERS'%20%7B'Dynamics%20365%20for%20Team%20Members%20Enterprise%20Edition'%7D%20default%20%7B'Unknown'%7D%20%7D%7D%7D%2C%20%40%7BN%3D'Active%20Units'%3BE%3D%7B%24_.ActiveUnits%7D%7D%2C%20%40%7BN%3D'Consumed%20Units'%3BE%3D%7B%24_.ConsumedUnits%7D%7D%2C%20%40%7BName%20%3D%20'RemainingUnits'%3B%20Expression%20%3D%20%7B%5Bstring%5D(%5Bint%5D(%20%24_.ActiveUnits%20-%20%24_.ConsumedUnits))%7D%7D%2C%20%40%7BN%3D'Account%20SkuId'%3B%20E%3D%7B%24_.AccountSkuId%7D%7D%20%7C%20ConvertTo-Html%3C%2FCOMPANY%3E%3C%2FCOMPANY%3E%3C%2FCOMPANY%3E%3C%2FCOMPANY%3E%3C%2FCOMPANY%3E%3C%2FCOMPANY%3E%3C%2FCOMPANY%3E%3C%2FCOMPANY%3E%3C%2FCOMPANY%3E%3C%2FP%3E%3CP%3ESend-MailMessage%20-SmtpServer%20mail.%3CCOMPANY%20domain%3D%22%22%3E%20-To%20%3CRECIPIENT%3E%40%3CCOMPANY%20domain%3D%22%22%3E%20-From%20%3CSENDER%3E%40%3CCOMPANY%20domain%3D%22%22%3E%20-Subject%20%22Consumed%20licenses%20report%22%20-Body%20%24SkuReport1%20-BodyAsHtml%3C%2FCOMPANY%3E%3C%2FSENDER%3E%3C%2FCOMPANY%3E%3C%2FRECIPIENT%3E%3C%2FCOMPANY%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-182193%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20license%20consumption%20alert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-182193%22%20slang%3D%22en-US%22%3E%3CP%3E%23Set%20the%20license%20variable%3C%2FP%3E%3CP%3E%24License%20%3D%20Get-MsolAccountSku%20%7C%20Where-Object%20%7B%24_.accountskuid%20-eq%20%22%3CYOURACCOUNTSKUID%3E%22%7D%3C%2FYOURACCOUNTSKUID%3E%3C%2FP%3E%3CP%3E%23Calculate%20available%20licenses%3CBR%20%2F%3E%24FreeLicense%20%3D%20%24License.ActiveUnits%20-%20%24License.ConsumedUnits%3C%2FP%3E%3CP%3E%23If%20fewer%20than%26nbsp%3B%3CSOME%20number%3D%22%22%3E%20available%20licenses%2C%20execute%20the%20rest%20of%20the%20script%3CBR%20%2F%3EIf%20(%24FreeLicense%20-lt%20%3CSOME%20number%3D%22%22%3E)%3C%2FSOME%3E%3C%2FSOME%3E%3C%2FP%3E%3CP%3E%7B%3C%2FP%3E%3CP%3E%23Do%20something.%20I%20have%20the%20script%20run%20then%20a%20report%20of%20all%20terminated%20users%20that%20still%20hold%20onto%20a%20license%2C%20based%20on%20license%20info%20in%20Msol%20and%20deactivation%20date%20in%20Active%20Directory%20(our%20termed%20users%20are%20deactivated%20in%20AD%20then%20removed%20after%2030%20days).%20Report%20also%20includes%20the%20day%20that%20the%20license%20will%20be%20freed%20up%20(i.e.%20when%20the%20termed%2Fdeactivated%20account%20will%20be%20fully%20removed).%20Report%20is%20then%20emailed%20to%20O365%20admin%20team.%3C%2FP%3E%3CP%3E%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20only%20problem%20is%20figuring%20out%20how%20to%20truly%20store%20the%20password%20securely%2C%20in%20order%20to%20run%20this%20script%20as%20a%20scheduled%20task.%20Theoretically%2C%20if%20a%20password%20is%20stored%20as%20an%20encrypted%20string%20in%20PowerShell%2C%20couldn't%20anyone%20who%20gains%20access%20to%20the%20server%20on%20which%20the%20script%20runs%20then%20use%20those%20secure%20credentials%20to%20run%20other%20malicious%20scripts%3F%20I'm%20relatively%20new%20to%20PS%20so%20I%20don't%20yet%20understand%20all%20concepts.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-166923%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20license%20consumption%20alert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-166923%22%20slang%3D%22en-US%22%3E%3CP%3EI%C2%B4d%20like%20to%20share%20it%20with%20you%20-%20I%20found%20the%20right%20Cmdlet%3A%20Get-MsolAccountSku%3C%2FP%3E%0A%3CP%3Egives%20you%20the%20overall%20figures%20I%20was%20searching%20for%3A%3C%2FP%3E%0A%3CP%3EAccountSkuId%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ActiveUnits%20WarningUnits%20ConsumedUnits%3CBR%20%2F%3E------------%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20-----------%20------------%20-------------%3CBR%20%2F%3Ep3group%3ASTREAM%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%201000000%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%200%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%2065%3C%2FP%3E%0A%3CP%3Eso%20this%20will%20be%20the%20basis%20for%20our%20alarming%20script%20of%20low%20license%20counts.%20not%20the%20powershell%20folks%20can%20step%20in%20%3A)%3C%2Fimg%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice365%2Fenterprise%2Fpowershell%2Fview-account-license-and-service-details-with-office-365-powershell%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Foffice365%2Fenterprise%2Fpowershell%2Fview-account-license-and-service-details-with-office-365-powershell%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-166715%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20license%20consumption%20alert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-166715%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20not%20sure%20what%20you%20mean%2C%20you%20mentioned%20PowerShell%20alerts%20above%3F%20There%20is%20no%20need%20to%20loop%20over%20each%20user%20in%20order%20to%20%22count%22%20the%20licenses%2C%20the%20Get-MsolAccountSku%20cmdlets%20will%20show%20you%20the%20%22total%22%20and%20%22assigned%22%20numbers.%20If%20you%20want%20to%20get%20those%20from%20the%20portal%2C%20you%20can%20use%20the%20overview%20at%3A%20%3CA%20href%3D%22https%3A%2F%2Fportal.office.com%2Fadminportal%2Fhome%23%2Fsetupproducts%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fportal.office.com%2Fadminportal%2Fhome%23%2Fsetupproducts%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-166632%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20license%20consumption%20alert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-166632%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Vasil%2C%3C%2FP%3E%0A%3CP%3Ethank%20you%20for%20the%20reply.%20The%20parts%20I%20can%20find%20above%20are%20how%20to%20send%20mails%20via%20powershell%20-%20got%20that%2C%20no%20issue.%3C%2FP%3E%0A%3CP%3EI%20want%20to%20go%20straight%20to%20the%20available%20license%20information%20within%20the%20Admin%20Center%20-NOT-%20exporting%20all%20licensed%20users%2C%20counting%20them%2C%20storing%20in%20a%20file%20%2F%20variable%20looping%20through%20E1%20and%20E3%20users%2C%20and%20then%20compare%20it%20against%20the%20SUM%20of%20available%20licenses%20in%20the%20tenant.%3C%2FP%3E%0A%3CP%3EI%20just%20want%20to%20get%20the%20latest%20part%20done%20-%20get%20the%20amount%20of%20purchased%20licenses%20from%20the%20tenant.%20Do%20you%20have%20an%20hint%2C%20how%20to%20get%20this%20information%20direct%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ebest%20regards%3C%2FP%3E%0A%3CP%3EMarkus%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-166592%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20license%20consumption%20alert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-166592%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20takes%20two%20lines%20of%20PowerShell%2C%20and%20you%20have%20all%20the%20building%20blocks%20in%20the%20replies%20above.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-166318%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20license%20consumption%20alert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-166318%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%0A%3CP%3Eeven%20if%20this%20threat%20appears%20to%20be%20stuck%20or%20dead%20-%20I%20would%20be%20very%20interested%20to%20get%20the%20powershell%20code%20for%20getting%20alerts%20on%20low%20license%20counts%20-%20based%20on%20comparision%20of%20purchased%20and%20assigned%20licenses%20directly%20on%20the%20tenant%20level.%3C%2FP%3E%0A%3CP%3EWould%20be%20very%20appreciated.%3C%2FP%3E%0A%3CP%3Ebest%20regards%3C%2FP%3E%0A%3CP%3EMarkus%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-56459%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20license%20consumption%20alert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-56459%22%20slang%3D%22en-US%22%3EMust%20probably%20we%20will%20use%20this%20way%2C%20but%20would%20be%20nice%20if%20microsoft%20provides%20this%20out%20of%20the%20Box.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-56458%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20license%20consumption%20alert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-56458%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20you%20go%20the%20PowerShell%20route%20and%20want%20a%20schedule%20report%20you%20can%20always%20set%20it%20up%20on%20a%20server%20to%20run%20as%20a%20schedule%20task%20on%20a%20recurring%20basis.%20I've%20done%20a%20few%20reporting%20solutions%20this%20way%20to%20achieve%20a%20scheduled%20report%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-56447%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20license%20consumption%20alert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-56447%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Vasil%20much%20appreciated%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-56425%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20license%20consumption%20alert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-56425%22%20slang%3D%22en-US%22%3E%3CP%3EUnfortunately%20no%2C%20only%20few%20reports%20have%20the%20schedule%20functionality%20(the%20EOP%20related%20ones).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-56302%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20license%20consumption%20alert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-56302%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Vasil%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20know%20if%20there%20is%20a%20way%20to%20schedule%20the%20reports%20to%20be%20send%3F%20instead%20of%20going%20every%20day%26nbsp%3Bto%20open%20the%20admin%20portal%20and%20look%20for%20the%26nbsp%3Breport.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-56275%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20license%20consumption%20alert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-56275%22%20slang%3D%22en-US%22%3E%3CP%3ESending%20messages%20via%20PowerShell%20is%20very%20easy.%20Get%20the%20information%20you%20want%20via%20the%20licensing%20cmdlets%2C%20then%20simply%20use%20something%20like%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%24body%20%3D%20%22blabla%20you%20have%20%24somevariable%20available%20licenses%20out%20of%20%24anothervariable%20blabla%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESend-MailMessage%20-SmtpServer%20outlook.office365.com%20-To%20user%40domain.com%20-From%20you%40domain.com%20-Subject%20%22Free%20licenses%20report%22%20-Body%20%24body%20-BodyAsHtml%20-UseSSL%20-Credential%20(Get-Credential)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOf%20course%20if%20you%20aim%20to%20automate%20it%20you%20can%20use%20a%20different%20account%2C%20store%20the%20credentials%20securely%20and%20reuse%20them.%20You%20can%20also%20use%20your%20own%20SMTP%20server%20instead%20of%20O365%20ones%2C%20etc.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-56098%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20license%20consumption%20alert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-56098%22%20slang%3D%22en-US%22%3E%3CP%3EI%20was%20wondering%20if%20someone%20has%20got%20that%20Powershell%20code%20for%20viewing%20license%20and%20setting%20up%20for%20email%20notification.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20how%20to%20view%20license%26nbsp%3B%20(Get-MsolUser%20-UserPrincipalName%20belindan%40litwareinc.com).Licenses.ServiceStatus%20%26nbsp%3B-%20how%20do%20i%20add%20this%20to%20a%20notification%20email.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-56079%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20license%20consumption%20alert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-56079%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20terms%20of%20alerts%2C%20no.%20A%20quick%20glance%20at%20the%20Admin%20portal%20or%20the%20Reports%20can%20give%20you%20this%20info.%20If%20you%20need%20it%20automated%20it%20really%20takes%20few%20lines%20of%20PowerShell%20code%20to%20check%20for%20available%20licenses%20and%20fire%20up%20an%20email%20notification.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-873637%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20license%20consumption%20alert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-873637%22%20slang%3D%22en-US%22%3E%3CP%3EA%20daily%20report%20via%20PowerShell%20is%20nice%2C%20but%20even%20better%20is%20an%20alert%20when%20certain%20numbers%20are%20hit%20based%20on%20previous%20reports!%20E.g.%200%20remaining%20or%201%20remaining.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1262933%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20license%20consumption%20alert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1262933%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F2798%22%20target%3D%22_blank%22%3E%40Carlos%20Gomez%3C%2FA%3E%2C%20this%20is%20probably%20coming%20late.%20Just%20thought%20I%20should%20share%20this%20should%20anyone%20else%20needs%20it.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Finsterswap.wordpress.com%2F2020%2F03%2F29%2Fautomatically-alert-your-team-when-your-tenant-is-running-low-or-out-of-office-365-license%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Finsterswap.wordpress.com%2F2020%2F03%2F29%2Fautomatically-alert-your-team-when-your-tenant-is-running-low-or-out-of-office-365-license%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EDepending%20on%20how%20big%20or%20complicated%20your%20environment%20is%2C%20the%20alert%20script%20may%20require%20a%20little%20modification.%20Our%20need%20was%20just%20as%20simple%20as%20%E2%80%9Ctell%20us%20when%20our%20license%20is%20low%20or%20out%E2%80%9D.%20As%20the%20saying%20goes%2C%20necessity%20is%20the%20mother%20of%20invention.%20Feel%20free%20to%20modify%20the%20script.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1494428%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20license%20consumption%20alert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1494428%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20can%20use%20this%20approach%3A%20%3CA%20href%3D%22https%3A%2F%2Fjanbakker.tech%2Fuse-power-automate-or-logic-apps-to-keep-an-eye-on-your-licenses%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fjanbakker.tech%2Fuse-power-automate-or-logic-apps-to-keep-an-eye-on-your-licenses%2F%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20script%20needed.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1505150%22%20slang%3D%22en-US%22%3ERe%3A%20Office%20365%20license%20consumption%20alert%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1505150%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F2798%22%20target%3D%22_blank%22%3E%40Carlos%20Gomez%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIts%20not%20pretty%20but%20this%20will%20do%20what%20you%20want.%26nbsp%3B%20I%20have%20configured%20it%20for%20SPE_E3%20sku%20but%20you%20can%20change%20it%20for%20which%20ever%20license%20type%20you%20want%20in%20the%20variables.%26nbsp%3B%20it%20will%20send%20an%20email%20when%20there%20are%2010%20licenses%20left%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%24sku%20%3DGet-MsolAccountSku%20%7C%20where%20accountskuid%20-EQ%20%22tennant%3ASPE_E3%22%3CBR%20%2F%3E%24skuactive%3D%24sku%20%7C%20select%20activeunits%3CBR%20%2F%3E%24skuconsumed%3D%24sku%20%7C%20select%20activeunits%3CBR%20%2F%3E%24skuactive%3D%24sku%20%7C%20select%20activeunits%3CBR%20%2F%3E%24skuconsumed%3D%24sku%20%7C%20select%20consumedunits%3CBR%20%2F%3E%24skuconsumed.ConsumedUnits%3CBR%20%2F%3E%24skuconsumed.ConsumedUnits.Equals(%2B10)%3CBR%20%2F%3E%24skuconsumed.ConsumedUnits%20%2B10%3CBR%20%2F%3E%24skuadd10%20%3D%20%24skuconsumed.ConsumedUnits%20%2B10%3CBR%20%2F%3Eif%20(%24skuadd10%20-ge%20%24skuactive.ActiveUnits)%20%7BSend-MailMessage%20-SmtpServer%20smtpserver01%20-To%20geeman%40maildomain.com%20-From%26nbsp%3Bgeeman%40maildomain.com%20-Body%20test%20-Subject%20test%20%7D%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Hi,

 

Does Microsoft provides any alerting on the case that availabe licenses to assign are\near to be 0?

 

I already see that several blogs provide details for creating your own alerting\report for license consumption using powershell, but I'm interested to confirm if Microsoft has something out of the box.

 

Regards.

19 Replies
Highlighted
Best Response confirmed by Carlos Gomez (Contributor)
Solution

In terms of alerts, no. A quick glance at the Admin portal or the Reports can give you this info. If you need it automated it really takes few lines of PowerShell code to check for available licenses and fire up an email notification.

Highlighted

I was wondering if someone has got that Powershell code for viewing license and setting up for email notification. 

 

I know how to view license  (Get-MsolUser -UserPrincipalName belindan@litwareinc.com).Licenses.ServiceStatus  - how do i add this to a notification email.

 

Thanks

Highlighted

Sending messages via PowerShell is very easy. Get the information you want via the licensing cmdlets, then simply use something like:

 

$body = "blabla you have $somevariable available licenses out of $anothervariable blabla"

 

Send-MailMessage -SmtpServer outlook.office365.com -To user@domain.com -From you@domain.com -Subject "Free licenses report" -Body $body -BodyAsHtml -UseSSL -Credential (Get-Credential)

 

Of course if you aim to automate it you can use a different account, store the credentials securely and reuse them. You can also use your own SMTP server instead of O365 ones, etc.

Highlighted

Hi Vasil,

 

Do you know if there is a way to schedule the reports to be send? instead of going every day to open the admin portal and look for the report.

 

Regards. 

Highlighted

Unfortunately no, only few reports have the schedule functionality (the EOP related ones).

Highlighted

Thanks Vasil much appreciated 

Highlighted

If you go the PowerShell route and want a schedule report you can always set it up on a server to run as a schedule task on a recurring basis. I've done a few reporting solutions this way to achieve a scheduled report :)

Highlighted
Must probably we will use this way, but would be nice if microsoft provides this out of the Box.
Highlighted

Hi everyone,

even if this threat appears to be stuck or dead - I would be very interested to get the powershell code for getting alerts on low license counts - based on comparision of purchased and assigned licenses directly on the tenant level.

Would be very appreciated.

best regards

Markus

Highlighted

It takes two lines of PowerShell, and you have all the building blocks in the replies above.

Highlighted

Hi Vasil,

thank you for the reply. The parts I can find above are how to send mails via powershell - got that, no issue.

I want to go straight to the available license information within the Admin Center -NOT- exporting all licensed users, counting them, storing in a file / variable looping through E1 and E3 users, and then compare it against the SUM of available licenses in the tenant.

I just want to get the latest part done - get the amount of purchased licenses from the tenant. Do you have an hint, how to get this information direct?

 

best regards

Markus

Highlighted

I'm not sure what you mean, you mentioned PowerShell alerts above? There is no need to loop over each user in order to "count" the licenses, the Get-MsolAccountSku cmdlets will show you the "total" and "assigned" numbers. If you want to get those from the portal, you can use the overview at: https://portal.office.com/adminportal/home#/setupproducts

Highlighted

I´d like to share it with you - I found the right Cmdlet: Get-MsolAccountSku

gives you the overall figures I was searching for:

AccountSkuId                           ActiveUnits WarningUnits ConsumedUnits
------------                           ----------- ------------ -------------
p3group:STREAM                         1000000     0            65

so this will be the basis for our alarming script of low license counts. not the powershell folks can step in :)

 

https://docs.microsoft.com/en-us/office365/enterprise/powershell/view-account-license-and-service-de...

Highlighted

#Set the license variable

$License = Get-MsolAccountSku | Where-Object {$_.accountskuid -eq "<YourAccountSkuid>"}

#Calculate available licenses
$FreeLicense = $License.ActiveUnits - $License.ConsumedUnits

#If fewer than <some number> available licenses, execute the rest of the script
If ($FreeLicense -lt <some number>)

{

#Do something. I have the script run then a report of all terminated users that still hold onto a license, based on license info in Msol and deactivation date in Active Directory (our termed users are deactivated in AD then removed after 30 days). Report also includes the day that the license will be freed up (i.e. when the termed/deactivated account will be fully removed). Report is then emailed to O365 admin team.

}

 

My only problem is figuring out how to truly store the password securely, in order to run this script as a scheduled task. Theoretically, if a password is stored as an encrypted string in PowerShell, couldn't anyone who gains access to the server on which the script runs then use those secure credentials to run other malicious scripts? I'm relatively new to PS so I don't yet understand all concepts. 

Highlighted

This issue, the lack of an Office 365 license consumption alert, is still unresolved (06/22/2018). 

There is a feedback forum post for the task; Azure AD feedback: Get 'low license count' notification 

Please vote it up if you really want to see the change. 

 

And as others have noted, you can set up a scheduled task to PowerShell an email to your relevant crew who may see going to the portal as an inconvenience. 

I included my script below with <descriptions> of items that need filled in. 

If you need help getting keys and such to encrypt passwords, try Share-gate or KeePass who provide good MS Online compatible encryption tools. 

 

# 
# Send_O365LicensingReport.ps1 
#

$key = (<integer array>); 
$secureString = "<encrypted Password>" 
$PWord = ConvertTo-SecureString $secureString -key $key; 
$User = "<GlobalAdmin>@<company domain or .microsoftonline.com>"; 
$Credential = New-Object –TypeName "System.Management.Automation.PSCredential" –ArgumentList $User, $PWord

#Get the Office365 tenant credentials 
$Office365credentials = Get-Credential $Credential;

Connect-MsolService -Credential $Office365credentials; 
write-host "Connecting to Microsoft SharePoint Online Service ..."

[String]$SkuReport1 = Get-msolAccountSku | Select @{N='Product Name';E={switch ($_.AccountSkuId) { '<company domain>:ENTERPRISEPREMIUM' {'Office 365 Enterprise E5'} '<company domain>:POWER_BI_PRO' {'Power BI Pro'} '<company domain>:ENTERPRISEPACK' {'Office 365 Enterprise E3'} '<company domain>:SHAREPOINTSTORAGE' {'SharePoint Online Storage'} '<company domain>:POWER_BI_STANDARD' {'Power BI (free)'} '<company domain>:EMS' {'Enterprise Mobility + Security E3'} '<company domain>:MCOMEETADV' {'Audio Conferencing'} '<company domain>:DYN365_ENTERPRISE_SALES' {'Dynamics 365 for Sales Enterprise Edition'} '<company domain>:DYN365_ENTERPRISE_TEAM_MEMBERS' {'Dynamics 365 for Team Members Enterprise Edition'} default {'Unknown'} }}}, @{N='Active Units';E={$_.ActiveUnits}}, @{N='Consumed Units';E={$_.ConsumedUnits}}, @{Name = 'RemainingUnits'; Expression = {[string]([int]( $_.ActiveUnits - $_.ConsumedUnits))}}, @{N='Account SkuId'; E={$_.AccountSkuId}} | ConvertTo-Html

Send-MailMessage -SmtpServer mail.<company domain> -To <recipient>@<company domain> -From <sender>@<company domain> -Subject "Consumed licenses report" -Body $SkuReport1 -BodyAsHtml

 

 

Highlighted

A daily report via PowerShell is nice, but even better is an alert when certain numbers are hit based on previous reports! E.g. 0 remaining or 1 remaining.

Highlighted

@Carlos Gomez, this is probably coming late. Just thought I should share this should anyone else needs it. https://insterswap.wordpress.com/2020/03/29/automatically-alert-your-team-when-your-tenant-is-runnin...

Depending on how big or complicated your environment is, the alert script may require a little modification. Our need was just as simple as “tell us when our license is low or out”. As the saying goes, necessity is the mother of invention. Feel free to modify the script.

Highlighted
Highlighted

@Carlos Gomez 

 

Its not pretty but this will do what you want.  I have configured it for SPE_E3 sku but you can change it for which ever license type you want in the variables.  it will send an email when there are 10 licenses left.  Keep in mind if you use 10 licenses in between the script run cycles you might get notified too late. 

 

$sku =Get-MsolAccountSku | where accountskuid -EQ "tennant:SPE_E3"
$skuactive=$sku | select activeunits
$skuconsumed=$sku | select consumedunits
$skuadd10 = $skuconsumed.ConsumedUnits +10
if ($skuadd10 -ge $skuactive.ActiveUnits) {Send-MailMessage -SmtpServer smtpserver01 -To geeman@maildomain.com -From geeman@maildomain.com -Body test -Subject test }