Splunk to Sentinel Migration – Part V – Reports and Dashboards

Now that we have the basics in place, its time to approach some of the harder topics that one would run into during a migration. The first difficult thing falls into the category of reports and dashboards.

This is where things become very much like migrating a non-SharePoint CMS to Sharepoint (which I have done many to many instances of with a high performance tool I created called PowerStream).

Splunk Dashboards are very much like web part pages in SharePoint and ASP.NET. They are made into various sections and placed into an XML/HTML file. On the other side of the equation, you have Workbooks in Azure Sentinel / Log Analytics / Azure Monitor. They are also similar to web part pages with sections.

Sections tend to have variables and queries tied to them and then some kind of context that tells the section how to render. This exists on both sides so a mapping has to be done between source and target.

Splunk Sections

In Splunk, you have the following sections and types:

When searching for these sections, you will look at each of the xml files that are exported for each dashboard. Inside these xml files you will find a s:key element with the name value of “eai:data”. This contains the actual form that will be displayed as part of the dashboard to users. Inside this form are rows and panels:

Inside these panels will be the sections that are of most importance. Things like HTML text and the search queries will be lurking:

As you can see above, the search query is embedded along with the filters and the options that need to be passed to it. These items need to be extracted and then converted to their Azure equivalents.

Sentinel Sections

In order to do the mapping to Azure you will need to understand how to create the workbooks and then upload them properly so they display in Azure Sentinel. In Azure workbooks, they are defined by JSON and have up to 11 different types of “items”. Here are some examples:

  1. Type 1: KqlItem- HTML\Text
  2. Type 3: KqlItem – Query
  3. Type 9: KqlParameterItem – Parameter
  4. Type 10: MetricsItem – Metric from Log Analytics
  5. Type 11: LinkItem – A Url/Link

Again, as part of the migration, you must take the extracted items from the Splunk dashboard and convert to the Workbook version. Once you have this figured out, you now need to upload the workbook.

Uploading Workbooks

Once you have a converted workbook, its time to upload it to Azure Sentinel. Because Sentinel simply builds off a Log Analytics workspace, most things (but definitely not all) are stored in that workspace. To upload, you will make a post to the following rest endpoint:

$url = "/subscriptions/$subscriptionId/resourcegroups/$resourceGroupName/providers/microsoft.operationalinsights/workspaces/$workspaceName"

The post body has some very specific items that must be set in order for the workbook to show up in Sentinel. As it took me some time to determine these parameters, I’ll leave it to use your favorite tool Fiddler to get to the same point 🙂

In the next post, we will explore how permissions work (well they kinda work, as I mentioned in earlier posts its the weakest part of Sentinel at the moment).

Splunk to Sentinel Blog Series

  1. Part I – SPL to KQL, Exporting Objects
  2. Part II – Alerts and Alert Actions
  3. Part III – Lookups, Source Types and Indexes
  4. Part IV – Searches
  5. Part V – Dashboards and Reports
  6. Part VI – Users and Permissions
  7. Part VII – Apps

References:

  1. My Twitter
  2. My LinkedIn
  3. My Email

Splunk to Sentinel Migration – Part IV – Searches

In Splunk, searches are saved queries. These queries can be used in any number of places, or simply just run when needed. Queries are built in the Splunk Programming Language (SPL).

In Part I of this series we discusses exporting the objects in the Splunk instance. Search queries you will discover are at the center of everything.

We have already discussed Alerts and how they use queries, but as we will see in the next post in this series other things like Dashboards and Reports also use them.

Searches

As we discusses in Part I of this series, we have to export the objects in Splunk as the first step. One of the things you will realize is that “Searches” is an overloaded term. In order to get dashboards and reports out, you have to hit the “searches” REST endpoint.

$url = "$apiUrl/servicesNS/-/search/saved/searches..."

When these object are exported, there are a few properties you will need to interrogate to determine what the search was created for:

  1. Report = {entry}.content.is_scheduled
  2. Dashboard = {entry}.content.isdashboard

We will explore these instances in the next series as they have other higher level components that must be considered, for the purposes of this blog, we simply want to discuss where are the possible places we can “save” these queries in Azure Sentinel.

  1. Sentinel Hunting Queries
  1. Sentinel Analytics (Scheduled Rule)
  1. Log Analytics (Saved Query)

Each of the items above have a different endpoint to create them:

#Hunting Query (Sentinel)
$url = "https://management.azure.com/subscriptions/$subscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.OperationalInsights/workspaces/$workspaceName/savedSearches/$($id)?api-version=2017-04-26-preview"

