Simplifying Function with Recursive CTE and/or Window Function

I’m trying to come up with a Recursive CTE and/or Window Function to create a function.

After days, I’ve boiled the function down to (pseudocode) where I have N and B, and need to generate E:

En = Bn * (1 – SUM(E1, E2, … En-1))

Examples:

╔═══╦═════════════╦═════════════╗ ║ N ║ B           ║ E           ║ ╠═══╬═════════════╬═════════════╣ ║ 0 ║ 0.142857143 ║ 0.142857143 ║ ║ 1 ║ 0.285714286 ║ 0.244897959 ║ ║ 2 ║ 0.285714286 ║ 0.174927114 ║ ║ 3 ║ 0.285714286 ║ 0.124947938 ║ ║ 4 ║ 0.285714286 ║ 0.089248527 ║ ║ 5 ║ 0.4         ║ 0.089248527 ║ ║ 6 ║ 0.666666667 ║ 0.089248527 ║ ║ 7 ║ 1           ║ 0.044624264 ║ ╚═══╩═════════════╩═════════════╝ 

E0 = 0.143 * (1 – 0) = 0.143
E1 = 0.286 * (1 – 0.143) = 0.245
E2 = 0.286 * (1 – (0.143 + 0.245)) = 0.175
E3 = 0.286 * (1 – (0.143 + 0.245 + 0.175)) = 0.125
E4 = 0.286 * (1 – (0.143 + 0.245 + 0.175 + 0.125)) = 0.089
E5 = 0.400 * (1 – (0.143 + 0.245 + 0.175 + 0.125 + 0.089)) = 0.089
E6 = 0.667 * (1 – (0.143 + 0.245 + 0.175 + 0.125 + 0.089 + 0.089)) = 0.089
E7 = 1.000 * (1 – (0.143 + 0.245 + 0.175 + 0.125 + 0.089 + 0.089 + 0.089)) = 0.044

If the table above was in Excel, C2 = B2 * (1 - 0) (base) and C3 = B3 * (1 - SUM(C$ 2:C2)) (recursive)

What I’ve tried:

Windowed Functions

Tried SUM(...) OVER(ORDER BY [N] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), but can’t reference the column recursively.

Recursive CTE

Tried several iterations of:

WITH B AS ([Num], [Best], [Effective Rate]) AS (     SELECT *         , [Best]     FROM A     WHERE [Num] = 0     UNION ALL     SELECT A.*         , (1 - [Effective Rate]) * A.[Best]     FROM B     JOIN A ON A.[Num] = B.[Num] + 1  ) 

and some with an extra column in the CTE, but it only covers 1 previous row and results after 2nd row are wrong.

Recursive CTE with Windowed Function

From all that I’ve tried, it seems that the recursive segment of the CTE is calculated independently of the other results, and SUM(...) OVER(...) only works on the current row. (With regard to the above table, all values of E would be 0.142857143).

I assume this is because the UNION ALL happens all at once, and not incrementally.

Alternative Solutions

What I would really like to happen is to simplify the above equation, and/or transform it into an iterative function.

Bonus: If anyone cares to know the source of this information, it’s used to calculate MACRS depreciation for tax purposes.

What happens to clones a deepspawn created if/when the deepspawn dies? (D&D mechanics and/or lore)

I am running a 5E homebrew campaign where a powerful creature akin to a deepspawn is consuming and creating spawn of powerful beings in various governments and organizations to puppet a growing portion of civilization.

My question is should my players succeed in finding and destroying this creature, what have past editions and/or D&D literature said about the fate of the spawn it had created?

Since this is homebrew I know I can pretty much do whatever I want, but I’m looking for insights into how to handle it so I have a plan in place.

I only have access to the Lost Empires of Faerun 4E book regarding them, and though it goes into detail about how the spawn are created and what the spawns’ capabilities are, it does not mention anything about the spawns’ existence after the deepspawn is slain.

Any examples from D&D literature or rulebooks would be greatly appreciated.

Thanks!

Should I activate Windows 10 and/or Office for a sandbox system?

I’m building a Windows 10 malware analysis sandbox. I’m debating activating Windows or not: I can think of arguments for both:

  • For activation: Malware might check the Windows activation status
  • Against activation: If I connect this system to a dedicated internet connection, I’ll need to disable the Windows Update services, which could also be a tell for malware

What approach do you recommend?

Can Amazing Tools of Manufacture be used for mundane and/or magic crafting?

The description of the item states, that “The wielder may take raw materials with a value equal to half the price of an object to be crafted…” (for the full description see here).

