Hi @Volans - thanks for reaching out with the suggestion. We definitely could start doing that going forward. In this additional tab on the Accounting Spreadsheet, was there a certain format that you were looking for us to use? Thanks, Willy

@wiki_willy for the format whatever is easier for you based on your workflow, here a couple of alternative options that comes to mind, but feel free to propose something else:

• add a Recycled column to the existing spredsheet (in the Assets sheet) that could contain either a date or a 1/yes/checkmark.
• add another Recycled sheet to the same spreadsheet with just Serial Number and Asset Tag so that the report would exclude them. You could also add a date column if that's useful for accounting purposes.

If a date will be available we could even use that in the report, so you could pre-populate the sheet and the report will ignore them only starting from the recycled date.

For the longer term I'm not sure what's required for accounting, how long we need to keep the history of devices after they have been recycled and based on that if we'll want to move very old devices to a separate sheet just for historical purposes. But nothing need to happen right now for this ;)

Got it, thanks @Volans! I'll sync up with my team to get their thoughts and feedback on Thursday, and get back to you afterwards.

Hi @Volans - we have one thought (and we're totally open to feedback, pros, cons, etc) that's somewhat tied to T310594. What if we were to change the status of recycled hardware to status = "recycled" in Netbox, instead of deleting them? And with the "recycled" status, it could be greyed out or something in Netbox, so that no one would have access to modify the entries or possibly even view them since it's no longer under WMF ownership. Then anything with the status of "recycled" in Netbox could be imported back into the Accounting Spreadsheet or ignored/exempt from Netbox errors. Do you think this would be a good idea at all?

Long term wise, I can take the action item and check with Julianne on how long they need to keep track of these devices.

Long term wise, I can take the action item and check with Julianne on how long they need to keep track of these devices.

Good idea! I think also how they need to keep track of them. Will it be a spreadsheet or do they have plans to use a different tool.

About the RECYCLED status.
The main upside I see are:

• apparent simplicity, just needing to change the status on a device, maybe remove some attributes, and we're set.
• It would clear up the issue outlined here (discrepancy between the accounting report and Netbox)

On the downsides:

• Risk of ossification (making more difficult to make changes to Netbox as we have more use-cases to account for)
• Maybe risk of confusion/added complexity (more transitions to automate, higher learning curve for new hires)

It's also not possible to make devices (or native fields) read only.

If the goal is only to address the accounting report issues, creating a new Netbox status seems too big of a hammer to me.

To build on Riccardo's ideas, if those recycled serial numbers were tracked in a Spreadsheet (or external tool). We could have a Netbox script (or cookbook) that abstracts the device deletion and update the Spreadsheet.

I agree with @ayounsi that adding a status just for this seems a bit too big of a hammer. Also there is no way to make those devices "grayed" and they will pollute all future searches and will need to be excluded manually when looking for something. For this reason I don't think it's the best solution here.

Hi @Volans - no problem, we can scrap the idea of having a "recycled status" in Netbox. For everything that gets deleted in Netbox, is there any feature or anything that could pull that information upon deletion and dump it into a csv file or port it into this spreadsheet? (just wondering if there's something else that could decrease any manual work) If not, we can just create a new tab, listing the asset tags and serial numbers of everything we've sent out / deleted in Netbox. I went ahead and created a new tab called "recycled" with the list of assets recycled from the last eqiad and codfw pickup, in case you wanted to test it out a bit.

Also, I chatted with Julianne a little bit today, and she prefers to keep all the older 5+ year old data. It doesn't have to remain within the "assets" tab on the Accounting Spreadsheet. She's totally fine if we cut and paste it to another tab or if she copies everything onto another document. Do you have a preference around this?

Thanks,
Willy

In T320955#8351639, @wiki_willy wrote:

For everything that gets deleted in Netbox, is there any feature or anything that could pull that information upon deletion and dump it into a csv file or port it into this spreadsheet? (just wondering if there's something else that could decrease any manual work)

Netbox supports webhooks, so potentially yes, we could write some code that gets called when a device is deleted that would add the data we need into the spreadsheet.

I went ahead and created a new tab called "recycled" with the list of assets recycled from the last eqiad and codfw pickup, in case you wanted to test it out a bit.

Great, I'll have a look.

Also, I chatted with Julianne a little bit today, and she prefers to keep all the older 5+ year old data. It doesn't have to remain within the "assets" tab on the Accounting Spreadsheet. She's totally fine if we cut and paste it to another tab or if she copies everything onto another document. Do you have a preference around this?

Not really, but I guess that moving the very old data elsewhere might speed up both the browser loading for those that use the spreadsheet manually and the netbox report.

Thanks @Volans. After you poke around a bit, let us know the best way of proceeding forward. If we can automate it via webhooks, that would be terrific. But if it takes a lot more time then it's worth, we're fine pasting all the Netbox deletions into the spreadsheet under the "recycled' tab. Thanks, Willy

Hi @Volans - just a heads up, it looks like some of those 5yr servers on the Accounting Spreadsheet are starting to pop up on the Netbox Error Report as accounting mismatches, even though we've sent some of them off for recycling. Let us know when you have the script working to exclude the assets on the recycling tab.

Thanks,
Willy

Following this doc I was able to add data to a spreadsheet using a generic python request:

var postDataList = [];
var waiting = 0;

function doPost(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var hook_load = null;
  try {
    hook_load = JSON.parse(e.postData.contents);
  } catch (error) {
    sheet.appendRow([new Date(), "error", "There was an error in this data"]);
    return;
  }
  sheet.appendRow([new Date(),hook_load]);

  SpreadsheetApp.flush();
};

import requests
url = 'https://script.google.com/a/macros/wikimedia.org/s/AKfycbzlXwS-VCxKddiV8mB8UBxFnaRVRvk0tKzbhUfaAlXc1MzKo_ygaMFUaYbLSFEi5YJa/exec'
myobj = {'somekey': 'somevalue'}
x = requests.post(url, json = myobj)

However adding it as a webhook in netbox-next didn't add the deleted device to the spreadsheet.

There are a few things to figure out and those might take time:

• Google Spreadsheet access through the proxies (squid don't show the request so Netbox probably didn't try)
• Google spreadsheet permissions
• Google script fine tuning (appending to an existing spreadsheet tab, error handling, etc)

That said, the webhook also fails silently, so if we go that way there is a risk of missing data as we delete devices.

Another option might be to abstract device deletion through a Netbox script. That way we could add safeguards (eg. prevent the deletion if X or Y conditions are not met) as well as ensure remote calls succeed and do a "write on disk" backup.

Development time to compare with the current copy/paste workflow.

@ayounsi we already have all that setup...

Thanks, let us know if there's anything we can do in the meantime. Here's a list of the assets that are reporting as Netbox errors for accounting mismatch, which we had shipped out for recycling and already deleted in Netbox:

WMF7248
WMF7212
WMF6620
WMF7376
WMF7431
WMF6660
WMF6661
WMF7458
WMF7456
WMF7457
WMF7232
WMF7234
WMF7233
5BF90C3
7S5LMH3
7S5MMH3
7S5NMH3
7S5PMH3

In T320955#8744401, @Volans wrote:

@ayounsi we already have all that setup...

To add a bit more context, I was out on my mobile and just wanted to post a quick reply to avoid anyone wasting time without having the full context on it.

@wiki_willy I'll try to look at it next week, it should be easy to read from the spreadsheet you showed me and exclude those for now. Then we can thing later for a more long-term plan that scales better.

Sounds good, thanks!

@wiki_willy I'll try to look at it next week, it should be easy to read from the spreadsheet you showed me and exclude those for now. Then we can thing later for a more long-term plan that scales better.

Change 905183 had a related patch set uploaded (by Volans; author: Volans):

[operations/software/netbox-extras@master] reports: exclude recycled devices from accounting

https://gerrit.wikimedia.org/r/905183

Change 905183 merged by jenkins-bot:

[operations/software/netbox-extras@master] reports: exclude recycled devices from accounting

https://gerrit.wikimedia.org/r/905183

@wiki_willy this is now deployed and should exlude the devices list in the accounting spreadsheet, Recycled sheet.
FYI from line 340 on that sheet there are a bunch of asset tags without other info, as this is the key used to exclude them, they are excluded too.

Feel free to resolve the task if this works for you.

Thanks @Volans, we'll get the additional info for lines 340 and onwards. I'm still seeing the following S/N's on the Accounting Spreadsheet that are still reporting Netbox errors though, of "Device with s/n not present in Netbox" Can you take a look at these, before we resolve the task?

5BF90C3
7S5LMH3
7S5MMH3
7S5NMH3
7S5PMH3

Thanks,
Willy

In T320955#8755710, @wiki_willy wrote:

Thanks @Volans, we'll get the additional info for lines 340 and onwards. I'm still seeing the following S/N's on the Accounting Spreadsheet that are still reporting Netbox errors though, of "Device with s/n not present in Netbox" Can you take a look at these, before we resolve the task?

5BF90C3

From the account sheet it's related to T264677, so probably was cloudnet2004-dev that was decommissioned in T306989. It has then been deleted in netbox in https://netbox.wikimedia.org/extras/changelog/95062/ Has it been recycled too?

7S5LMH3
7S5MMH3
7S5NMH3
7S5PMH3

Those 4 in the account sheet are related to T286590 that was referring to T290694 that installed cp403[3-6] that later were decommissioned in T321596 and deleted in Netbox:
https://netbox.wikimedia.org/extras/changelog/105987/
https://netbox.wikimedia.org/extras/changelog/105988/
https://netbox.wikimedia.org/extras/changelog/105989/
https://netbox.wikimedia.org/extras/changelog/105990/

Where those recycled too?

Hi @Volans - yup, that's correct. They've all been recycled. For any equipment that we've already sent out for recycling, we've been adding the asset tags and serial numbers to the "recycled" tab of the Accounting Spreadsheet since last year:

https://docs.google.com/spreadsheets/d/11xbHX7lRzglFYc85kvmtOjssOfm3tCkxH7cYr7ImFbk/edit#gid=1683221629

So since the serial numbers I had listed are already part of this recycled host list (ie: have been added to the "recycled" tab of the Accounting Spreadsheet and deleted in Netbox), how come they're still popping up here as a Netbox error?

https://netbox.wikimedia.org/extras/reports/results/4437511/

Is there something else that we need to do to prevent the Netbox error from being triggered?

@wiki_willy that's because all those have N/A in the Accounting tab of the spreadsheet in the Asset tag column and so they don't match.

Hi @Volans - thanks for the details on S/N #7S5LMH3, 7S5MMH3, 7S5NMH3, 7S5PMH3, and 5BF90C3. The first four were deleted in error, which @RobH just fixed...and I'll ask @Papaul about 5BF90C3 when he's back next week. If these had been legitimately recycled and deleted in Netbox though, how would we prevent the Netbox error from alerting? If I'm looking at it correctly, the "Asset tag #" column on the Accounting Spreadsheet is supposed to show up as "N/A" because of the formula in place:

=NETBOX_BYSERIAL(<cell number>, "asset-tag")

This formula starts being used on line 804 of the Accounting Spreadsheet for assets purchased in December 2019 and onward, but not previously in lines 1 thru 803. As more time passes, do more items in lines 804 and greater, become hardcoded with the data being pulled in the "Asset tag #" column?

I don't manage that spreadsheet, so I have no idea :) If that doesn't work we can easily switch to do the match on the Serial number column, that seems hardcoded for all values. Will that be ok for you?

Would it be possible to use both serial number and/or asset tag for the match? I'll follow up with Julianne (she's currently out) regarding the formula being used on the accounting sheet; my bad, I thought you had helped create that part.

But if the asset tag comes from netbox it will not match anything for future hosts... as the host will not be anymore in Netbox :)

Change 906635 had a related patch set uploaded (by Volans; author: Volans):

[operations/software/netbox-extras@master] reports: accounting use serial to match recyled

https://gerrit.wikimedia.org/r/906635

@wiki_willy I've sent the above patch to match on serial instead of asset tag. LMK what do you want to do.

Change 906635 merged by jenkins-bot:

[operations/software/netbox-extras@master] reports: accounting use serial to match recyled

https://gerrit.wikimedia.org/r/906635

Change 906638 had a related patch set uploaded (by Volans; author: Volans):

[operations/software/netbox-extras@master] reports: accounting convert serial to string

https://gerrit.wikimedia.org/r/906638

Change 906638 merged by jenkins-bot:

[operations/software/netbox-extras@master] reports: accounting convert serial to string

https://gerrit.wikimedia.org/r/906638