Wednesday, April 22, 2015

SCCM MIF File Generator

This VBS will provide you with a mean to generate the NOIDMIF.mif files for SCCM clients based on a pre-populated Excel sheet. It has been shared in the Microsoft Community Gallery and it can be downloaded here.

Output MIF File will be similar to the screenshot below
Pre-work
1) The excelsheet should be populated base don the format below with the Values in Column A to C based on your own information of Ship Date (Aka Purchase Date) and Known Hardware Warranty Date.
The Excel should be named as ServersInventory.xlsx for servers and computersinventory.xlsx for clients.
 
2) The file should be executed on a machine with Excel installed
3) Modify the struser and strpwd in the vbs to the necessary credentials to enable the MIF files generated to be copied to the target mahcine
How to use
1) Save the excel files and the downloaded vbs file in the same folder
2) Execute the vbs
3) Enter 1 if you populating for servers and 2 if its for workstations/notebooks
4) Enter the OU that you wish to target against
Outcome
1) A scan will be done on the the specified OU against the information available in the excel sheet.
2) MIF files will be generated and copied for matches found and the machine name will be populated in the excel. (Screenshot as below)



Thursday, March 26, 2015

SSRS Reporting Structure Mapping

Documenting the structure of the SSRS reporting is often.
The below would enable you to be provide with the output in the sample screenshot with much ease.



--QUERY 1
USE ReportServer
GO

SELECT 
CASE WHEN C.Name = '' THEN 'Home' ELSE C.Name END AS ItemName, 
C.Description as Report_Description,
LEN(C.Path) - LEN(REPLACE(C.Path, '/', '')) AS ItemLevel, 
CASE 
WHEN C.type = 1 THEN '1-Folder' 
WHEN C.type = 2 THEN '2-Report' 
WHEN C.type = 3 THEN '3-File' 
WHEN C.type = 4 THEN '4-Linked Report' 
WHEN C.type = 5 THEN '5-Datasource' 
WHEN C.type = 6 THEN '6-Model' 
WHEN C.type = 7 Then '7-ReportPart'
WHEN C.type = 8 Then '8-Shared Dataset'
ELSE '9-Unknown' END AS ItemType, 
CASE WHEN C.Path = '' THEN 'Home' ELSE C.Path END AS Path, 
ISNULL(CASE WHEN CP.Name = '' THEN 'Home' ELSE CP.Name END, 'Home') AS ParentName, 
ISNULL(LEN(CP.Path) - LEN(REPLACE(CP.Path, '/', '')), 0) AS ParentLevel,
ISNULL(CASE WHEN CP.Path = '' THEN ' Home' ELSE CP.Path END, ' Home') AS ParentPath 
FROM 
dbo.Catalog AS CP 
RIGHT OUTER JOIN
dbo.Catalog AS C ON CP.ItemID = C.ParentID


The below will provide a much more detailed report level including who created and modified the report, when was the last exection  etc

USE ReportServer
GO