The ratio of 2:1 for market price vs. costs is normally used for the creation of magic items, therefore this seems to indicate that the creation of magic items is the intended use (for mundane items the ratio is 3:1 or 4:1 using the Unchained Rules).

On the other hand, crafting of mundane items is not specifically excluded, and the text refers to a skill check for an “object to be crafted”.

Could for example a Master Craftsman with at least 6 ranks in Craft(armor) craft a Mithral Full Plate (market price 10.500 gp) with these tools in 6 days for 5250 gp?

Has a recent patch disabled TLS1.2 and/or RSA on Internet Explorer?

Our office intranet has from today started refusing access from Internet Explorer 11 (necessary as it uses plugins) to a certain page. The error I get in IE is in Japanese, but talks about old TLS versions. If I look at the page with Chrome, on the Security page

Connection – obsolete connection settings

The connection to this site is encrypted and authenticated using TLS 1.2, RSA, and AES_256_GCM.

RSA key exchange is obsolete. Enable an ECDHE-based cipher suite.

I know that the deprecation date for TLS 1.2 has been extended due to COVID-19, but a recent patch seems to have fixed CVE-2020-1118, but in the process might it have broken something else? Note, I do allow TLS 1.2 connections in IE.

What balance considerations should I make if I remove the Corruption and/or Insanity mechanics from Shadow of the Demon Lord?

In Shadow of the Demon Lord, there are mechanics that help portray its dark fantasy world. “Insanity” arises from undergoing stressful situations, whereas “Corruption” accumulates as the the character commits evil or dark acts. Both have tangible effects for the character (a high Corruption character would be impossible to resurrect, for example).

Suppose that a group would enjoy the general underlying gameplay, but not the darkness necessitated by the setting.

If I were to remove Corruption and Insanity from the game, what parts of the system would I have to tailor or remove so that the game remains fair?

How can I gain resistance to poison and/or necrotic damage?

I’m about to start a campaign that I suspect will be filled with poisonous & necrotic monsters due to the setting. I have been thinking of playing a stout halfling, but would prefer to play one with a wisdom bonus. Are there any other ways to gain resistance to at least one of these types?

I will likely be a squishy cleric in a caster role, so I should be able to avoid most of it – but I’m wondering if there’s anything else I can do.

XSS filter bypass &lt and/or

On a homepage I can inject HTML-text in the URL. After som T-and-E I was able to send in this thing; ?type=<script<script>>alert(1)</script<script>> Which gave me a homepage which presented me with the headline-text: <script>alert(1)</script> So I am pretty close.

The Page Source had the following HTML: <h1>&lt;script>alert(1)&lt;/script></h1> So for some reason only the Less-than is transformed into &lt. As soon as I send in the &-sign the whole html-injection is filtered out.

Any suggestion on how I can bypass this. I have tried double encoding to get the real “<“-sign. Any “<“-sign sent into the URL gets converted into &lt.

I have tried suggestions from OWASPs checklist, Portswagger, Stackoverflow etc. The injection is out-of-tags. It is not in a script or html-tags, so I need to put it between tags according to me.

Chkrootkit and Rkhunter – What other tools are capable and proper to diagnose risks and/or infections?

PREAMBLE

I’m on the internet for a while looking for the right security tools to locate and diagnose malware on Linux servers. Tools capable of doing system scans on Linux. Tools with capabilities and characteristics similar to Chkrootkit and Rkhunter.

I know that there are many tools to find malware like ClamAV, Linux Malware Detect (maldet), Sophos and Lynis. But most of these tools are intended for system hardening, file servers, email servers or shared hosted environments…

The only tools capable or proper to do system scans on Linux systems that I have found so far are Chkrootkit and Rkhunter, as we can see in a successful detection made by Chkrootkit in this thread https://unix.stackexchange.com/a/567413/61742 .

That is, the other tools are not able to deal with risks for the Linux system itself, such as detecting if the system is infected. In general they are only able to scan specific folders and tell you whether a file is dangerous or not and remove it.

Contrary to what is thought by many people, Linux is vulnerable to malware. Tools like Chkrootkit and Rkhunter play a very important role!

According to the good experiences I had with my servers in my infrastructure, the best way to find malware on Linux is to use several tools together as done in this tool that I made available to the community https://github.com/eduardolucioac/private_tux (BSD-3-Clause license) and that I use on my infrastructure daily.

Finally, I think this question is very relevant to the community, it needs to be done and it was done in the right place!

NOTE: I’m the author of Private_Tux and it has no commercial purposes (BSD-3-Clause license).


QUESTION

What other tools are capable and proper to diagnose risks and/or infections (malware) in the Linux system?