Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ANY SQL EXPERTS?

Posted on 2009-04-09
14
Medium Priority
?
372 Views
Last Modified: 2012-05-06
Hi,

    I'm using Logparser which uses a lite SQL engine and operates and is driven off of SQL syntax.  This is what I'd like to do:

    I'm trying to parse through my authentication logs to determine if there is IP variation from the same User that has logged in.

    For example:  JDOE, shows to have logged in 10 times on 4/9/2009.  9 of those logins show the same IP and EventID (540 in this example).  However 1 of JDOES logins shows a completely different IP address as well as a weird type of Event code (example: shows 800).

    I'd like the SQL statement tell tell me about these variations but haven't quite figured out the correct syntax for it.

    I convert the log file to a CSV with that has the following headers (in order):

DATE, TIME, EVTID, STATUS, DC, DOMAIN, USERNAME,IP,LOGIN-TYPE,AUTH-PROTOCOL

SELECT * FROM \\server\logs\auth.csv  WHERE IP (this is where I need to set the IP range at '000.000.000.000,999.999.999.999')

    I can't figure the correct syntax out and it's driving me nuts, in fact I don't know if it's even possible without employing some sort of script (which I'm hoping is not the case because I'd like to think SQL syntax is advance enough to handle something like this query.  Any help is GREATLY APPRECIATED!!!
0
Comment
Question by:itsmevic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
14 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24111061
Try the following and, if it doesn't work, post the error message(s).
First try SQL_1 to see if that is the correct syntax for accessing the data.
Then, if that works, try SQL_2 to see if that's what you want.
Finally, try SQL_3 (assuming everything else works) to see if that is a little more usable. ;-)

SQL_1:
SELECT * 
FROM \\server\logs\auth.csv;
 
 
SQL_2:
SELECT * 
FROM \\server\logs\auth.csv  
WHERE IP BETWEEN '000.000.000.000' AND '999.999.999.999'
ORDER BY USERNAME, DATE, TIME, IP;
 
SQL_3:
SELECT USERNAME, DATE, TIME, IP, COUNT(IP)
FROM \\server\logs\auth.csv  
WHERE IP BETWEEN '000.000.000.000' AND '999.999.999.999'
GROUP BY USERNAME, DATE, TIME, IP
ORDER BY USERNAME, DATE, TIME, IP;

Open in new window

0
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 24111116
Why not create a new table and bring the informatoin in from the .csv file.  From there you can query the table:

select Distinct Username, IP
from table
group by username, ip
order by username

That will show if someone has only logged in from 1 ip then they will only have 1 line in the table, if they have more then they will have additional lines.
0
 

Author Comment