SELECT
CAT_PARENT.Name AS ParentName,
CAT.Name AS ReportName,
ReportCreatedByUsers.UserName AS ReportCreatedByUserName,
CAT.CreationDate AS ReportCreationDate,
ReportModifiedByUsers.UserName AS ReportModifiedByUserName,
CAT.ModifiedDate AS ReportModifiedDate,
CountExecution.CountStart AS ReportExecuteCount,
EL.InstanceName AS LastExecutedServerName,
EL.UserName AS LastExecutedbyUserName,
EL.TimeStart AS LastExecutedTimeStart,
EL.TimeEnd AS LastExecutedTimeEnd,
EL.Status AS LastExecutedStatus,
EL.ByteCount AS LastExecutedByteCount,
EL.[RowCount] AS LastExecutedRowCount,
SubscriptionOwner.UserName AS SubscriptionOwnerUserName,
SubscriptionModifiedByUsers.UserName AS SubscriptionModifiedByUserName,
SUB.ModifiedDate AS SubscriptionModifiedDate,
SUB.Description AS SubscriptionDescription,
SUB.LastStatus AS SubscriptionLastStatus,
SUB.LastRunTime AS SubscriptionLastRunTime
FROM
dbo.Catalog CAT
INNER JOIN
dbo.Catalog CAT_PARENT
ON 
CAT.ParentID = CAT_PARENT.ItemID
INNER JOIN
dbo.Users ReportCreatedByUsers
ON 
CAT.CreatedByID = ReportCreatedByUsers.UserID
INNER JOIN
dbo.Users ReportModifiedByUsers
ON 
CAT.ModifiedByID = ReportModifiedByUsers.UserID
LEFT OUTER JOIN
(
SELECT
ReportID,
MAX(TimeStart) LastTimeStart
FROM
dbo.ExecutionLog
GROUP BY
ReportID
) AS LatestExecution
ON 
CAT.ItemID = LatestExecution.ReportID
LEFT OUTER JOIN
(
SELECT
ReportID,
COUNT(TimeStart) CountStart
FROM
dbo.ExecutionLog
GROUP BY
ReportID
) AS CountExecution
ON 
CAT.ItemID = CountExecution.ReportID
LEFT OUTER JOIN
dbo.ExecutionLog AS EL
ON 
LatestExecution.ReportID = EL.ReportID
AND 
LatestExecution.LastTimeStart = EL.TimeStart
LEFT OUTER JOIN
dbo.Subscriptions SUB
ON 
CAT.ItemID = SUB.Report_OID
LEFT OUTER JOIN
dbo.Users SubscriptionOwner
ON 
SUB.OwnerID = SubscriptionOwner.UserID
LEFT OUTER JOIN
dbo.Users SubscriptionModifiedByUsers
ON
SUB.ModifiedByID = SubscriptionModifiedByUsers.UserID
ORDER BY
CAT_PARENT.Name,
CAT.Name

Wednesday, March 25, 2015

Retrieve monitors and rules on SCOM Agent

For SCOM admins, there is always a need to know what a servers has been monitored for.
There is a powershell that is provided by 2 MVPs previously (Stefan Stranger and Jeremy Pavleck) which serves this purpose.

To use run from OpsMgr Command Shell:
Get-OpsMgrWorkflows_v1.ps1 -agentname "myagent.contoso.com" | export-csv -path c:\temp\workflows.csv

Save the below as Get-OpsMgrWorkflows_v1.ps1
        
#param ([string]$agentname = $(read-host "Please enter OpsMgr Agent Name"))             
            
