SQL Query in SIM 6.1

[Franny]

Hi, I'm a SQL newbie but I've managed to run a SQL query against our SQL 2008 database backend (separate server from SIM 6.1). However, I don't know how to exclude the CDROM and floppy disk drives from the report. Can anyone help? I'm guessing I need to put something like a WHERE description = 'CDROM' but I need help with the syntax.

Also, what do you do with the results of the query? I'd like to email it in htm format or Excel or save it to a share. Anything that looks pretty, is automated and accessible.

I've pasted code below that I'm using from "Show SQL Queries".

I find it very frustrating that I'm having to learn to be a SLQ developer because SIM won't do the reports that I want.

SQL query used to generate the data in the table: Logical Disk Drives

select R_LogicalDisks.DeviceName, R_LogicalDisks.Description, R_LogicalDisks.SizeMb, R_LogicalDisks.UsedMb, R_LogicalDisks.UsedPercent from R_LogicalDisks WHERE ((R_LogicalDisks.SnapShotID=-1) OR (R_LogicalDisks.SnapShotID is NULL)) AND DeviceKey in (select devices.deviceKey from devices LEFT OUTER JOIN notices ON devices.deviceKey = notices.deviceKey LEFT OUTER JOIN DB_deviceInfoEx ON devices.deviceKey = DB_deviceInfoEx.deviceKey LEFT OUTER JOIN IPAddress ON devices.deviceKey = IPAddress.deviceKey and IPAddress.ipIndex = 0 LEFT OUTER JOIN associated_device_data ON devices.deviceKey = associated_device_data.deviceKey LEFT OUTER JOIN deviceProtocolInfo ON devices.deviceKey = deviceProtocolInfo.deviceKey LEFT OUTER JOIN simAggrStatus ON devices.deviceKey = simAggrStatus.deviceKey LEFT OUTER JOIN DeviceStatusValue ON devices.deviceKey = DeviceStatusValue.deviceKey and DeviceStatusValue.StatusSourceId= 6 JOIN consolidatedNodeAuths ON devices.mxGuid = consolidatedNodeAuths.NodeId and consolidatedNodeAuths.userID = '0000005056870c0b0000000400000006' WHERE ( ( devices.productType = 1) ) ) order by R_LogicalDisks.DeviceName ASC


thanks,
Franny

[Rob Buxton]

You're going to have to learn some kind of scripting language if you want to do this kind of stuff.
The SQL command you're missing is: "<>"
e.g. in a perl script I have the SQL is:
SELECT CAST (CIM_LogicalDisk.SystemName as CHAR(35)),
CAST (CIM_LogicalDisk.Description as CHAR(30)),
CIM_LogicalDisk.dc_PercentSpaceUsed, CAST (CIM_LogicalDisk.DeviceID as CHAR(25))
FROM CIM_LogicalDisk
WHERE (CIM_LogicalDisk.SnapshotID=-1) AND
(CIM_LogicalDisk.dc_PercentSpaceUsed>='80') AND
((CIM_LogicalDisk.Description<>'Network Connection') AND
(CIM_LogicalDisk.Description<>'3 1/2 Inch Floppy Drive') AND
(CIM_LogicalDisk.Description<>'CD-ROM Disc')AND
(CIM_LogicalDisk.SystemName Not Like '10.%'))
order by CIM_LogicalDisk.SystemName ASC

It's also a bit primitive, but I'm not a developer either.
The CAST command sets the length of the output, makes it easier for me to cut up and process.
This just extracts those disks that are over 80% full. Ignores various devices and items listed as IP addresses only.

[Franny]

thanks for your response. I don't know anything about Perl but I downloaded Strawberry Perl to give your script a go but I'm not sure how to connect to the SQL DB to run it, and running it standalone brought up syntax errors. I've spent all day reading beginners guide to Perl tutorials.

I have a script from Microsoft's web site that will read in a list of servers and give me a disk usage report but I don't want to have to maintain the list manually, I'd rather get the list from SIM so that it's always up-to-date.

If there's a CD in the drive the standard logical disk report from Managed Reports says the disk is 100% full, some of the servers are remote so it's not practical to eject the CDs so they don't show up in the report.

We would also like to have a disk report that only shows C and D drives. It's very time consuming all of this.