1

I have a lot of (huge and chaotic) CSV files with a bunch of (Cisco) serial numbers.

My goal is to extract them (BTW: And to call the Cisco API later on for getting service/support coverage feedback)

Now I'm looking for a proper way to handle that CSV file.

I'm interested if there are others AND also why my initial "preferred" one doesn't work -- it's using a named class that contains a combination of [:alpha:] AND [:digit:].

To decipher the serial number, here's how it is composed.

Cisco S/N format is: LLLYYWWXXXX.

LLL = Location code (i.e. FOC = FoxConn China)

YY = Year code (08 = 2004...09=2005...etc...)

WW = Week code (weeks 01 to 52)

XXXX = Base-34 Alpha Numeric Unique identifier (Includes 0 to 9 & entire alphabet except I & O).

Source: https://community.spiceworks.com/how_to/96973-cisco-device-serial-number-explanation

# Doesn't Work

grep -E -o -w "[[:alnum:]]{11}" Inventory.csv | head
Description
UNIVERSALK9
techsupport
FCW203.....
UNIVERSALK9
techsupport
FCW203.....
UNIVERSALK9
techsupport
FDO201.....
[..]

# Does work

grep -o -w -P '([A-Z]){3}[0-9]{4}[[:alnum:]]{4}' Inventory.csv
FCW1234A1EF
FCW1234A1NG
FDO1234A1KB
FDO1234A103
FOC1234A137
FCW1234A10A
FOC1234A1GH
FOC1234A1GU
[..]
  • I’m curious as to how you got periods out of the [[:alnum:]] version. Did you mask some data or are those real periods? – Jeff Schaller Sep 5 '18 at 15:54
  • My english is not the best so I don't understand "period" in that context, sorry. But the only part I've "faked" are the serial numbers itself. 1234 is faked and the last 4 parts of the serials. – SchmuFoo Sep 5 '18 at 16:13
  • The part after FCW203 has ...... is there other data there? – Jeff Schaller Sep 5 '18 at 16:19
  • Your are totaly right, I have faked the real data (alpha and number) – SchmuFoo Sep 7 '18 at 7:27
0

Your initial grep was too "loose", as regular expressions go. You asked it for [[:alnum:]]{11}, which means 11 "alphanumerics", which allows these non-Cisco-serial-number terms:

  • techsupport
  • Description
  • UNIVERSALK9

Your second grep is "tighter", meaning it is a bit more restrictive in what it will match. It's still not a great fit for a Cisco serial number, though, in two ways:

  • [0-9]{4} will allow 4 digits, zero through nine; this will allow invalid "week" data, such as 0899 or 0900.
  • [[:alnum:]]{4} will allow any 4 alphanumerics, which will allow the forbidden I and O.

Your second grep will catch any and all Cisco serial numbers, since it allows more than the requirements, but it will also be fooled into allowing invalid serial numbers.

I might approach a messy file with awk, because it allows for some powerful pattern matching and string manipulation. The awk script below does two things, at a high level:

  1. loop through every line of input, looking for something that might be a Cisco serial number
  2. on those lines of input, look for every potential match; as long as there's a match, extract that serial number and perform an additional test (checking that the "week" value is valid). You could perform "year" tests here, if you know more about what you expect those values to be. Similarly for the Location code, if you expect a smaller set of locations to show up.

The only other thing I did was to adjust the regular expression to be tighter in regards to the Base-34 section.

Here's the script:

awk '
        BEGIN {
                recisco="[A-Z]{3}[0-9]{4}[0-9ABCDEFGHJKLMNPQRSTUVWXYZ]{4}"
        }
        $0 ~ recisco {
                while(match($0, recisco) > 0) {
                        week=substr($0, RSTART+5, 2);
                        if (week > 1 && week < 54) {
                                print "Found: "substr($0,RSTART,RLENGTH)
                        }
                        $0=substr($0, RSTART+RLENGTH);
                }
        }'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.