topic Re: Loading from log file in QlikView

Loading from log file

Mon, 03 Dec 2012 10:37:17 GMT

Loading from log file

I have the below row in a log file.

This log is not set in table formation and I like to load it as table

2012-12-01 00:01:54,328 INFO [ABC] Got request: action=getenv&cname=PEL&id=352622041677225&local=en &serving_ci=17528&serving_lac=6301&manufact=HELLO&model=9000T&os=2.2&version=1.1.12.1&wifi_ssid=&wify_ip=&mobile_apn_name=Internet+PEL&mobile_ip=10.100.00.101&height=800&width=480&ram=304&size=13544& intstorage_size=1917&home_mcc=42&home_mnc=03&service_mcc=42&service_mnc=03&coordinates=32.0278248%2C34.8897514&coordinates_accurate=false&coordinates_last_updated=0&pnames=facebook%7C1%2Cgallery%7C1%2Cplugin%7C1%2CAutocarIndia_Advice%7C1%2Cbbc_world%7C1%2Cyoutube%7C1

The first column should be the date and time (the green part)

The re part can be dropped

Then each column name is set after the ampersand sign (&) and its value after the equal sign (=)

Any ideas how this can be achieve?

Thanks,

Tomer

Re: Loading from log file

jonathandienst — Mon, 03 Dec 2012 11:37:40 GMT

Tomer

Would it be possible to load a small test file with about 100 lines?

Regards

Jonathan

Re: Loading from log file

Gysbert_Wassenaar — Mon, 03 Dec 2012 13:13:30 GMT

Try:

Table:

load logtime, trim(subfield(parval,'=',1)) as parameter, trim(subfield(parval,'=',2)) as value;

load timestamp#(left(@1,23),'YYYY-MM-DD hh:mm:ss,fff') as logtime, subfield(mid(@1,24),'&') as parval;

LOAD @1

FROM

comm67571.log

(txt, codepage is 1252, no labels, delimiter is '\t', msq);

The result will be a table in the form

logtime	parameter	value
2012-12-01 00:01:54,328	cname	PEL
2012-12-01 00:01:54,328	coordinates	32.0278248%2C34.8897514
2012-12-01 00:01:54,328	coordinates_accurate	false
2012-12-01 00:01:54,328	coordinates_last_updated	0
2012-12-01 00:01:54,328	height	800
2012-12-01 00:01:54,328	home_mcc	42

etc..

There will still be a record for "INFO [ABC] Got request: action". If you want to get rid of this you can do a resident load with a where clause: where value <> 'getenv'

Re: Loading from log file

Mon, 03 Dec 2012 14:27:41 GMT

sample can be found in the below link...

https://www.dropbox.com/s/vpqxvli8rdnix4a/Sample.txt

Thanks,

Tomer

Re: Loading from log file

Mon, 03 Dec 2012 14:30:57 GMT

Thanks for that Gysbert ,

Can you please explain a little bit about the syntax and the logic that been using?

Tomer