#Scheduled Rule (Sentinel)
$url = "https://management.azure.com/subscriptions/$subscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.OperationalInsights/workspaces/$workspaceName/providers/Microsoft.SecurityInsights/alertRules/$($ruleId)?api-version=2021-03-01-preview";

#Saved Query (Log Analytics)
$url = "https://management.azure.com/subscriptions/$subscriptionId/resourceGroups/$resourceGroupName/providers/microsoft.insights/scheduledqueryrules/$($name)?api-version=2018-04-16";

Each of these endpoints require a POST with very specific properties to tell Sentinel or Log Analytics what to create and what it is for. For example, Azure Sentinel Hunting queries can be targeted at MITRE Framework Tactics, so you’d include those, however, Splunk has no concept of MITRE that you can pull from so you’d have to add that from a reference file.

As was discussed in the first post in this blog series, the above items are the easy part, its the tokenization of the Splunk query and then conversion to KQL that is the hard part.

Splunk to Sentinel Blog Series

  1. Part I – SPL to KQL, Exporting Objects
  2. Part II – Alerts and Alert Actions
  3. Part III – Lookups, Source Types and Indexes
  4. Part IV – Searches
  5. Part V – Dashboards and Reports
  6. Part VI – Users and Permissions
  7. Part VII – Apps

References:

  1. My Twitter
  2. My LinkedIn
  3. My Email

Splunk to Sentinel Migration – Part III – Lookups, Source Types and Indexes

In the previous two blog series articles, we explored exporting objects, alerts and alert actions. We also looked at the concept of converting SPL to KQL. As part of that conversion process their are some special things that will need to be present in order for the queries to run properly on either the Splunk or the Azure Sentinel side.

These items include lookups, source types and indexes.

Lookups (Splunk)

Lookups are an important part of any SIEM/SOAR system. Typically these make up special lists of users, or more commonly Indicators of Compromise such as IP Addresses, Domain Names, etc.

When it comes to Splunk, you can do lookups in a number of different ways. This includes a file upload with can be either a plaintext CSV file, a gzipped CSV file, or a KMZ/KML file.
The maximum file size that can be uploaded through the browser is 500MB.

You can also do script/command based lookups:

So how do lookups translate into Azure Sentinel? There are several ways:

  1. Log Analytics Table
  2. Watchlists
  3. Azure Storage

Picking between the three options depend on the size and use of the actual lookup table. Note, as we will see in a later post, Sentinel doesn’t have a very robust permissions scheme (yet), so they is something to consider as well. In my opinion, this is probably the largest factor when considering moving to Azure Sentinel from another SIEM.

Log Analytics Table

You can use the Data Collector API to bring in Lookups into Azure Sentinel / Log Analytics.

https://docs.microsoft.com/en-us/azure/azure-monitor/logs/data-collector-api

Once imported, they would then show up like this:

Watchlists

Watchlists are the almost identical mapping of a Lookup in Splunk. As you can see here, I imported the exported CSV file programmatically from Splunk into a Watchlist in Azure Sentinel:

Azure Storage

Kusto Query Language (KQL) allows for the runtime creation of a table based on an external file. In this case, we can export the CSV files from Splunk into Azure Storage and then use KQL to bring it into the queries:

https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/externaldata-operator?pivots=azuredataexplorer

Source Types

In addition to lookup data, a vital component is you have to have log data to run your queries on. This log data comes in all kinds of shapes and formats. There are about 150 out of the box Source Types in Splunk.

Source types defined how data is formatted such that the indexers can index it. It is very much an ingest time type of thing.

Indexes

You can consider these like tables in a database. They have a name, rows and columns with type. Splunk stores the data from logs in an “index”. This data is ingested using the source types mentioned above.

In order to determine if a query is going to run, you have to gain access to all the indexes, then export the schema of those indexes. This of course would all go into the tokenizer / compiler and then save the graph of the query for comparison to the “tables” in Azure Sentinel.

As you can imagine, it is not going to be a one to one mapping on the other side. Some things are pretty common, others, not so much.

As we will see in Part VII, Apps have the ability to import new source types, which then in turn create new indexes. As we will see, Apps hold some similarity to “Connectors” in Azure Sentinel.

Ultimately, the goal is to ensure that we have the same Source Type -> Data Source mappings such that the queries are converted properly. In many cases, you’ll need to create a mapping file of the properties on the left side of the equation with the properties on the right side of the migration equation.

