Need help Filtering Data from an API

Hi Everyone,

I need help on figuring out a way to filter some data that I get back from an API. Im able to get all the data that Im looking for but I would like to know a way for me to filter it better. The data that Im getting back is basically 2 rows of data as seen here.
Row 1 Row 2

ID|999MOB001|Last Contact>8 d 5 h
ID|999test13|Last Contact>2 h 0 m
ID|testipad1|Last Contact>42 m 7 s

As you can see in Row 2 I get back data that is showing the last time it was contacted.

What Im looking to get done is to get back only lines that have a specific data value that is greater than say 2 d(ays). Below is a copy of my script that Im running. So any help in what I can change to get back only the data that is older than 2 days would be helpful. From what I have been looking at I might have to use perl against my output.txt file to help sort but I wanted to see if there was a way to get this done with out using perl.

curl https:// blah blah.com/api > test.csv

sed -i '/"/d' test.csv

awk -F "\"*,\"*" '{print "ID|" $3 "|Last Contact>" $21}' test.csv > output.txt

Thanks in advance

The following awk command will do that:

awk -F ">" '/Last Contact>.* d/ { if ($2 >= 2) print }' test.csv

That is, consider only lines measured in days - this scheme will fail if you encounter abbreviated month or years in your date format.

Awesome thanks that worked!

Yes its only listing days and hours

Did you consider simplifying and accelerating your code snippet like

curl https:// blah blah.com/api | awk -F ">" '/\"/ {next} /Last Contact>.* d/ { if ($2 >= 2) print }' > output.txt

?

Thanks I tired that as well it works as well!!!

Now I have a new problem how would I set the sort to show any number of days like if I wanted to show data that was 14 D or older? Also the another issue that Im running into is that Im not showing any days that are the number 10. There are some in the file but they do not show up when I pull data that is greater than or equal to 2 days.

M105POD071,CCQML69HFMJF,53 d 2 h,6476BABC3B68,
633MOB43,DMPNRXUQG5VJ,9 d 13 h,908D6C582E1E,
103MOB14,DQTNP2HEG5VJ,17 d 21 h,2C1F23014C4A,
M153POD071,CCQLX7QUF4K1,177 d 12 h,F82793A56130,

My current code with the changes that I needed to make. But how would I edit this to pull greater than or equal to 14 d from $3???

awk -F, '/,* d/ { if ($3 >= 2 && $3 <=100) print }' test.csv

awk -F'[, ]' '$3>=14 && $3<=100' test.csv

Thank you this is helpful but still not 100% what I need.

This brings back data that has h(hours) and m(inutes) I need to get data that is only for x-number of d(ays) and this is also stopping at 100 I need it to go past 100 to like 999 d(ays)

403mob16,DMPNPTV2G5VJ,19 h 11 m,2C1F2305E136,
999MOB001,DMRNL7EHG5VT,27 d 5 h,24A0741E65B8,
621MOB93,DMPNRPB4G5VJ,35 m 7 s,908D6C1B2D06,
M059POD071,C3TJ74NQDNQW,100 d 16 h,BC677843C4C7,

awk -F'[, ]' '$4=="d" && $3>=14 && $3<=100' myFile

just modify the above to modify your integer value ranges.

Fantastic that worked!!!

Any chance you can explain how that worked?

What does the $4=="d" do? Is that looking 4 placed in front of d?

$4 is the forth field. The field separator is specified by -F'[, ]' . So it's either a comma or a space. Now look at your sample data

Ahhh.....now I see it. Thanks Again!

124mob7,iOS 8.3,iPad Air 2,6.3,DMPNRMTHG5VJ,1 h 4 m,908D6C582D84
M215POD2,iOS 8.3,iPod touch 5th gen,6.2,CCQPM37YFMJF,10 d 22 m,48E9F12FD989
M155POD072,iOS 8.2,iPod touch 5th gen,6.3,CCQM129ZF4K1,31 d 3 h,F827939F3D33
M115POD053,iOS 8.3,iPod touch 5th gen,6.3,CCQMCCTGFMJF,3 d 0 h,B03495D4AE6F

awk -F'[, ]' '$9=="d" && $6>=14'

Hi Guys like my name says I'm still struggling with this. The last code I had worked great but now they want more data from our devices and now I'm not able to get back my data by filtering it the same way. I made a change to the awk command to go to the 9th column which I think is correct but I have also tried it with 10 or 11 and I get different results. Any help as to why I'm now not able to get back the data that I want and sort it correctly?

I think my issue is that I have different data in column 3. Sometimes its 2 words and 1 space, 3 words and 2 spaces or 4 words and 3 spaces. Would I need a while loop or something like that? If so can you provide an example?

Thanks

You may want to run this against your sample file:

awk -F"[, ]" '{for (i=1; i<=NF; i++) print "field", i, ": ", $i}' file

This is a nice piece of code THANKS! It showed me that I sometimes have 12 fields or 13 or 14. I would guess that this is where my problem is. This is where I now think a while loop or something might be what I need to address the variance in the column???

Exactly. You've got (at least) two options here - get the field relative to line end (= NF), if that is constant, or, if the file has a constant number of comma separated fields, use only comma as FS, and then split the field with the time data in it.

Could I just remove all "spaces" from the file? Then run the awk command? Using the [,] as field separator?

Try

awk -F"[, ]" '{print $(NF-3)}' file

or

awk -F, '{n=split ($6, T, " "); print T[2]}' file

They both did the same thing they listed the letter after the number in the 6th column. Sorry but Im not sure how I would be able to use this?

Didn't you WANT the letter after the number to find out if it's days, hours, or minutes?

I do but I want to be able to sort off the number of d(ays). So if something is older than 14 days I can get back just the lines that are >=14 in that column.