Automated creation of service requests

Ryan_Kelley

Hello All!

We have a procedurally generated power BI report that contains a list of vulnerabilities(out of date software ect) per machine.

For our analysts we create tickets 1 per vulnerabilities so if 100 machines have out of date (adobe) for example it will create 100 tickets to patch them(we try to automate using sccm ect)

My question: Is there a way(preferably using power BI) but if not then exporting a report to excel and then using PowerShell(or I see a beta web-hooks available) to generate tickets from a list?

IE take this +100 other entries(The powershell script would take the info from an excel sheet in the same folder):

The result would be:

Thank you all for any help! Hoping automated ticket generation is a popular request or there is way to do so in power BI !

Simon_Zeinhofer

Hello @Ryan_Kelley ,

fortunately powershell provides a really simple way to import and process a .csv file.

Here is a short code snippet. I am not a powershell pro myself but it works really well and I use it in many scripts which we use in orchestrator runbooks:

$csvPath = 'the path to your csv file'


#if the delimiter is a "," and you have header rows, you can just use this command
$csv = import-csv -Path $csvPath


#if the delimiter is not the standard "," but e.g. a ";" ,you have to use this command
$csv = import-csv -Path $csvPath -Delimiter ";"


#if you do not have header rows you need to define the column names, else the first row will be treated as header
$csv = Import-Csv -Path $csvPath -Header "Column1,Column2,Column3"


#ofc you can combine the -header and -delimiter switch.


#after importing the csv, you can just iterate through it
foreach($line in $csv)
{


    #whenever I do this I declare my columns as variables to process them further, but that's optional
    $value01 = $line.Column1
    $value02 = $line.Column02
    #.
    #.
    #.
    #.
    #and so on...now you can process the columns from each row
    #.
    #.
}

For the creation of work items with certain values, I could provide you a sample script, BUT I would suggest you dig your way through @Adam_Dzyacky 's Powershell course. When I started all I could was split a string in powershell, but after the course (and a lot of times failing hard) I wrote my own transition scripts from one SCSM environment to another and use scripts in SCO instead of OOTB activities most of the time -Learning by doing :)

https://cireson.com/blog/definitive-service-manager-powershell-8-commands/

There are 7 parts ;)

Brian_Wiest

What is the source of the Power BI report?

Ryan_Kelley

The sources of the power BI report are service management and service manager (we used both depending on the objective)

Brian_Wiest

So then the source is a SQL database, use the same rules in for PowerBi and build a SCORCH runbook to create matching work items.

Ryan_Kelley

I was thinking that but - apologies for not mentioning- Some of the sources are also being tied in from rapid7,ATP, CISA , ect - as we wanted a very comprehensive report.

Hence the question on how to create tickets in scsm using the power bi report or failing that exporting what we want and then running a script to generate tickets

Power BI -> export list to excel -> run script -> generate SRs for analysts - Unless Orchestration Console can do that?

Brian_Wiest

We also have outside systems that provide reporting that I cannot hit directly, aka receiving reporting with csv's. So to address that have power automate capturing the files and moving them into a custom SQL database. Then combining the OOB databases and these custom databases we run rules that create 100's of tickets each month of "issue assets"

Since Power Automate can "Talk" with Power Bi you might be able to have Automate perform the export job as a flat file, copy to a local file share, then call SCORCH RestAPI to kick off a runbook to process that export file.

HTH

Adam_Dzyacky

Historically my advice would have been any one of the following (obviously varies based on specific use case):

• Export to a CSV, PowerShell loop through to create Work Items & and their relationships (semi-manual extracts, but fairly straightforward all in all)
• Use the SCCM management pack for SCOM to Alert on failures to generate an SCSM Incident (somewhat technically involved, but possible)
• Use the SCSM/SCCM Desired State Configuration Workflow within SCSM for SCCM items that fall out of their Baseline (may not apply to every scenario)
• Integrate those systems with PowerShell and then report off of SCSM (ideal, but also technically involved)

HOWEVER!

Per our Innovate 2022 announcement yesterday - with the upcoming release of our Power Automate Connector for SMP, I can't see why you now couldn't potentially use Power Automate's Power BI connector to extract data and then drive the creation of Incidents/Service Requests entirely from Power Automate.

Ryan_Kelley

Hello,

I attended the Innovate 2022 announcement! :)

I am very excited to use power automate -> however -> Is there an expected release date?

Thank you for replying!

Brett_Moffett

Hi @Ryan_Kelley

The Cloud Connector is available right now and the Cloud Activity is in testing at the moment so should be released shortly.

Brad_Zima

@Ryan_Kelley the Power Automate connector is currently awaiting final MS approval but if you contact @Adam_Dzyacky he can set you up with a copy you can side-load into your Power Automate environment.

Ryan_Kelley

@Brad_Zima - I sent @Adam_Dzyacky a message asking for a copy: https://community.cireson.com/messages/388

Thank you for helping! :)

Ryan_Kelley

Hello All!

I wanted to circle back around. We have found through testing we would prefer to: Export to a CSV, PowerShell loop through to create Work Items & and their relationships.

The reason is because we do not want it fully automated where the SRs are created for us without prior approval.

Does anyone have an example powershell code that we can use that would generate SRs based on the columns of a CSV file?

Simon_Zeinhofer

Hello @Ryan_Kelley ,

fortunately powershell provides a really simple way to import and process a .csv file.

Here is a short code snippet. I am not a powershell pro myself but it works really well and I use it in many scripts which we use in orchestrator runbooks:

$csvPath = 'the path to your csv file'


#if the delimiter is a "," and you have header rows, you can just use this command
$csv = import-csv -Path $csvPath


#if the delimiter is not the standard "," but e.g. a ";" ,you have to use this command
$csv = import-csv -Path $csvPath -Delimiter ";"


#if you do not have header rows you need to define the column names, else the first row will be treated as header
$csv = Import-Csv -Path $csvPath -Header "Column1,Column2,Column3"


#ofc you can combine the -header and -delimiter switch.


#after importing the csv, you can just iterate through it
foreach($line in $csv)
{


    #whenever I do this I declare my columns as variables to process them further, but that's optional
    $value01 = $line.Column1
    $value02 = $line.Column02
    #.
    #.
    #.
    #.
    #and so on...now you can process the columns from each row
    #.
    #.
}

For the creation of work items with certain values, I could provide you a sample script, BUT I would suggest you dig your way through @Adam_Dzyacky 's Powershell course. When I started all I could was split a string in powershell, but after the course (and a lot of times failing hard) I wrote my own transition scripts from one SCSM environment to another and use scripts in SCO instead of OOTB activities most of the time -Learning by doing :)

https://cireson.com/blog/definitive-service-manager-powershell-8-commands/

There are 7 parts ;)

Ryan_Kelley

Thank you! This answered my question! :)