Splunk to Sentinel Blog Series

  1. Part I – SPL to KQL, Exporting Objects
  2. Part II – Alerts and Alert Actions
  3. Part III – Lookups, Source Types and Indexes
  4. Part IV – Searches
  5. Part V – Dashboards and Reports
  6. Part VI – Users and Permissions
  7. Part VII – Apps

References:

  1. My Twitter
  2. My LinkedIn
  3. My Email

Splunk to Sentinel Migration – Part II – Alerts and Alert Actions

Continuing where we left off in Part I, I will explore how to convert Splunk Alerts and Alert Actions into the Sentinel equivalents.

Alerts

Alerts have the same core problem as everything else in Splunk. They are built on SPL queries and must be converted in order for anything meaningful to be accomplished. This means we have to export the alert object, grab the query, convert it, and finally check that it actually executed in Azure Sentinel and then perform the migration.

Alert Actions

Once you have the alert query converted, the Alert very likely has an action tied to it. Splunk provides several out of the box actions:

Migrating these can be a daunting task if you are doing it manually. Not something I’d suggest trying to do in a large Splunk instance. The first step is to export the alert actions into their JSON form:

JSON Export

And then interrogate the various properties for the Alert Action.

Now that you know how to convert the Alert Actions, you need to find all the alerts that are using the actions so you can create them on the Azure side.

Alerts

Alerts are queries that when the query hits a set of targets, will execute one or more Alert Actions. The first step to migrating these is to export them.

Reviewing the exported JSON object, you will find several interesting properties.

The “QualifiedSearch” and “Search” property is the query that the alert is based off of.

This “Actions” property will tell you what actions have been activated for that alert query.

Once you have the list of activated actions, you have to figure out what information you will need to create the corresponding item in Azure Sentinel or Log Analytics.

In the example above, the logevent action has been enabled for the alert. Review the remainder of the properties, you will find a serialization technique Action.{ActionName}.{PropertyName}.{SubPropertyName0}….

Once you get the hang of it, you can then take the exported data and start to build out the migration tasks that need to occur for each Alert Action Type. Here is a basic mapping based on the items above:

  1. Email -> Action Group
  2. LogEvent -> Log to Log Analytics
  3. Lookup -> Insert into Lookup (more to come on lookup migration later in this series)
  4. SMS -> Action Group
  5. Webhook -> Action Group
  6. Script -> Runbook
  7. Custom / App -> Logic App / Runbook

Everything above can be accomplished using Azure Management REST queries (I know as I have done the mappings successfully). Some of these have corresponding Azure CLI or PowerShell commands, however some of them do not. So the best approach is to implement everything using simple REST calls.

Splunk to Sentinel Blog Series

  1. Part I – SPL to KQL, Exporting Objects
  2. Part II – Alerts and Alert Actions
  3. Part III – Lookups, Source Types and Indexes
  4. Part IV – Searches
  5. Part V – Dashboards and Reports
  6. Part VI – Users and Permissions
  7. Part VII – Apps

References:

  1. My twitter
  2. My LinkedIn
  3. My Email

Splunk to Sentinel Migration – Part I

I have been a busy bee the last few years. Solliance has kept me focused on a myriad of projects and clients using various technologies such as Machine Learning, AI, Security, IoT and much more.

Recently, I have been focused on building internal training for a whole new breed of security professionals inside Microsoft called Security Cloud Solution Architects (CSAs). As part of their training, they must learn about Azure Defender, Microsoft Defender, Azure Sentinel etc.

One of the topics I added to the training focuses on moving customers to Azure Sentinel. Which means, you would need to retire your old SIEM/SOAR system in lieu of the new one. Some folks would say that this is a easy transition, however, if you have been tasked with a migration project of this kind, you know better.

At a high level, most SIEMs have the same myriad of concepts such as Alerts, Alert actions, Search Queries (along with the tools SIEM language), Dashboard, etc. Each of these present a different set of challenges when migrating them to another platform such as Azure Sentinel. In the following blogs series, I will attempt to run through how I have solved some of these challenges and how I have automated the process.

First, let’s talk about the artifacts that exist in Splunk:

  1. Alerts
  2. Alert Actions
  3. Source Types
  4. Searches
  5. Dashboard
  6. Reports
  7. Namespaces
  8. Permissions

Splunk Query Language to Kusto Query Language

Splunk customers put a lot of work into adding log sources based on source types and then building queries from the data that is ingested and indexed from them. Once they have built the queries, these queries then tend to be used a multiple places such as Alerts, Dashboards and Reports. Splunk uses the Splunk Processing Language (SPL). This is vastly different from the Azure Sentinel Kusto Query Language (KQL). Because it is not easy to map 1:1 from Splunk to Sentinel as it requires you to understand the SPL syntax and the corresponding KQL syntax. For example, take the following query in SPL:

