I have a license log file(TEXTLINE) that contains details of users and time-stamps of when they have taken in or given out a license. It looks like below:
For Ex:
9:43:58 (app_d) IN: "LICENSE_APP1_NAME" A123456@ABCDEFG
9:44:12 (app_d) OUT: "LICENSE_APP1_NAME" A123456@ABCDEFG
Every six hours it also logs in the date like below:
9:12:41 (lmgrd) TIMESTAMP 2/6/2015
It also has the log details when the user is denied a license like below:
6:24:21 (app_d) DENIED: "LICENSE_APP1_NAME" A123456@ABCDEFG (Licensed number of users already reached. (-4,342:10054 ""))
Now I tried using the following command in Log Parser:
Logparser -i:textline -rtp:-1 "Select text from '[LOGFILEPATH]' where text like '%Licensed number of users already reached%'"
Logparser -i:textline -rtp:-1 "Select text from '[LOGFILEPATH]' where text like '%TIMESTAMP%'"
The first gives me a list of all the lines where the log shows License denied and the second gives me all the lines containing date. If I use select '*', it gives three columns namely LogFilename, Index and Text.
I want to combine these two selects. I have tried 'UNION' but didn't work. My desired output is, I need to print all the lines that has lines showing both date and the license denied line like shown below.(according to index)
For Ex: (Plz ignore Line gaps if there are any)
9:12:41 (lmgrd) TIMESTAMP 2/6/2015
9:25:32 (app_d) OUT: "LICENSE_APP1_NAME" A123456@ABCDEFG
10:36:40 (app_d) IN: "LICENSE_APP1_NAME" A123456@ABCDEFG
10:36:53 (app_d) OUT: "LICENSE_APP1_NAME" A123456@ABCDEFG
12:38:13 (app_d) OUT: "LICENSE_APP1_NAME" A123456@ABCDEFG
12:43:58 (app_d) IN: "LICENSE_APP1_NAME" A123456@ABCDEFG
15:12:42 (lmgrd) TIMESTAMP 2/6/2015
15:27:41 (app_d) OUT: "LICENSE_APP1_NAME" A123456@ABCDEFG
16:28:30 (app_d) IN: "LICENSE_APP1_NAME" A123456@ABCDEFG
16:28:54 (app_d) IN: "LICENSE_APP1_NAME" A123456@ABCDEFG
18:29:04 (app_d) OUT: "LICENSE_APP1_NAME" A123456@ABCDEFG
18:30:51 (app_d) OUT: "LICENSE_APP1_NAME" A123456@ABCDEFG
It would be even better if I can display just the date in the date line. Can we trim the rest of it?
Also my future requirement would contain an output with a table that shows a column 'user' with all the users details(Ex: A123456@ABCDEFG), a column 'session' with the number of times that user had a session(based on the number of "IN" and "OUT" in the logs), a column 'Total hours' that uses the difference in time stamps to calculate the time he had used the license and a column 'Denials' which shows the number of times the user had been denied a license due to "Licensed number of users already reached" error. If not the entire code, can I get some help in understanding the idea of how this can be made possible? Thanks in advance.
P.S. A123456@ABCDEFG and LICENSE_APP1_NAME are just shown as example. There are many such users and a few other apps that the license file maintains. Later requirements consist of finding out the users connected to each app from the license file.
Your first question is solved with an OR:
Select text from '[LOGFILEPATH]'
where text like '%Licensed number of users already reached%'
OR text like '%TIMESTAMP%'
Then, you can retain just the date from the "...TIMESTAMP..." lines by using EXTRACT_TOKEN (see http://logparserplus.com/Functions) only on the lines matching TIMESTAMP:
Select CASE INDEX_OF(text, 'TIMESTAMP') WHEN -1 THEN NULL ELSE EXTRACT_TOKEN(text, 3, ' ') END AS Date FROM ...
Finally, your last set of requirements might be a bit overstretching the capabilities of LogParser. First of all, I suggest you switch to the TSV input format, which assumes your log is space-separated and should be able to return the user ID in its own column. Do read it up in the LogParser documentation.
Once you achieve that, you can start using GROUP BY on the user ID and aggregate various counters in the SELECT clause. For example, assuming the TSV format gives you the IN/OUT indicator in the "Field3" column and the user ID in the "Field5" column, the following query will return the total number of IN: for each user:
SELECT Field5 AS User, COUNT(*) AS NumberOfSessions
FROM '[LOGFILEPATH]'
WHERE Field3 = 'IN:'
GROUP BY Field5
This said, the 'TotalHours' requirement is really tricky as it requires a delta between different rows (which is quite complicated to do even on a real SQL database), while the 'Denials' requirement is simply another count, but this time only of messages matching your pattern.