Solved

ANY SQL EXPERTS?

Posted on 2009-04-09
14
359 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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 …
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now