Spiceworks

Spiceworks is best value for money (it is free) IT inventory and Ticket system. And No, it does not send any local collected info to any external site! Their business model is advertising. Spiceworks is supported by big players like Microsoft, HP, Dell, IBM, VMware, EATON, Intel etc.

The database is stored in SQLite format. Well known and open. For more advanced use you need to know this DB (SQL Reports and importing Devices)

Use a tool like SQLite Expert Personal (free) to view the DB and get the tables and fieldnames.

Other than SQLite it uses Nmap, Ruby, Apache and WinPcap.

Spiceworks works well without an agent. But you can install a little agent on the PC's as well.

What can Spiceworks do?

- it offers a Heldesk ticket tool and user portal

- it collects information on devices (ip ranges) using WMI, snmp, ssh etc.

- it collects info on users and computers on your Active Directory Domain

- it collects installed software on Microsoft OS

- it can detect unwanted software

- it collects license keys for OS, Office and other software

- it can do asset management, e.g. who has what devices

- it reports, manual or send it as PDF at given times

- it created manual reports (internal drag and drop or SQL)

- it alerts based on rules (triggers, ping, eventviewer, collected data, ...)

- it provides purchases, even predict toner empty dates

- it can monitor your bandwith on LAN and WAN routers

- it provides info on switches and which devices are on the ports

- Spiceworks has a great community providing good support and answers

What it cannot do!

- distribute and manage software

Make custom notifications to show up in spiceworks

I was looking for a way to get Spiceworks alerts for things like detecting suspicious registry keys or files on the disk. The way to do it is using the tools:

- A login script
- the proper command to detect what you want on the PC
- EventCreate.exe command with a distinctive ID to trigger Spiceworks (like 999)
- Configure Spiceworks Monitor and Alerts to e-mail this event if occurs in the event log

For example:

For /f %%a in ('Reg.exe Query "HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Image File Execution Options" /f "VerifierDlls" /v /s^|Findstr HKEY_') Do (

REM powershell.exe "send-mailMessage -to "'info@klaaspuul.com'" -subject 'Possible hack on %COMPUTERNAME%' -from '%COMPUTERNAME%@klaaspuul.com'

REM -body 'Suspicious registry key detected: %%a' -SmtpServer '192.168.10.20'"

Msg.exe * "Suspicious registry key detected on this PC: %%a Please contact IT as soon as possible."

EventCreate.exe /L SYSTEM /T WARNING /ID 999 /D "Suspicious registry key detected on this PC: %%a Please contact IT as soon as possible."

)

Plugins

Plugins are addons to the default Spiceworks installation. These can be Apps, Reports or customizations.

Remove or Edit Ticket Comment - Default you can only modify the ticket first entry. This can be used to edit all comments. Helpdesk v1 and V2.

Chart Wizzard - Create more Chart types from SQL Report Widgets

Dutch Language Pack - Most up to date translated Dutch UI

Friendly Network Scan Entries - Give a name to these ip ranges you are scanning

Bind Spiceworks to one NIC or multihomed

When having more than one IP address on the server it is advised to bind Spiceworks to only one IP address. You need to edit the C:\Program Files (x86)\Spiceworks\httpd\conf\httpd.conf file:

Listen 1080

Listen <ipnumber>:1080

Custom Reports SQL

Domain Password Expiry days, make available as Widget type Bar for the Dashboard!

-- HR: 4 mei 2015 - version 1.0

-- Spiceworks SQL Report to show password expiry days for all normal users (512)

Select cn,Round(julianday(pwdlastset,'+90 day')-julianday('now'),1) As passwordexpires

From ad_users

-- Where (userprincipalname is not null) And (passwordexpires > -10)

Where useraccountcontrol = 512

Order By passwordexpires Asc;

Domain Logons

-- HR: 4 mei 2015 - version 1.0

-- Spiceworks SQL Report to show last logon info Domain Accounts

-- Select '<a href="/people/'||user_id||'">'||cn||'</a>' As Username,

Select cn As Username,

badpasswordtime,

badpwdcount

lastlogontimestamp As LastLogon

From ad_users

-- Where (userprincipalname is not null)

Where useraccountcontrol < 66080 /* normal user is 512 */

Order By Username Asc;

Accounts logged in on which computers

-- HR: 8 mei 2015 - version 1.0

-- Spiceworks SQL Report to show last logon info Domain Accounts on what PC's

Select n.name As 'Login Name',

n.full_name As 'Full Name',

n.number_of_logons As 'Nr of Logons',

n.last_logon As 'Last Logon',

d.name As PC,

d.description As Description

From network_users As n

Left Join devices As d On n.computer_id = d.id

Where n.number_of_logons Is Not Null

Order By 'Login Name' Asc;

Weekly Printer Supplies report: (e-mail every week to purchase in XLS format)

-- HR 8 mei 2015 - version 1.0

-- Get all Printer Supplies info

Select d.name As PrinterName,

d.manufacturer As PrinterManufacturer,

d.model As PrinterModel,

p.name As SupplyName,

p.color As TonerColor,

p.level As PctRemaining,

p.projected_empty_date As ProjectedEmptyDate,

p.average_cartridge_life As AverageCartridgeLife,

d.page_count As PageCount,

d.location As PrinterLocation,

