[SOLVED] Select values from multiple rows comprising a record
Linux - GeneralThis Linux forum is for general Linux questions and discussion.
If it is Linux Related and doesn't seem to fit in any other forum then this is the place.
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
Select values from multiple rows comprising a record
Gents,
We have a log file that contains some transaction logs. Each record consists of 5 lines that contain 5 keys-parameters or fields-values pairs as shown below:
Code:
login:olddog
time:Wed Sep 29 15:30:56 GMT 2015
id:NNN1231002
key:K3320030
status:nok
login:abc
time:Wed Sep 30 14:10:18 GMT 2015
id:NNN12312
key:K3323
status:ok
login:xyz
time:Wed Sep 30 15:52:20 GMT 2015
id:NNN123232
key:K33445
status:nok
login:blah
time:Wed Sep 30 16:50:50 GMT 2015
id:NNN1231200
key:K3323787
status:ok
login:blah123
time:Wed Sep 30 18:01:19 GMT 2015
id:XXX89898
key:Njjj34423
status:ok
So, on the 6th line begins a new record, and so on. The above sample log file contains 5 records/transactions.
The requirement is such that we need to show only "login" and "status" (rows) of each transaction/record occurred between 15 and 17 hours for a given date, say "Sep 30" in the above example.
Had the records been in a database-table format (values in columns instead of rows), it would have been easier for me to achieve the result. Here I am really confused.
Is this your homework? What have you tried so far?
No!
Quote:
Originally Posted by pan64
What have you tried so far?
[1] As I said, I am not sure about as to how to work with this sort of log file where fields are on separate lines and instead of usual 1 line = 1 record, 5 lines are making up 1 record and only selected lines are to be picked from each record which is again based on certain conditions such as date/time.
[2] However, I did try this:
"split" the file after every 5 lines so that "wc -l file" / "5 lines" would give those many files, in "/tmp/work/" for example. "grep" those file names that contain the given date. Then use "awk" to compare the time field / value (by splitting the hour and minute fields first) and select lines of our interest by checking the value of "NR".
This approach would work but there could be a better way to do it.
Of course, we are not limited to Bash. I should have said "Shell Scripting" instead. So, awk, sed, and any other tools for our Shell Scripting can be used. However, I am not familiar with Python, Ruby, etc. so examples from those languages would not make it easy for me to understand.
[1] As I said, I am not sure about as to how to work with this sort of log file where fields are on separate lines and instead of usual 1 line = 1 record, 5 lines are making up 1 record and only selected lines are to be picked from each record which is again based on certain conditions such as date/time.
[2] However, I did try this:
"split" the file after every 5 lines so that "wc -l file" / "5 lines" would give those many files, in "/tmp/work/" for example. "grep" those file names that contain the given date. Then use "awk" to compare the time field / value (by splitting the hour and minute fields first) and select lines of our interest by checking the value of "NR".
This approach would work but there could be a better way to do it.
Of course, we are not limited to Bash. I should have said "Shell Scripting" instead. So, awk, sed, and any other tools for our Shell Scripting can be used. However, I am not familiar with Python, Ruby, etc. so examples from those languages would not make it easy for me to understand.
Personally I would write a program.
However I think what you ought to do is offer more exactly what you have tried by posting that within [code] tags and you can show the outcome of your sample as well. This will aid people in providing better assistance for you.
You should consider the need for repeatability as well as variability of this all. You've shown one set of data and it's really a very small sample. If you need to do one thing, once. Then maybe multi-pass the file and get it done, forget the exactness of how you did it and instead remember the techniques you've used. If instead you'll need to do this repeatedly, then take more time, determine how universal each modification step needs to be, run through some tests and then code it within a script.
Gents,
We have a log file that contains some transaction logs. Each record consists of 5 lines that contain 5 keys-parameters or fields-values pairs as shown below:
So, on the 6th line begins a new record, and so on. The above sample log file contains 5 records/transactions. The requirement is such that we need to show only "login" and "status" (rows) of each transaction/record occurred between 15 and 17 hours for a given date, say "Sep 30" in the above example.
Had the records been in a database-table format (values in columns instead of rows), it would have been easier for me to achieve the result. Here I am really confused. Could you please help me with it? Please note: We want to do it in Bash only.
Read the "Question Guidelines" link in my posting signature. As you've been told before, we will be happy to HELP you with it, but where is the effort YOU have put forward so far??? After being here for FIVE YEARS now, shouldn't you have some ideas on how to write a script?? Since you've written scripts before, it seems like you're asking us for a handout.
Since you want a 'database-table format', why don't you just run it through awk/sed, and get it into a format you CAN import into a database? Show us what you've done/tried, and we'll help.
I slitted the file (containing the same data I gave in my original post above) after every 5 lines so that for 25 lines long file I would get 5 files data0 through data4.
Code:
# split data -l 5 -d -a 1 "data"
# ls -l data[0-9]
-rw-r--r--. 1 root root 85 Oct 1 19:28 data0
-rw-r--r--. 1 root root 76 Oct 1 19:28 data1
-rw-r--r--. 1 root root 79 Oct 1 19:28 data2
-rw-r--r--. 1 root root 82 Oct 1 19:28 data3
-rw-r--r--. 1 root root 84 Oct 1 19:28 data4
Then using this grep clause to filter out files that do not contain the date string "Sept 30" and processing each file to print only line numbers 1 and 5:
Code:
# for FILE in `grep -l "Sep 30" data[0-4]` ; do awk '{if(NR==1 || NR==5){print $0;}}' $FILE; done
login:abc
status:ok
login:xyz
status:nok
login:blah
status:ok
login:blah123
status:ok
The only thing I don't know at this stage is how to compare the "time" sub-string. I am just able to look for the date part as shown in the grep clause above. I want to also check for those dates that contain hours between 15 and 17 inclusive- something that should handle this query:
Give me files that contain Sep 30, 2015 and the time is between 15 and 17 hours.
I am able to print out every 1st and 5th lines as shown below (without splitting the file):
Read the "Question Guidelines" link in my posting signature. As you've been told before, we will be happy to HELP you with it, but where is the effort YOU have put forward so far??? After being here for FIVE YEARS now, shouldn't you have some ideas on how to write a script?? Since you've written scripts before, it seems like you're asking us for a handout.
Since you want a 'database-table format', why don't you just run it through awk/sed, and get it into a format you CAN import into a database? Show us what you've done/tried, and we'll help.
I respect you and your guidelines as well. I certainly know scripts and would not look for ready made materials. But I am certainly bold enough to admit that I don't know "How to test for a date/time in 3rd line and then print lines 1st and 5th" using awk or sed or any other such tool in Shell Scripting.
Those who did not get the question, could ask for an improved version of it. No script is required from anybody here. I will do that myself. I do know how to convert a string into date/time in awk but again I don't know how to look ahead in a given line and then get some steps back and print something and then again jump some steps ahead and print something again.
Is this your homework? What have you tried so far?
No. This is not my homework. You should be respectful of others and their questions. Do not bother to write such craps again when you see my posts. And if you had been that much CURIOUS then you should have said: Show me your code that you have written and I/we would see how to improve it.
I am resolving this post as I have been able to look ahead in awk and print by going backward and forward:
Code:
[root@devhost ~]# cat values
a1
b
c
d
e
a2
b
c
d
e
1
2
3
4
5
a3
b
c
d
e
[root@devhost ~]# cat values | awk '{if(NR==1){LINE1=$0;} if(NR == 3 && $0 == "c"){print LINE1; FOUND=1;} if(NR==5){NR=0;if(FOUND==1){print $0; FOUND=0;}}}'
a1
e
a2
e
a3
e
[root@devhost ~]#
Though this works, but this part is not yet clear to me:
how to convert a date string "Wed Sep 29 15:30:56 GMT 2015" into something so that "Sep 29, 2015 Between 14 and 16" hours can be looked for in it and use it in place of "c" above.
I think the personal attack on pan64 is unwarranted. Note that TBOne called you to the carpet with a lot more force. Note that chrism01 similarly echoed pan64's original question which was very middle ground:
Quote:
Is this your homework? What have you tried so far?
I do not feel this warrants such an adamant response on your part.
I fully agree with TBOne's comments:
Quote:
Read the "Question Guidelines" link in my posting signature. As you've been told before, we will be happy to HELP you with it, but where is the effort YOU have put forward so far??? After being here for FIVE YEARS now, shouldn't you have some ideas on how to write a script?? Since you've written scripts before, it seems like you're asking us for a handout.
None of us consider ourselves "experts" in fact when new members have asked questions inquiring how they become experts we all say that we don't consider ourselves as such but instead work at learning new stuff all the time and just experience over that time helps with growing our knowledge base.
If I were to take 1/2 a minute to cross check I'd likely find that TBOne is 100 percent correct, you've been in the forums for 5 years, you've made in excess of 500 posts, and the only thing I haven't checked is whether or not you've written scripts. I'll concede that you have, because experience tells me that TBOne, pan64, and chrism01 are regular advice givers who's advice I respect. Doesn't mean I disrespect your thoughts, advice, and opinions; I just feel you're being too one sided with your push-back in this case.
Being on the forums for 5 years is nothing magical. Posting in excess of 500 times means that you however have been active and thus participating with Linux. To whit, the chatter forums like Non-Nix -> General does not count towards one's post count, therefore you also have participated somewhat actively.
Final conclusion here is that you asked a poorly phrased question and three or more people noted this fact. This is NOT a big deal. Each of us is fully capable of writing a poor question or response, in fact I write incorrect or non-helpful responses a lot. What IS a big deal is "in my humble opinion", you specifically attacking one specific advice giver. Sorry, but they said it before some of the rest of us could say it. So understand that while you're offended, you asked a clinker and a better reaction might be to clarify, as you had done.
No. This is not my homework. You should be respectful of others and their questions. Do not bother to write such craps again when you see my posts. And if you had been that much CURIOUS then you should have said: Show me your code that you have written and I/we would see how to improve it.
Would not that be a better statement from a GURU?
This type of response to a fellow LQ member is not allowed;
Quote:
From LQ Rules
Challenge others' points of view and opinions, but do so respectfully and thoughtfully ... without insult and personal attack. Differing opinions is one of the things that make this site great.
Please consider rereading LQ Rules so you do not fall into other problems.
I suggest that if you find issues with fellow members to press the <Report> button for the post in question so a Moderator can resolve any potential issues.
LQ is a very helpful site for members and we want it to remain that way.
Looking back at my own responses and your cool-some comment on them, I am really smiling now thinking that it is never good to feel frustrated or offended without giving a thing a second thought at least. Ah... I can also recall I was rather irritated because of other matters related to office politics and my behavior perhaps was reflecting the frustration in every way and I was not aware of it. This is amazing that we subconsciously can do things which we would not otherwise.
@pan64:
You are welcome to comment on my posts and you should continue doing that as you have done before.
Quote:
Originally Posted by rtmistler
I think the personal attack on pan64 is unwarranted. Note that TBOne called you to the carpet with a lot more force. Note that chrism01 similarly echoed pan64's original question which was very middle ground:I do not feel this warrants such an adamant response on your part.
I fully agree with TBOne's comments:
None of us consider ourselves "experts" in fact when new members have asked questions inquiring how they become experts we all say that we don't consider ourselves as such but instead work at learning new stuff all the time and just experience over that time helps with growing our knowledge base.
If I were to take 1/2 a minute to cross check I'd likely find that TBOne is 100 percent correct, you've been in the forums for 5 years, you've made in excess of 500 posts, and the only thing I haven't checked is whether or not you've written scripts. I'll concede that you have, because experience tells me that TBOne, pan64, and chrism01 are regular advice givers who's advice I respect. Doesn't mean I disrespect your thoughts, advice, and opinions; I just feel you're being too one sided with your push-back in this case.
Being on the forums for 5 years is nothing magical. Posting in excess of 500 times means that you however have been active and thus participating with Linux. To whit, the chatter forums like Non-Nix -> General does not count towards one's post count, therefore you also have participated somewhat actively.
Final conclusion here is that you asked a poorly phrased question and three or more people noted this fact. This is NOT a big deal. Each of us is fully capable of writing a poor question or response, in fact I write incorrect or non-helpful responses a lot. What IS a big deal is "in my humble opinion", you specifically attacking one specific advice giver. Sorry, but they said it before some of the rest of us could say it. So understand that while you're offended, you asked a clinker and a better reaction might be to clarify, as you had done.
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.
