Re: Inventory Query Help Needed

From: TheBook (TheBook_at_discussions.microsoft.com)
Date: 01/16/05 

Date: Sat, 15 Jan 2005 20:17:02 -0800

Mucho thanks for the help, I see you have a clue here. It took me about 45
minutes thinking about your question concerning the date/time to figure out
why
the results didn't come out quite right. It returned 8 entries for the
computer name diet01, and 14 entries for the computer name security03....that
because of the question you asked about the date and time...Why are there two
datetime columns?.

It's imported in from a text file and looks like this in the text file;
10:44:47 AM 01/10/2005 tab delimited.

In the query analyzer window LoginDate looks like; 2005-10-01 00:00:00.000.
and loginTime looks like 1899-12-30 10:44:47.000.

So, if the two were combined as is "normal", your query would have worked
using the MIN operator, but since they are two different columns, it returned
multiple rows for the same computer name for each time someone logged into it
on a given day, since 2005-10-01 is the MIN, but is so many times due to
multiple logins.

FYI, the below query against the DB returns accurate results
select *
from netcollect
where logindate > '2005-10-01'

So, let's throw out the LoginTime Column, how can the query be modified to
return a distince computer name with all joining table information?

Thanks in advance!!

Jeff

"Adam Machanic" wrote:

> This might do it; I'm not really sure what your LoginDate/LoginTime are,
> though -- how do you have that split up into two columns that are both
> datetime?:
>
> SELECT
> N.ComputerName,
> N.IPAddress,
> N.UserName,
> N.MACAddress,
> S.OSType,
> S.TotalDiskSpace,
> S.FreeDiskSpace,
> S.TotalMemory,
> S.DefaultPrinter,
> S.DefaultPrinterQueue,
> S.CPUType
> FROM NetCollect N
> JOIN SystemCollect S ON N.ComputerName = S.ComputerName
> AND N.IPAddress = S.IPAddress
> WHERE N.LoginTime =
> (SELECT MIN(N1.LoginTime)
> FROM NetCollect N1
> WHERE N1.ComputerName = N.ComputerName)
>
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>
> "TheBook" <TheBook@discussions.microsoft.com> wrote in message
> news:D1201A92-D53A-4927-BBA1-8B93C6C20556@microsoft.com...
> > I have two tables shown below. When someone logs into the network the
> > computer registeres it's information in the two tables shown below, hence
> > duplicating it's computername, ipaddress, and username information in each
> > table (don't ask me why the guy setup it up for two tables instead of
> > combining it into one, it was like that when I was hired).
> >
> > I'm trying to develope a Stored Procedure to give an inventory of all the
> > unique Computers that have logged into the network withing the last 90
> days.
> >
> > As well, I'd like to combine the unique fileds of the two tables.
> > Show the most recen username that logged into it (since computers are
> shared).
> > Show the most recent IP Address only (since they change on dhcp)
> >
> > So the result set would have one row per unique computername on the
> network
> > like this:
> >
> > Computername, username(that logged into the computer last), ipaddress
> (most
> > recent one), macaddress, OSType, TotalDiskSpace, FreeDiskSpace,
> TotalMemory,
> > DefaultPrinter, DefaultPrinterQueue, CPUType
> >
> > I'm a network guy. Anyting outside of a basic select and delete is out of
> > the question for me.
> >
> > Thanks in advance!
> >
> >
> > CREATE TABLE [dbo].[NetCollect] (
> > [IPAddress] [varchar] (50) NULL ,
> > [MACAddress] [varchar] (50) NULL ,
> > [UserName] [varchar] (50) NULL ,
> > [ComputerName] [varchar] (50) NULL ,
> > [LoginTime] [datetime] NULL ,
> > [LoginDate] [datetime] NULL ,
> > [Pagenet] [varchar] (50) NULL
> > ) ON [PRIMARY]
> > GO
> >
> > CREATE TABLE [dbo].[SystemCollect] (
> > [IPAddress] [varchar] (50) NULL ,
> > [ComputerName] [varchar] (50) NULL ,
> > [UserName] [varchar] (50) NULL ,
> > [OSType] [varchar] (50) NULL ,
> > [TotalDiskSpace] [varchar] (50) NULL ,
> > [FreeDiskSpace] [varchar] (50) NULL ,
> > [TotalMemory] [varchar] (50) NULL ,
> > [DefaultPrinter] [varchar] (100) NULL ,
> > [DefaultPrinterQueue] [varchar] (100) NULL ,
> > [CPUType] [varchar] (100) NULL
> > ) ON [PRIMARY]
> > GO
> >
>
>
>

Relevant Pages

  • Re: NDIS Drivers, Symbolic Names, Ethernet send/receive etc.
    ... (I retrieved these NDIS and IP strings from QueryDosDevice) ... >> Does windows xp automatically install NDIS drivers for detected network ... Pointer to a buffer that contains the query OID that NDIS should use. ... Specifies the size, in bytes, of the query OID at lpInBuffer. ...
    (microsoft.public.win32.programmer.networks)
  • RE: LDAP query for xerox 3545 printer/scanner blocked by SBS 2003?
    ... You also cannot query using the LDP tool. ... Step2:Please ensure the DNS on workstation is pointed to SBS server. ... Run Network Monitor on the SBS server to capture the network traffic ... Monitor will capture some packets which pass through this interface. ...
    (microsoft.public.windows.server.sbs)
  • Re: Accessing a paradox table from a C# application...
    ... I would get a copy of Paradox and play around with restructuring the table and indexes and see what happens to query speed. ... There are several threads on the optimal way to set up a network. ... SELECT TOP 1000 * FROM ModelSer ...
    (comp.databases.paradox)
  • Re: Slow Database after splitting
    ... If it is on the network, this can have a negative impact on performance. ... Yes the Form 2 Query is not a proper name, was just used for saving the ... query built in the record source, then removing the record source, and ... By splitting I mean the database is still in Access .mdb. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Profiler sees very different durations for the same SQL select stmt of spid(s) from different c
    ... you've got a network problem with spid 180. ... are then able to test the network without having to install Query Analyzer ... that would still occupy at least 97 MB in memory on the client. ... it may be a connection issue. ...
    (microsoft.public.sqlserver.server)