d.last_scan_time As LastScanTime

-- d.description As PrinterModel,

-- d.serial_number As PrinterSerialNumber,

-- d.ip_address As PrinterIPAddress,

-- d.mac_address As PrinterMACAddress

From devices As d

Left Join printer_supplies As p On d.id = p.network_printer_id

Where p.level > 0

Order By PctRemaining Asc;

All Event Logs (Error) from every PC: (e-mail every week to IT)

-- HR 15 mei 2015 : version 1.0

-- List all (Error/Warning/Information/Failure Audit) collected Events from all computers

Select d.name As Computer,

e.log As EventLog,

e.uid As ID,

e.event_date As Date,

e.source As Source,

Replace(e.message, 'message string data: ','') As Message

From events As e Left Join devices As d On e.computer_id = d.id

Where e.event_type = 'Error'

Order By Date Desc;

Software installed in the last 30 Days: (e-mail every week to IT)

-- HR: 13 mei 2015 - version 1.0

-- Spiceworks SQL Report to show New Installed Software on what PC's

Select '<a href="/inventory/software/applications/'||s.id||'">'||s.name||'</a>' As Software,

-- '<a href="/inventory/groups/devices/'||d.id||'">'||d.name||'</a>' As PCName,

'<a href="/asset/show/'||d.id||'">'||d.name||'</a>' As PCName,

i.install_date As InstallDate,

i.version As Version

From software_installations As i

Left Join software As s On i.software_id = s.id

Left Join devices As d On i.computer_id = d.id

Where i.install_date > Date('now','-30 day')

Order By InstallDate Desc;

List all Monitors on PC's with resolution, brand, date, serial

-- HR 15 mei 2016 : version 1.0

-- List all monitors, sizes and serials from all computers

SELECT

'<a href="/asset/show/'||d.id||'">'||d.name||'</a>' As PCName,

-- d.name AS 'PCName',

m.name AS 'Name',

m.screen_width||'x'||m.screen_height AS 'Resolution',

m.manufacturer AS 'Manufacturer',

m.model_name AS 'Model',

m.serial_number AS 'Serialnr',

m.manufacturer_date AS 'ProdDate',

d.last_scan_time AS 'LastScan'

FROM desktop_monitors As m INNER JOIN devices As d ON m.computer_id = d.id

-- WHERE d.name='lpc002'

ORDER By PCName Desc;

Report all mailbox sizes (uses Powershell script Exchange_push.ps1 that runs every day on the exchangeserver)

Make available as Widget type Pie for the Dashboard!

-- HR 6 juni 2016

-- Get all mailbox sizes

Select

m.display_name as User,

m.size as Bytes,

m.storage

From microsoft_exchange_mailboxes as m

Order By m.size Desc;

List all found Shares on all Windows PC's. Ignore ntfs permissions. Table share_permissions still incomplete with only empty permissions.

-- HR: 08-06-2016

-- Show all Shares of all disks detected

-- And list Only share Permissions (not Ntfs)

SELECT

-- s.id As ShareID,

s.name As ShareName,

s.path As Path,

s.caption As Caption,

p.account As Account,

d.name As PCName,

s.updated_on As 'Last Update'

FROM

shares As s

LEFT JOIN devices As d ON s.computer_id = d.id

LEFT JOIN share_permissions As p ON s.id = p.share_id

WHERE

-- Uncomment share or ntfs

p.permission_type = 'share'

-- p.permission_type = 'ntfs'

AND s.name Is Not 'print$'

-- Find unknown accounts with next line

-- AND p.account Like 'Unknown%' -- (S-1-5-32-549) is Server Operators

ORDER BY PCName, ShareName Asc;

List all detected devices on the network with a Web server

-- HR: 08 jun 2016

-- List all devices with a Web server

SELECT

w.title As Title,

w.port As Port,

w.name As Name,

d.name As PCName

FROM

webservers As w INNER JOIN devices As d ON w.device_id = d.id

ORDER BY

PCName Asc;

List Domain User Accounts with potential dangers

-- HR: 16 maart 2018 - version 1.0

-- Spiceworks SQL Report to show remarkable attributes of Domain User Accounts

-- USERACCOUNTCONTROL flag

-- ACCOUNTDISABLE 0x0002 2

-- LOCKOUT 0x0010 16

-- PASSWD_NOTREQD 0x0020 32

-- PASSWD_CANT_CHANGE 0x0040 64

-- DONT_EXPIRE_PASSWORD 0x10000 65536

-- PASSWORD_EXPIRED 0x800000 8388608

Select

cn As Username,

badpwdcount as BadPwd,

(useraccountcontrol & 2 = 2) as Disabled,

(useraccountcontrol & 16 = 16) as Locked,

(useraccountcontrol & 65536 = 65536) as PwNotExpires,

(useraccountcontrol & 8388608 = 8388608) as PwExpired,

(useraccountcontrol & 32 = 32) as PwNotSet,

(useraccountcontrol & 64 = 64) as PwNotChange

From ad_users

Where (badpwdcount > 0)

Or (Disabled > 0)

Or (Locked > 0)

Or (PwNotExpires > 0)

Or (PwExpired > 0)

Or (PwNotSet > 0)

Or (PwNotChange > 0)

Order By Username Asc;