Search a String & Return Top Values

[gbs01]

I have a text file that is pulled off my router.
I would like to :

1)Query my data listed below and,
2)Return a Query that ranks the data based on the ip address in the string.

For example, I need to know that this
- ip address showed up 950 times
- another ip address showed up 500 times,
- etc.

Basically a Top 100 of all ip address's in the table.

Heres a copy of my table:
Thanks in advance!
jlig


Time Data
---------- --------------------------------
7:47:59 AM .895232 802.1Q vlan#500 P0 12.223.20.58.netbios-dgm > 12.223.20.255.netbios-dgm: NBT UDP PACKET(138)
7:47:59 AM .915047 802.1Q vlan#500 P0 12.223.20.58.netbios-ns > 12.223.20.255.netbios-ns: NBT UDP PACKET(137): QUERY; REQUEST; BROAD
7:47:59 AM .621556 802.1Q vlan#500 P0 12.223.20.18.netbios-ns > 12.223.20.255.netbios-ns: NBT UDP PACKET(137): QUERY; REQUEST; BROAD
7:47:59 AM .957143 802.1Q vlan#500 P0 12.223.20.194.netbios-ns > 12.223.20.255.netbios-ns: NBT UDP PACKET(137): QUERY; REQUEST; BROA
7:47:59 AM .957143 802.1Q vlan#500 P0 12.223.20.194.netbios-ns > 12.223.20.255.netbios-ns: NBT UDP PACKET(137): QUERY; REQUEST; BROA
7:47:59 AM .508170 802.1d config 8000.00:09:5b:31:f2:a9.8005 root 8000.00:07:eb:44:0d:d8 pathcost 58 age 7 max 20 hello 2 fdelay 15
7:48:00 AM .694518 802.1Q vlan#500 P0 12.223.20.194.netbios-ns > 12.223.20.255.netbios-ns: NBT UDP PACKET(137): QUERY; REQUEST; BROA
7:48:00 AM .752186 802.1Q vlan#500 P0 arp who-has 12.223.20.194 tell 12.223.20.58
7:48:00 AM .694518 802.1Q vlan#500 P0 12.223.20.194.netbios-ns > 12.223.20.255.netbios-ns: NBT UDP PACKET(137): QUERY; REQUEST; BROA
7:48:00 AM .642615 802.1Q vlan#500 P0 12.223.20.58.netbios-ns > 12.223.20.255.netbios-ns: NBT UDP PACKET(137): QUERY; REQUEST; BROAD
7:48:00 AM .193786 802.1Q vlan#500 P0 12.223.20.245.netbios-dgm > 12.223.20.255.netbios-dgm: NBT UDP PACKET(138)
7:48:00 AM .642615 802.1Q vlan#500 P0 12.223.20.58.netbios-ns > 12.223.20.255.netbios-ns: NBT UDP PACKET(137): QUERY; REQUEST; BROAD

 

[Crowley16]

first you need to split that into meaningful fields, and then store it in a table

then you can write a query which looks like:
SELECT TOP 100 count(ip) FROM tblName

--------------------
Procrastinate Now!

 

[gbs01]

That would be great but the data cannot be formated in any meaningful way. It comes out of the router in 1 string per second with Time field first.

So I have to bring into Access with 2 fields; Time & Data string.

I know there is a way to search the string in my query & find each ip address, but the last Octet throws me. The last set of numbers can be 2 or 3 digits.

Ex. 12.223.20.217.netbios-ns or it could be 12.223.20.80.netbios-ns

Thanks !

 

[Crowley16]

split it once you've got it into access then...

it's either that or messing about with the instr and mid functions in your sql

--------------------
Procrastinate Now!