| rest splunk_server=$splunk_server$ /services/server/info
| fields splunk_server version
| join type=outer splunk_server [rest splunk_server=$splunk_server$ /services/server/status/installed-file-integrity
    | fields splunk_server check_ready check_failures.fail]
| eval check_status = case(isnull('check_failures.fail') AND isnotnull(check_ready), "enabled", 'check_failures.fail' == "check_disabled", "disabled", isnull(check_ready), "feature unavailable")
| eval check_ready = if(check_status == "enabled", check_ready, "N/A")
| fields version check_status check_ready
| rename version AS "Splunk version" check_status AS "Check status" check_ready AS "Results ready?"

Would become something like this in KQL:

let table0 = externaldata (data:string) [@"/services/server/status/installed-file-integrity"]| project splunk_server,check_ready,check_failures.fail;
externaldata (data:string) [@"/services/server/info"]| project splunk_server,version
| join kind = outer (table0) on splunk_server
| extend check_status  = case (isnull(check_failures.fail) and isnotnull(check_ready),"enabled",'check_failures.fail' == "check_disabled","disabled",isnull(check_ready),"feature unavailable")
| extend check_ready  = iff (check_status == "enabled",check_ready,"N/A")
| project-rename version = "Splunk version",check_status = "Check status",check_ready = "Results ready?"

If you thought manually transforming queries was a challenge, think through the complexities of automating the process (which the above was done through). It requires writing a tokenizer, then creating a two-pass compiler to build out the language graph. From there, I have to be able to transform the graph of SPL to a graph of KQL, then spit out the KQL query text from the final converted graph. Can anyone say “Dragon Book”?!? I knew you could!

There are some quirks to all this however, I have to be able to know what are strings, variables, table and/or column names…which requires me to interrogate the Splunk indexes for that information. Once I have all the metadata in hand, err memory, I can more easily determine what each thing is in the query when building the graph and subsequent KQL queries.

Some may find this tasks daunting, however, if you do a search you may find a tool called https://uncoder.io. Although it shows great promise when you load the site, it doesn’t really work (broken most of the time) and thusly, I had to build my own as you can see from above.

Apps and Source Types – Oh My.

But…indexes are based on the Source Types that have been added in Splunk. Splunk provides several out of the box source types, but new ones can be added from Apps. Think things like F5, CheckPoint, etc. These source types provide log data that is then indexed and available for querying. Apps have to be mapped from the Splunk based ones to the Azure Sentinel ones, ouch. The indexes created from Splunk Apps have to be transformed to the similar table and column structure in the Azure Sentinel side.

Once you solve these problems, you can easily automate the query transformations from SPL to KQL.

Exporting Artifacts

In order to automate the entire process, you have to export all the objects in the Splunk instance. This can be done using the REST APIs provided by Splunk. It has a very object oriented way of storing these objects which are then serialized to JSON in the REST responses. This provides a very convenient way of dumping the artifacts that would then be used in a migration tool (such as the one I have written).

Next Post – Alerts and Alert Actions

Join me later this month when I write about how to convert Splunk Actions and Alert Actions to Azure Sentinel via automated means!

Splunk to Sentinel Blog Series

  1. Part I – SPL to KQL, Exporting Objects
  2. Part II – Alerts and Alert Actions
  3. Part III – Lookups, Source Types and Indexes
  4. Part IV – Searches
  5. Part V – Dashboards and Reports
  6. Part VI – Users and Permissions

References:

  1. Splunk Processing Language (SPL)
  2. Kusto Query Language (KQL)
  3. Splunk REST API
  4. Splunk Download
  5. Azure Sentinel
  6. My twitter
  7. My LinkedIn
  8. My Email

Project Server 2010 – Moving Project Sites and deleting the old PWA causes issues