function Get-AgentWorkflow($agentname)            
{            
 #Original Script from Jeremy Pavleck.            
 #http://www.pavleck.net/2008/06/sp1-gem-finding-rules-running-on-remote-agents/            
 #Use the OpsMgr Task Show Running Rules and Monitors.            
 $taskobj = Get-Task | Where-Object {$_.Name -eq "Microsoft.SystemCenter.GetAllRunningWorkflows"}            
             
 # Grab HealthService class object            
 $hsobj = Get-MonitoringClass -name "Microsoft.SystemCenter.HealthService"            
 # Find HealthService object defined for named server            
 $monobj = Get-MonitoringObject -MonitoringClass $hsobj | Where-Object {$_.DisplayName -match $agentname}            
             
 #Start Task GetAllRunningWorkflows            
 $taskOut = Start-Task -Task $taskobj -TargetMonitoringObject $monobj            
 [xml]$taskXML = $taskOut.OutPut             
             
 #Get Workflows            
 $workflows=$taskXML.selectnodes("/DataItem/Details/Instance/Workflow")            
             
 #Retrieve Monitors            
 $monitors = get-monitor            
             
 #Retrieve Rules            
 $rules = get-rule            
             
 #Retrieve Discoveries"            
 #Used the Group-object because there are some discovery rules with the same DisplayName            
 $discoveries = get-discovery | select-object -Unique            
             
 #Get Overrides"            
 #monitoroverrides = foreach ($monitor in Get-ManagementPack | get-override | where {$_.monitor}) {get-monitor | where {$_.Id -eq $monitor.monitor.id}}            
 #$rulesoverrides = foreach ($rule in Get-ManagementPack | get-override | where {$_.rule}) {get-rule | where {$_.Id -eq $rule.rule.id}}            
 #$discoveryoverrides = foreach ($discovery in Get-ManagementPack | get-override | where {$_.discovery}) {get-discovery | where {$_.Id -eq $discovery.discovery.id}}            
            
            
 #Check for each workflow if it's a Rule or Monitor or Discovery.            
 foreach ($workflow in $workflows)            
 {            
  #Check for Monitor            
  $monitor = $monitors | where-object {$_.Name -eq $workflow."#text"}            
              
  if ($monitor -eq $null)            
  {            
   #Check for Rule            
   $rule = $rules | where-object {$_.Name -eq $workflow."#text"}            
   if ($rule -eq $null)            
   {             
    #Check for Discovery            
    $discovery = $discoveries | where-object {$_.Name -eq $workflow."#text"}            
    if ($discovery -eq $null)            
    {            
                
    }            
    else            
    {            
     #Get ManagementPack            
     $mp = $discovery.getmanagementpack()            
     #Check if Discovery has an override            
     #$flag = $discoveryoverrides | Where-Object {$_.DisplayName -eq $discovery.DisplayName}            
     #if ($flag -eq $null)            
     #{            
     # $override = "false"            
     #}            
     #else            
     #{            
     # $override = "true"            
     #}            
     $discobject = new-object System.Management.Automation.PSObject            
     $discobject = $discobject | add-member -membertype NoteProperty -name Type -value "Discovery" -passthru            
     $discobject = $discobject | add-member -membertype NoteProperty -name DisplayName -value $discovery.DisplayName -passthru            
     $discobject = $discobject | add-member -membertype NoteProperty -name Description -value $discovery.Description -passthru            
     #$discobject = $discobject | add-member -membertype NoteProperty -name Override -value $override -passthru            
     $discobject = $discobject | add-member -membertype NoteProperty -name ManagementPack -value $mp.DisplayName -passthru            
     $discobject            
    }            
   }            
   else            
   {            
    $mp = $rule.getmanagementpack()            
    #Check if Rule has an override            
    #$flag = $ruleoverrides | Where-Object {$_.DisplayName -eq $rule.DisplayName}            
    #if ($flag -eq $null)            
    #{            
    # $override = "false"            
    #}            
    #else            
    #{            
    # $override = "true"            
    #}            
    $ruleobject = new-object System.Management.Automation.PSObject            
    $ruleobject = $ruleobject | add-member -membertype NoteProperty -name Type -value "Rule" -passthru            
    $ruleobject = $ruleobject | add-member -membertype NoteProperty -name DisplayName -value $rule.DisplayName -passthru            
    $ruleobject = $ruleobject | add-member -membertype NoteProperty -name Description -value $rule.Description -passthru            
    #$ruleobject = $ruleobject | add-member -membertype NoteProperty -name Override -value $override -passthru            
    $ruleobject = $ruleobject | add-member -membertype NoteProperty -name ManagementPack -value $mp.DisplayName -passthru            
    $ruleobject            
   }            
  }            
  else            
  {            
   #Get ManagementPack for Monitor            
   $mp = $monitor.getmanagementpack()            
   #Check if Monitor has an override            
   #$flag = $monitoroverrides | Where-Object {$_.DisplayName -eq $monitor.DisplayName}            
   #if ($flag -eq $null)            
   #{            
   # $override = "false"            
   #}            
   #else            
   #{            
   # $override = "true"            
   #}            
   $monitorobject = new-object System.Management.Automation.PSObject            
   $monitorobject = $monitorobject | add-member -membertype NoteProperty -name Type -value "Monitor" -passthru            
   $monitorobject = $monitorobject | add-member -membertype NoteProperty -name DisplayName -value $monitor.DisplayName -passthru            
   $monitorobject = $monitorobject | add-member -membertype NoteProperty -name Description -value $monitor.Description -passthru            
   #$monitorobject = $monitorobject | add-member -membertype NoteProperty -name Override -value $override -passthru            
   $monitorobject = $monitorobject | add-member -membertype NoteProperty -name ManagementPack -value $mp.DisplayName -passthru            
   $monitorobject            
  }            
 }            
            
            
}            
            
Get-AgentWorkflow $agentname