Solved

ANY SQL EXPERTS?

Posted on 2009-04-09
14
364 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
  • 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 11

Expert Comment

by:N R
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 11

Expert Comment

by:N R
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 11

Expert Comment

by:N R
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 11

Accepted Solution

by:
N R earned 250 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 250 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

809 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