by:itsmevic
ID: 24111277
Hi Gallitin,

    Thanks for your suggestion, however this is a log file which contains hundreds if not thousands of "different users" not just one.  I probably should of stated that in my original question ( :  To search individually for each user would take forever as you can imagine.  I'd need to run the SQL statement against the "whole" log, with the same logic.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:Nathan Riley
ID: 24111302
Ok, so what your looking to be returned is the username of anyone that has logged in from multiple ip's correct?
0
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 24111349
If so this should return what your looking for:
select username,
case when count(IP) > 1 then IP else null end as IP
from table
where IP is not null
group by username, ip
order by username

Open in new window

0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24111357
If you are really only interested in those that are logging in from multiple IP addresses, then try this:
(By the way, you don't need to worry about the IP address range you are specifying . . . the max is really 255.255.255.255. ;-)

SELECT USERNAME, DATE, TIME, IP_COUNT
FROM
(
SELECT USERNAME, DATE, TIME, COUNT(IP) AS IP_COUNT
FROM \\server\logs\auth.csv  
GROUP BY USERNAME, DATE, TIME
)
WHERE IP_COUNT > 1
ORDER BY USERNAME, DATE, TIME;

Open in new window

0
 

Author Comment

by:itsmevic
ID: 24111822
Hi 8080 - Tried your code below but it's not working for some reason.  Below is exactly how i'm typing it into Logparser.  Perhaps I have a space where one is not needed or vice-versa?  

Logparser -i:CSV -o:DATAGRID "SELECT USERNAME,DATE,TIME,IP_COUNT FROM(SELECT USERNAME,DATE,TIME,COUNT(IP) AS IP_COUNT FROM\\MYSERVER\LOGS\AUTH.CSV GROUP BY USERNAME,DATE,TIME) WHERE IP_COUNT > 1 ORDER BY USERNAME,DATE,TIME
0
 

Author Comment

by:itsmevic
ID: 24111917
Hi Gallitin,

   Thank you for providing input, I tried your code to as well.  Remember, I do not have actual SQL installed on my machine and that Logparser uses a lite engine of SQL so I'm not using "tables" and things of that nature.  It's basically, just a SQL syntax type of parse through a simple header based .CSV file.  
0
 
LVL 12

Accepted Solution

by:
Nathan Riley earned 1000 total points
ID: 24111927
Oh ok then did you change the from part?
select username,
case when count(IP) > 1 then IP else null end as IP
from \\server\logs\auth.csv 
where IP is not null
group by username, ip
order by username

Open in new window

0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 1000 total points
ID: 24111933
You might try the following (it has a space after the from and before the \\).
By the way, does it give any error messages?

Logparser -i:CSV -o:DATAGRID "SELECT USERNAME,DATE,TIME,IP_COUNT FROM(SELECT USERNAME,DATE,TIME,COUNT(IP) AS IP_COUNT FROM \\MYSERVER\LOGS\AUTH.CSV GROUP BY USERNAME,DATE,TIME) WHERE IP_COUNT > 1 ORDER BY USERNAME,DATE,TIME

Open in new window

0
 

Author Comment

by:itsmevic
ID: 24112146
hI 8080,

    I'm assuming the "IP_COUNT" is an clause in SQL that tells it to count.  My headers do not reflect the tag IP_COUNT.  You know I tried your most recent code, copied and pasted it right into Logparser, it keeps telling me either:

1.)  Error opening file "C:\Program Files\Log Parser 2.2\(SELECT USERNAME" : The system cannot find the file specified.

   I don't know why it's saying this because I have the FROM clause pointing directly to the source of the data i.e. \\myserver\logs\auth.csv

  It's almost as if it's not recognizing the statement  FROM(SELECT USERNAME,DATE,TIME,COUNT(IP) AS IP_COUNT FROM \\MYSERVER\LOGS\AUTH.CSV
0
 

Author Comment

by:itsmevic
ID: 24112183
Hi Gallitin,

     Tried your code to directly into Logparser

logparser -i:csv -o:datagrid "select username,case when count(IP) > 1 then IP else null end as IP
from \\server\logs\auth.csv where IP is not null group by username, ip order by username

    Getting this error:
   
        Error:  Syntax Error:  <when-statement>: expecting WHEN keyword instead of token 'count(ip)'

     Remember I'm that I'm just pulling this data from a .csv file and not directly from they system logs on the server.

     See what happens is this, my server generates the log, these logs are in unrecognizable format with weird extensions assigned to them.  From there I run a script, that takes that log, copies and moves it and then renames it to a .CSV file.  When it's in it's .CSV stage, I then launch Log Parser 2.2 and run it against that .CSV file.
0
 

Author Closing Comment

by:itsmevic
ID: 31568712
Very helpful ( :
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24113280
itsmevic,
> I'm assuming the "IP_COUNT" is an clause in SQL that tells it to count.
Actually, the IP_COUNT is an"alias" (i.e. it gives a column name) for  the results of the   COUNT(IP), which is what actually counts the instances.
>My headers do not reflect the tag IP_COUNT.
That's because it is a calculated value that I have given a name to.
>You know I tried your most recent code, copied and pasted it right into Logparser, it >keeps telling me either:

>1.)  Error opening file "C:\Program Files\Log Parser 2.2\(SELECT USERNAME" : The
> system cannot find the file specified.
That is probably because you have in effect, told the Logparser that the filename includes the SELECT statement.  Notice, once again, there is no space after the slash after the filename and before the open parenthesis.

As a passing observation, the answer you Accepted will produce a list of IP addresses that have been used multiple times and a NULL for the IP address if has been used only once.  In addition, if the person logged in from IP address 1 on one day and IP address 2 twice on the next day, it will give you a NULL instead of IP address 1 and will give you the ip address of IP address 2.  From your original question posting, I understood that you were concerned about daily results and not over all results.  
If your user logs in from a different IP address every time he/she logs in, you will have a string of NULL IP addresses.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question