Recently we had to deconstruct the entire Project Server environment and rebuild it.  This involved moving the sharepoint sites related to the projects and then re-attaching the old project server databases to a new PWA (ie, the old PWA was http://projects.contoso.com, the new PWA is http://projects.contoso.com/PWA).  Then we had to relink the PWA projects to their old but moved sites urls.

This all went fine until the moment we had to view or edit the properties of the documents in the team sites.  We would get a lovely correlation error when trying to view or edit.  The generic error was:

05/30/2012 10:36:10.02  w3wp.exe (0x207C)                        0x1D80 Project Server                 General                        g7ls Exception  System.ServiceModel.FaultException: The server was unable to process the request due to an internal error.  For more information about the error, either turn on IncludeExceptionDetailInFaults (either from ServiceBehaviorAttribute or from the <serviceDebug> configuration behavior) on the server in order to send the exception information back to the client, or turn on tracing as per the Microsoft .NET Framework 3.0 SDK documentation and inspect the server trace logs.    Server stack trace:      at System.ServiceModel.Channels.ServiceChannel.ThrowIfFaultUnderstood(Message reply, MessageFault fault, String action, MessageVersion version, FaultConverter faultConverter)     at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)     at System.S… 22df46a2-3f76-4c8c-b317-d2076882b490
05/30/2012 10:36:10.02* w3wp.exe (0x207C)                        0x1D80 Project Server                 General                        g7ls Exception …erviceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)     at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)     at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)    Exception rethrown at [0]:      at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)     at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)     at Microsoft.Office.Project.Server.Interfaces.IAuthentication.GetUserByName(String username)     at Microsoft.Office.Project.Server.AuthenticationHelper.AuthenticateUserByName(String userName, Guid … 22df46a2-3f76-4c8c-b317-d2076882b490
05/30/2012 10:36:10.02* w3wp.exe (0x207C)                        0x1D80 Project Server                 General                        g7ls Exception …siteId) 22df46a2-3f76-4c8c-b317-d2076882b490

Changing the includeExceptionDetailInFaults property of the D:Program FilesMicrosoft Office Servers14.0WebServicesSharedProjectServerPSIweb.config file to "True", we get a more meaningful error:

05/30/2012 10:36:12.68  w3wp.exe (0x207C)                        0x282C Project Server                 General                        g7ls Exception  System.ServiceModel.FaultException`1[System.ServiceModel.ExceptionDetail]: No site found (Fault Detail is equal to An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is: System.InvalidOperationException: No site found    at Microsoft.Office.Project.Server.DataAccessLayer.DAL.GetConnectionStrings(PlatformContext context)     at Microsoft.Office.Project.Server.DataAccessLayer.DAL..ctor(PlatformContext context)     at Microsoft.Office.Project.Server.Base.PlatformContext.get_Dal()     at Microsoft.Office.Project.Server.Base.PlatformContext.InitSqlCommandTimeout(CachedContextProperties cachedProperties)     at Microsoft.Office.Project.Server.Base.PlatformContext.PreInitialize(Guid trackingId, Guid siteId, SPUrlZone urlZone, CultureInfo languageCulture, Cultu… a73494b6-2bff-4b2f-9faf-67ad55d9f7d9
05/30/2012 10:36:12.68* w3wp.exe (0x207C)                        0x282C Project Server                 General                        g7ls Exception …reInfo localeCulture)     at Microsoft.Office.Project.Server.Base.PlatformContext..ctor(Boolean isWindowsUser, String username, Guid userId, Guid trackingId, Guid siteId, SPUrlZone urlZone, CultureInfo languageCulture, CultureInfo localeCulture, Guid delegateUid, String delegateName)     at Microsoft.Office.Project.Server.Base.Platform…). a73494b6-2bff-4b2f-9faf-67ad55d9f7d9

After reviewing the possibilites of what could cause this, I found a few things:

  • Project sites have special content types (Project Site Document)
  • The Project Site Document has a special field type (Microsoft.Office.Project.PWA.CustomFieldTypes.SPFieldPWALink)
  • Project site lists have event receivers (Microsoft.Office.Project.PWA.WSSEventReceivers.PSDBUpdater)

I tried several different things before I found the one item that was really causing the issues with viewing and editing.  They included:

  • Trying to remove the content type and set them to "Document" – Didn't work
  • Trying to remove the event receivers (removing them was successful, but it didn't fix the problem)
  • Removing the "Links" column values from all the items (the values removed, but it didn't fix the problem)
  • Removing the "Links" field from the list – this was the answer

Turns out that the custom field type had recorded the old PWA url and when the display forms were trying to render, the call to the PWA authentication service would fail because it couldn't find the old PWA url. After removing the "Links" field from the "Content Type/List" and changing the content types to "Document", we were able to viewedit the properties of the items once again:

$f = $list.fields["Links"]
$f.delete()

foreach ($li in $list.items)
{
if ($li["Content Type"] -eq "Project Site Document")
{
$li.file.checkout()
$li["Content Type"] = "Document"
$li.update()
$li.file.checkin("")
}
}

Enjoy!
Chris

Follow me on twitter: @givenscj

Check out the previous blog post:
https://blogs.architectingconnectedsystems.com/blogs/cjg/archive/2012/05/30/SharePoint-Farm-Full-Password-Reset.aspx