StevenMcNutt.com | Steves Tech Blog

Time 2020-12-18 02:03:56

Web Name: StevenMcNutt.com | Steves Tech Blog

WebSite: http://www.stevenmcnutt.com

ID:137661

Keywords:

com,StevenMcNutt,Steves,

Description:

The Cradlepoint devices are using Cradlepoint s cloud management service. Otherwise the routing between the Cradlepoint and the protected network need to be set up. I was unable to get this to work, though I didn t put any time in to it since I didn t have to get it to work.A pseudo Spoke-Hub setup, with the redundancy of the hub to the spoke not being of great concern. In this case our hub site is a cloud provider that uses Fortinet s hosted version of a Fortigate.I am not an expert in this field, some of these steps may not be needed or the configuration suboptimal in some ways. I am just relating what worked for me.The initial setup of the spoke site is a simple site-to-site VPN utilizing static IP addresses at each site:That link will need to be redone since the new connection at the spoke site will need to be an aggregate VPN and the existing IPsec tunnel cannot be set as an aggregate member. I recommend using the USB configuration load so that if the process goes south, the Fortigate can be rebooted and the old, working configuration reloaded.First, the Cradlepoint needs to be set to a dedicated interface on the Fortigate. For most of the sites I used Wan2, though at one I had to take a port out of the Lan configuration. Optimally this port will be part of a separate network so that a system can be hooked to other ports in the set for diagnosing subnet specific issues, but, I forget to do that every time. With that set, I set two equally weighted static routes to the static IP address of the Hub, one through the existing gateway, and one through the internal address of the Cradlepoint. (I did this because the only thing I wanted routed over the Cradlepoint was work traffic.)Next, make a new VPN connection at the Hub that will listen for the Spoke connection. This will be a dial up VPN connection; I used IKE2 and Forced NAT transversal based on recommendations from Fortinet s support forum:The at the spoke site I then setup a matching VPN connection, being careful to mark as an aggregate member:Next, setup a new Redundant IPsec aggregate on the Spoke and add the new VPN connection to it. On the Hub site add a new, equally weighted, static route to the Spoke s network using the new VPN connection made at the hub and add policy rules allowing traffic over it.So far this has been non-destructive, but the next step will interrupt the connection for a bit, depending on how fast you are. On the spoke, change the default gateway to the hub network from the existing VPN connection to the IPsec aggregate, and then change the policy rules used to allow traffic to (and from) the hub to use the IPsec aggregate as well. At this point, the VPN connection over the Cradlepoint network should come up, if not diagnose and fix the issue (every time I had an issue here it was because my settings in the VPN connections did not match up).Next, delete the old VPN connection on the Spoke system that went to the Hub network and recreate it, this time as an aggregate connection.In this next step I usually add the new, re-done, VPN connection to the aggregate and then remove the Cradlepoint aggregate to more throughly test the connection. Once I confirm that it works I add the Cradlepoint back into the aggregate. I then test the redundancy by breaking the regular VPN connection at the hub (by changing the passcode, etc.) and the Fortigate should fail-over to the Cradlepoint VPN. When I un-break it, it should fail-back (confirm by resetting the statistics on the VPN monitor).I generally like having custom reports within the system but it can be spendy to do a bunch of customized reports for trivial tasks. One issue I faced was with reports (in version ~9) where the data was good, but the layout wasn t in a dataview (Vouchers Payable, Aging reports, etc). I could get around this myself by running a query inside of SQL Server Management Studio, but that got me thinking: why not just allow users themselves to execute the stored procedures that generate the data from within Excel? This gets around two issues, first is that it is not a customized report as the user is running the same routine that CSI does and secondly they needed to get it into Excel anyway, so in a way I m skipping the middle man .The first thing to do is to collect the list of parameters needed to run the report. This can be gotten rather easily by printing the report and then going to Background Task History and pulling the task parameters:I then make a note of those and execute the stored procedure inside of SSMS using those parameters, but an important note here: do not leave any of the values as null as Excel cannot pass null values to the stored procedure (as I am doing it without any code). After some cleanup I have something like this:This is also good because the parameters are in the sequence needed for Excel, as we ll see in this next step where we will put the parameters on a separate tab. Although ranges are required for Excel, and it kinda sorta doesn t do nulls, I d found that putting a double single quote in the cell accomplishes what I need it to (note: shows up as a single when viewing the spreadsheet):Next we can put the query together in Excel, be sure to use From Microsoft Query because otherwise we will not be able to pass parameters (dynamically) to the stored procedure:If needed on the next step add a connector to the database server ( New Data Source ), then select the connector and click OK. On the next step click Cancel and then Yes to edit it in Microsoft Query. Click Close and then click the SQL button:Then click the Definition tab and then Edit Query. The formatting on this can be a smidge tricky since there are multiple parameters, but you ll want a question mark for each parameter going to the stored procedure, so in this case I ll enter a query like this:After you click OK you will get a warning that it can t be represented graphically, click OK again and you will be prompted to fill out the parameters, just click OK on all of them (leaving them blank). Even if it comes back with no data that s okay, just go up to File->Return Data to Excel.When it goes to Excel it will re-prompt for parameters. In this case, it will more than likely want real data or it will error out and dump the query and you ll have to restart from the Data Connector step. I will usually key this in and fix it afterward, though you can set it up properly at this step by selecting the cells on the Parameters sheet, just be sure to click Use this value :Hopefully that ran through okay, but this then gets to up a permission issue that I ran into. For users running these stored procedures the only database access I wanted them to have was to the stored procedures themselves. However, when I tried to run it as the user I would get ODBC errors in Excel and in SQL I would get errors like:Get DataType failed for StoredProcedureParameterProperty DataType is not available for StoredProcedureParameterThis post put me on the right path. The issue is that many CSI stored procedures have custom data types and the user needs to have View Definition permission to the schema where the data types are scoped to, in this case dbo . So I give the user Execute permission on the stored procedure and View Definition to the dbo schema and then the user can run the query inside of Excel with minimal permissions.[Note that some of the data returned by these stored procedures can be ungainly. For the Vouchers Payable report I ended up making a new copy of the stored procedure and cleaning up the outputs. This is rather trivial and the advantage here is that as the stored procedure changes the Excel output will change accordingly without having to change anything on the spreadsheet].A user deleted and recreated their mobile account which landed their mobile device into quarantine but it was unable to be approved. Also, quarantined devices for users who have been deleted or disabled cannot be removed using the GUI.The issue seems to be that there are two sets of data to keep track of mobile devices on the server. The device could not be approved, because it was already approved and was stuck in quarantine. Rejecting the device added it as an unapproved device for the user.First I needed to delete some zombie devices. Step one was to set Powershell scope to the whole forest:Set-AdServerSettings -ViewEntireForest $trueThen get a list of mobile devices:get-mobiledevice |fl UserDisplayName,Guid,DeviceTypeI then pick out the phone that I need to delete and remove it:Remove-MobileDevice -IdentityThen I need the device ID that I need to change (this will be the same Device ID in the management GUI for Exchange):get-casmailbox -Identity |fl ActiveSyncBlockedDeviceIds,ActiveSyncAllowedDeviceIdsThen set delete/add the device to the Blocked or Allowed lists:Set-CASMailbox -Identity -ActiveSyncBlockedDeviceIDs @{remove= }ORSet-CASMailbox -Identity -ActiveSyncAllowedDeviceIDs @add= }One of the more lightly requested modifications to the Customers form in Syteline was for there to be a way to tell how long someone had been a customer (for credit check purposes). I rigged this up to work by first adding a field for CreateDate to the Customers form (which is easy since it s already in the IDO for the form):I then execute a SQL query to change the CreateDate to be the customer since date (going forward these should be the same, but for legacy customers the date would just be whenever they were added to the database):UPDATE customer_mst SET CreateDate='1/1/20' WHERE cust_num='TEST' AND site_ref='TESTDB';We had some initial struggles getting running on our CSI implementation so we started with one of our smaller operations. We had very little guidance with several nuances with the package (it s a long story that s not fit for print) so I m going to touch upon them here, but, the overarching rule is thus: routers need to be correct so that both costing and scheduling is correct. If the routers are bad, both the costing and scheduling will not work as designed in CSI.To begin, all manufactured parts in CSI need a router to be setup up in Current Operations and Current Materials. In other systems these are called the job templates , though in CSI there is only one true template . I will point out that this mostly applies to the APS flow in an actual costed environment. I do suppose that it is possible to use a standard costed system and ignore the scheduler and in that case perhaps the Currents are not needed. However, I think that is still bad form since the system will then not have a baseline to compare actual production values to. It is possible for the numbers to be compared to the standard but the standard is just an educated guess made by an, no doubt talented, accountant while the planned cost that is derived from the router is a guess made by production.Properly constructed routers have another benefit too: if the job cost variance report shows a very odd planned cost then that probably means that there is an issue with the router that may show up in scheduling the job. In this way production and accounting get actionable data out of the same single data set.As well, this planned cost is critical in an actual costed environment. In CSI if a partial job quantity is moved in to inventory, how does CSI know how to cost it since not all the costs have been incurred on the job? The answer: it uses that planned cost. Thus it is critically important that the planned be as close to the actual as possible (think of it like a standard cost that isn t actually standard). When the job is closed out the difference between the planned and the actual is sent to the inventory adjustment account in CSI. The correct sequence for inventory moves when using actual costing in CSI then is:If the job is being moved into inventory complete, then close the job and move the inventory at the same time.If there have been any partial moves from the job into inventory, then move all the parts as partial and close the job out separately (within the Job Orders form).I found the operation and tuning of APS itself pretty straightforward with two notable exceptions:Forecasting does not work. Well, I take that back, there are a narrow set of circumstances where the Forecasting function might work but I ve been unable to find anyone that it would work for. The workaround for this is to put a bogus sales order into the system that serves as the forecast (and manually deducting the forecast as its consumed).APS handles Setup times very poorly in that it doesn t roll the Setup resource group off of the job after the setup is complete. Part of this probably has to do with the fact that CSI has no way to know if a setup is complete. The workaround for this is to put the setup on the router as a separate operation. This can lead to some wonky views in the scheduler since there is no guarantee that the machine being setup is the same one that will be doing the run, BUT, the throughput will be correct.We also ran into two other issues that were no fault of the package. In one instance, some of our routers were very inaccurate and it was very difficult to get them fixed so the planning people resorted to marking the parts as MRP which forces APS to use whatever lead time it is told. The other issue had to do with a change in methodology since it s important that the operations are closed out as they are completed, otherwise APS leaves the whole time scheduled. This was something we had to work on with the employees so that they knew when (and when not) to close out an operation.

TAGS:com StevenMcNutt Steves 

<<< Thank you for your visit >>>

Hot Websites