Add Asset State to Patch Report

[WitheringSilenc]

Forgive me if this has been asked before. I use the monthly patch report that is posted every month. However, the only machines in the report are the ones whose state is set to 'Active'. I would like to also include the states 'In Repair' and 'Spare' so that I can more accurately track my hardware assets and their purpose.

For example, I have a PC, currently it is a testing spare in my office. I would like to set it as a 'Spare' state and still have it included in the monthly patch report.

I've tried adding the corresponding integer for the state 'Spare' but I am getting syntax errors and I'm still new at the report building.

I found the following during a Google search for the relating integer for the states:

1 Active
2 Non-Active
3 Sold
4 Stolen
5 Broken
6 Don't show
7 Spare
8 In Repair
9 Stock

Can you help figure this out?

Thanks

[WitheringSilenc]

RC62N,

Thank you for your reply! The part of the code I couldn't figure out is

Code:
1 Where tblAssetCustom.State IN (1, 7, 😎

That's exactly what I needed. I knew where to look for how they are listed by Active, but not how to incorporate two others.

Again, thanks for your help!

[RCorbeil · Accepted solution]

You didn't actually need to resort to Google; everything you need is available with LANSweeper.

Take a look at the WHERE clause on the report you want to modify and you should see

Where tblAssetCustom.State = 1

Look at the top of the report editor and you should see "View Database Documentation" to the right of the title. Click on that. You'll be presented with a list of LANSweeper's tables and, if you scroll down, the contents of those tables.

Press Ctrl+F and type "tblAssetCustom", the name of the table that the WHERE clause is filtering a field from. That should jump you to the name of the table in the list. Click on it and you should jump to the details of what's in the table.

Back to the WHERE clause, it's filtering on tblAssetCustom.State, so take a look at the description of the State field. You'll see that that field references tblState.State. Click (or Ctrl+click to open in a new tab so you don't lose your place) on the "tblState" link and you'll jump to the details of that table.

Given that, if you want to know what all the states are, create a new report,

Select Top 1000000
  tblstate.State,
  tblstate.Statename
From tblstate

and there's your list. (Take that, Google! )

Knowing the contents of that list, you can then edit the WHERE clause on the report you want to modify. Change

Where tblAssetCustom.State = 1

to

Where tblAssetCustom.State IN (1, 7, 😎

to include assets that are Active (1), Spare (7) or In Repair (8).