June 29, 2012 at 12:06 pm
Also, is there a reason to separate the date and time into seperate columns? You may want to keep the date and time together as a datetime data type.
June 29, 2012 at 12:14 pm
Lynn, thanks for all the help. I'm getting the idea. As for splitting the field that contains "UTC" SQL does not know what to do with it, the date format does not seem to want to work no matter what I tried, the only bit that does work is the time segment. All of it would still need to be changed to get it to work, and I need to pull records by date, by time, or both once I get the data into a table, for reporting purposes.
Your soulution seems workable. I was just going to read the entire string into a variable and extract each bit, convert it to whatever was needed and write the variables to my table, then move onto the next one, which seemed logical to me. Now I'm creating a nothe table with text fields for the data elements and then doing what I described above. I'm beginning to think my Access solution was not a bad start:-) This getting confusing. You guys are good though, there is no doubt about that. Makes me realize how much I need to learn.
Rob
June 29, 2012 at 12:20 pm
rjbirkett (6/29/2012)
Lynn, thanks for all the help. I'm getting the idea. As for splitting the field that contains "UTC" SQL does not know what to do with it, the date format does not seem to want to work no matter what I tried, the only bit that does work is the time segment. All of it would still need to be changed to get it to work, and I need to pull records by date, by time, or both once I get the data into a table, for reporting purposes.Your soulution seems workable. I was just going to read the entire string into a variable and extract each bit, convert it to whatever was needed and write the variables to my table, then move onto the next one, which seemed logical to me. Now I'm creating a nothe table with text fields for the data elements and then doing what I described above. I'm beginning to think my Access solution was not a bad start:-) This getting confusing. You guys are good though, there is no doubt about that. Makes me realize how much I need to learn.
Rob
Yes, the columns with the date time is not is a SQL format. Don't worry about that at the moment, keep it as a single character string column for now. That can be dealt with once the data is imported into a table in a more usable format than a single string. Small steps, worry about one thing at a time for now so that you are learning what you need to learn. Some things can be combined later.
June 29, 2012 at 12:27 pm
Step one.
Run the following code, after ensuring you have the delimited split routine I call created. Look at the data returned, does this look correct so far?
with SampleData as (
select * from (VALUES (
'|CALL_END |20 |0 |0 |21 |236 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |7152753911 |7152753911 |0 |0 |7260 |7260 |99 |g711Ulaw64k |20 |10.207.0.14 |8284 |IP |GWAPP_NORMAL_CALL_CLEAR |0 |3119 |4974 |16 |-1 |3807383372162012121829@10.207.60.2 | 12:18:29.000 UTC Jun 21 2012 | 12:18:29.000 UTC Jun 21 2012 | 12:20:09.000 UTC Jun 21 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 12:20:09]'))dt(SampleRec))
select
sd.SampleRec,
--max(case ds.ItemNumber
--when 1 then ds.Item else null end),
max(case ds.ItemNumber
when 2 then ds.Item else null end) [call_status],
max(case ds.ItemNumber
when 3 then ds.Item else null end) [channel_num],
max(case ds.ItemNumber
when 4 then ds.Item else null end) [sipcall_ident],
max(case ds.ItemNumber
when 5 then ds.Item else null end) [trunk_num],
max(case ds.ItemNumber
when 6 then ds.Item else null end) [b_channel],
max(case ds.ItemNumber
when 7 then ds.Item else null end) [sipconf_id],
max(case ds.ItemNumber
when 8 then ds.Item else null end) [trunk_group_num],
max(case ds.ItemNumber
when 9 then ds.Item else null end) [endpoint_type],
max(case ds.ItemNumber
when 10 then ds.Item else null end) [call_originator],
max(case ds.ItemNumber
when 11 then ds.Item else null end) [source_IP],
max(case ds.ItemNumber
when 12 then ds.Item else null end) [destination_IP],
max(case ds.ItemNumber
when 13 then ds.Item else null end) [sourcephone_numtype],
max(case ds.ItemNumber
when 14 then ds.Item else null end) [sourcephone_numplan],
max(case ds.ItemNumber
when 15 then ds.Item else null end) [sourcephone_number],
max(case ds.ItemNumber
when 16 then ds.Item else null end) [sourcephone_nummap],
max(case ds.ItemNumber
when 17 then ds.Item else null end) [destphone_numtype],
max(case ds.ItemNumber
when 18 then ds.Item else null end) [destphone_numplan],
max(case ds.ItemNumber
when 19 then ds.Item else null end) [destphone_number],
max(case ds.ItemNumber
when 20 then ds.Item else null end) [destphone_nummap],
max(case ds.ItemNumber
when 21 then ds.Item else null end) [call_duration],
max(case ds.ItemNumber
when 22 then ds.Item else null end) [call_codec],
max(case ds.ItemNumber
when 23 then ds.Item else null end) [packet_intervall],
max(case ds.ItemNumber
when 24 then ds.Item else null end) [rtp_IP],
max(case ds.ItemNumber
when 25 then ds.Item else null end) [rtp_port],
max(case ds.ItemNumber
when 26 then ds.Item else null end) [call_release],
max(case ds.ItemNumber
when 27 then ds.Item else null end) [termination_reason],
max(case ds.ItemNumber
when 28 then ds.Item else null end) [fax_data],
max(case ds.ItemNumber
when 29 then ds.Item else null end) [packets_in],
max(case ds.ItemNumber
when 30 then ds.Item else null end) [packets_out],
max(case ds.ItemNumber
when 31 then ds.Item else null end) [packets_lost],
max(case ds.ItemNumber
when 32 then ds.Item else null end) [header_id],
max(case ds.ItemNumber
when 33 then ds.Item else null end) [rtp_id],
max(case ds.ItemNumber
when 34 then ds.Item else null end) [callsetup_datetime],
max(case ds.ItemNumber
when 35 then ds.Item else null end) [callconnect_datetime],
max(case ds.ItemNumber
when 36 then ds.Item else null end) [callrelease_datetime],
max(case ds.ItemNumber
when 37 then ds.Item else null end) [rtp_delay],
max(case ds.ItemNumber
when 38 then ds.Item else null end) [rtp_jitter],
max(case ds.ItemNumber
when 39 then ds.Item else null end) [localrtp_source],
max(case ds.ItemNumber
when 40 then ds.Item else null end) [remotertp_source],
max(case ds.ItemNumber
when 41 then ds.Item else null end) [redirect_reason],
max(case ds.ItemNumber
when 42 then ds.Item else null end) [redirect_number],
max(case ds.ItemNumber
when 43 then ds.Item else null end) [redirect_numplan]--,
--max(case ds.ItemNumber
--when 44 then ds.Item else null end),
--max(case ds.ItemNumber
--when 45 then ds.Item else null end)
from
SampleData sd
cross apply dbo.DelimitedSplit8K(SampleRec,'|') ds
group by
sd.SampleRec;
June 29, 2012 at 12:52 pm
This is extremely simple and allows you to do whatever transforms you need. Don't worry about performance. It will be within milliseconds of anything else. Simplicity allows for maintainability. Careful with these RBAR bible thumpers.
declare @calls table (c1 varchar(10), c2 varchar(10), c3 int, c4 int);
declare @t table (rownbr int, token varchar(max));
declare @s-2 varchar(max);
set @s-2 = '|CALL_END |20 |0 |0 |21 |236 |2 |ISDN |TEL '
insert into @t
select rownbr, LTRIM(rtrim(token)) from dbo.StringParse(@s, '|', 1)
insert into
@calls
select
(select case when token is null then 'empty' else token end from @t where rownbr = 1), -- c1
(select token from @t where rownbr = 2), -- c2
(select cast(token as int) from @t where rownbr = 3), -- c3
(select cast(token as int) from @t where rownbr = 4); -- c4
select * from @calls;
June 29, 2012 at 12:52 pm
Violating my own statements here, added conversion steps on the datetime columns directly to the code. Run this and check the output.
with SampleData as (
select * from (VALUES (
'|CALL_END |20 |0 |0 |21 |236 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |7152753911 |7152753911 |0 |0 |7260 |7260 |99 |g711Ulaw64k |20 |10.207.0.14 |8284 |IP |GWAPP_NORMAL_CALL_CLEAR |0 |3119 |4974 |16 |-1 |3807383372162012121829@10.207.60.2 | 12:18:29.000 UTC Jun 21 2012 | 12:18:29.000 UTC Jun 21 2012 | 12:20:09.000 UTC Jun 21 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 12:20:09]'))dt(SampleRec))
select
sd.SampleRec,
--max(case ds.ItemNumber
--when 1 then ds.Item else null end),
max(case ds.ItemNumber
when 2 then ds.Item else null end) [call_status],
max(case ds.ItemNumber
when 3 then ds.Item else null end) [channel_num],
max(case ds.ItemNumber
when 4 then ds.Item else null end) [sipcall_ident],
max(case ds.ItemNumber
when 5 then ds.Item else null end) [trunk_num],
max(case ds.ItemNumber
when 6 then ds.Item else null end) [b_channel],
max(case ds.ItemNumber
when 7 then ds.Item else null end) [sipconf_id],
max(case ds.ItemNumber
when 8 then ds.Item else null end) [trunk_group_num],
max(case ds.ItemNumber
when 9 then ds.Item else null end) [endpoint_type],
max(case ds.ItemNumber
when 10 then ds.Item else null end) [call_originator],
max(case ds.ItemNumber
when 11 then ds.Item else null end) [source_IP],
max(case ds.ItemNumber
when 12 then ds.Item else null end) [destination_IP],
max(case ds.ItemNumber
when 13 then ds.Item else null end) [sourcephone_numtype],
max(case ds.ItemNumber
when 14 then ds.Item else null end) [sourcephone_numplan],
max(case ds.ItemNumber
when 15 then ds.Item else null end) [sourcephone_number],
max(case ds.ItemNumber
when 16 then ds.Item else null end) [sourcephone_nummap],
max(case ds.ItemNumber
when 17 then ds.Item else null end) [destphone_numtype],
max(case ds.ItemNumber
when 18 then ds.Item else null end) [destphone_numplan],
max(case ds.ItemNumber
when 19 then ds.Item else null end) [destphone_number],
max(case ds.ItemNumber
when 20 then ds.Item else null end) [destphone_nummap],
max(case ds.ItemNumber
when 21 then ds.Item else null end) [call_duration],
max(case ds.ItemNumber
when 22 then ds.Item else null end) [call_codec],
max(case ds.ItemNumber
when 23 then ds.Item else null end) [packet_intervall],
max(case ds.ItemNumber
when 24 then ds.Item else null end) [rtp_IP],
max(case ds.ItemNumber
when 25 then ds.Item else null end) [rtp_port],
max(case ds.ItemNumber
when 26 then ds.Item else null end) [call_release],
max(case ds.ItemNumber
when 27 then ds.Item else null end) [termination_reason],
max(case ds.ItemNumber
when 28 then ds.Item else null end) [fax_data],
max(case ds.ItemNumber
when 29 then ds.Item else null end) [packets_in],
max(case ds.ItemNumber
when 30 then ds.Item else null end) [packets_out],
max(case ds.ItemNumber
when 31 then ds.Item else null end) [packets_lost],
max(case ds.ItemNumber
when 32 then ds.Item else null end) [header_id],
max(case ds.ItemNumber
when 33 then ds.Item else null end) [rtp_id],
max(case ds.ItemNumber
when 34 then convert(datetime,replace(substring(right(ds.Item, 12),5,3) + left(right(ds.Item, 12),4) + right(right(ds.Item, 12),5) + ' ' + left(ds.Item, 13),'.',':'),113) else null end) [callsetup_datetime],
max(case ds.ItemNumber
when 35 then convert(datetime,replace(substring(right(ds.Item, 12),5,3) + left(right(ds.Item, 12),4) + right(right(ds.Item, 12),5) + ' ' + left(ds.Item, 13),'.',':'),113) else null end) [callconnect_datetime],
max(case ds.ItemNumber
when 36 then convert(datetime,replace(substring(right(ds.Item, 12),5,3) + left(right(ds.Item, 12),4) + right(right(ds.Item, 12),5) + ' ' + left(ds.Item, 13),'.',':'),113) else null end) [callrelease_datetime],
max(case ds.ItemNumber
when 37 then ds.Item else null end) [rtp_delay],
max(case ds.ItemNumber
when 38 then ds.Item else null end) [rtp_jitter],
max(case ds.ItemNumber
when 39 then ds.Item else null end) [localrtp_source],
max(case ds.ItemNumber
when 40 then ds.Item else null end) [remotertp_source],
max(case ds.ItemNumber
when 41 then ds.Item else null end) [redirect_reason],
max(case ds.ItemNumber
when 42 then ds.Item else null end) [redirect_number],
max(case ds.ItemNumber
when 43 then ds.Item else null end) [redirect_numplan]--,
--max(case ds.ItemNumber
--when 44 then ds.Item else null end),
--max(case ds.ItemNumber
--when 45 then ds.Item else null end)
from
SampleData sd
cross apply dbo.DelimitedSplit8K(SampleRec,'|') ds
group by
sd.SampleRec;
June 29, 2012 at 12:54 pm
I think this is as far as I go for now. You need to review everything provided and work through it so you understand what is going on with the code.
June 29, 2012 at 12:57 pm
Lynn, I am sufficiently humbled. That looks exactly as it does in my VBA string (apart from the returned data has the whole string in the first column.
So, MsgDate is in the current syslog database as yyy-mm-dd so how do I select all the CDR records for today and process them the same way? BTW, SandBox did not exist so I used tempdb which is probably not a good idea(?). I should create a temporary table I guess. Nice job on the script though. I wish I understood how it worked more clearly. You have got me further than I would have made it in two days. I have been reading up on this all morning, on my 4th cup of coffee already....
Rob
June 29, 2012 at 1:03 pm
rjbirkett (6/29/2012)
Lynn, I am sufficiently humbled. That looks exactly as it does in my VBA string (apart from the returned data has the whole string in the first column.So, MsgDate is in the current syslog database as yyy-mm-dd so how do I select all the CDR records for today and process them the same way? BTW, SandBox did not exist so I used tempdb which is probably not a good idea(?). I should create a temporary table I guess. Nice job on the script though. I wish I understood how it worked more clearly. You have got me further than I would have made it in two days. I have been reading up on this all morning, on my 4th cup of coffee already....
Rob
Sandbox is my playground database. No, tempdb is not a good place to put the function. You will need it in the database where you will be calling it. For now, you may want to consider creating a sandbox to play in so that you don't break anything in the other database.
In my code, I am showing the source string. You could actually comment that out and it won't cause a problem. Not sure where MsgDate is in the string, is it one of the columns I commented out or is it a column in your actual source table?
June 29, 2012 at 1:05 pm
Bill Talada (6/29/2012)
This is extremely simple and allows you to do whatever transforms you need. Don't worry about performance. It will be within milliseconds of anything else. Simplicity allows for maintainability. Careful with these RBAR bible thumpers.
declare @calls table (c1 varchar(10), c2 varchar(10), c3 int, c4 int);
declare @t table (rownbr int, token varchar(max));
declare @s-2 varchar(max);
set @s-2 = '|CALL_END |20 |0 |0 |21 |236 |2 |ISDN |TEL '
insert into @t
select rownbr, LTRIM(rtrim(token)) from dbo.StringParse(@s, '|', 1)
insert into
@calls
select
(select case when token is null then 'empty' else token end from @t where rownbr = 1), -- c1
(select token from @t where rownbr = 2), -- c2
(select cast(token as int) from @t where rownbr = 3), -- c3
(select cast(token as int) from @t where rownbr = 4); -- c4
select * from @calls;
You know, I seem to be the only other person responding here so I hope you aren't calling me a RBAR Bible Thumper.
June 29, 2012 at 1:06 pm
Lynn, you have don more than enough. I need to keep reading, but I get the general principle, and I think you can see why CDR processing can be such a pain in the rear end. Never standard, never the same output, and always difficult to work with. I appreciate all the help and the pointers. I'm off to do some reading, I need to get to grips with T-SQL SELECT, WHILE and a few other text processing bits as well as the INSERT statement. So different from SQL language.....
Rob
June 29, 2012 at 1:10 pm
I duplicated the sample record you provided earlier, changed the values in the date columns slightly, commented to source string so that it isn't returned. How is this so far?
with SampleData as (
select * from (VALUES (
'|CALL_END |20 |0 |0 |21 |236 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |7152753911 |7152753911 |0 |0 |7260 |7260 |99 |g711Ulaw64k |20 |10.207.0.14 |8284 |IP |GWAPP_NORMAL_CALL_CLEAR |0 |3119 |4974 |16 |-1 |3807383372162012121829@10.207.60.2 | 12:18:29.000 UTC Jun 21 2012 | 12:18:29.000 UTC Jun 21 2012 | 12:20:09.000 UTC Jun 21 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 12:20:09]'),(
'|CALL_END |20 |0 |0 |21 |236 |2 |ISDN |TEL |10.207.60.2 |10.207.0.10 |2 |1 |7152753911 |7152753911 |0 |0 |7260 |7260 |99 |g711Ulaw64k |20 |10.207.0.14 |8284 |IP |GWAPP_NORMAL_CALL_CLEAR |0 |3119 |4974 |16 |-1 |3807383372162012121829@10.207.60.2 | 12:18:29.000 UTC Jun 22 2012 | 12:18:29.000 UTC Jun 22 2012 | 12:20:09.000 UTC Jun 22 2012 |0 |0 |0 |0 |-1 |0 |0 | |0 <013><010> [Time: 12:20:09]'))dt(SampleRec))
select
--sd.SampleRec,
--max(case ds.ItemNumber
--when 1 then ds.Item else null end),
max(case ds.ItemNumber
when 2 then ds.Item else null end) [call_status],
max(case ds.ItemNumber
when 3 then ds.Item else null end) [channel_num],
max(case ds.ItemNumber
when 4 then ds.Item else null end) [sipcall_ident],
max(case ds.ItemNumber
when 5 then ds.Item else null end) [trunk_num],
max(case ds.ItemNumber
when 6 then ds.Item else null end) [b_channel],
max(case ds.ItemNumber
when 7 then ds.Item else null end) [sipconf_id],
max(case ds.ItemNumber
when 8 then ds.Item else null end) [trunk_group_num],
max(case ds.ItemNumber
when 9 then ds.Item else null end) [endpoint_type],
max(case ds.ItemNumber
when 10 then ds.Item else null end) [call_originator],
max(case ds.ItemNumber
when 11 then ds.Item else null end) [source_IP],
max(case ds.ItemNumber
when 12 then ds.Item else null end) [destination_IP],
max(case ds.ItemNumber
when 13 then ds.Item else null end) [sourcephone_numtype],
max(case ds.ItemNumber
when 14 then ds.Item else null end) [sourcephone_numplan],
max(case ds.ItemNumber
when 15 then ds.Item else null end) [sourcephone_number],
max(case ds.ItemNumber
when 16 then ds.Item else null end) [sourcephone_nummap],
max(case ds.ItemNumber
when 17 then ds.Item else null end) [destphone_numtype],
max(case ds.ItemNumber
when 18 then ds.Item else null end) [destphone_numplan],
max(case ds.ItemNumber
when 19 then ds.Item else null end) [destphone_number],
max(case ds.ItemNumber
when 20 then ds.Item else null end) [destphone_nummap],
max(case ds.ItemNumber
when 21 then ds.Item else null end) [call_duration],
max(case ds.ItemNumber
when 22 then ds.Item else null end) [call_codec],
max(case ds.ItemNumber
when 23 then ds.Item else null end) [packet_intervall],
max(case ds.ItemNumber
when 24 then ds.Item else null end) [rtp_IP],
max(case ds.ItemNumber
when 25 then ds.Item else null end) [rtp_port],
max(case ds.ItemNumber
when 26 then ds.Item else null end) [call_release],
max(case ds.ItemNumber
when 27 then ds.Item else null end) [termination_reason],
max(case ds.ItemNumber
when 28 then ds.Item else null end) [fax_data],
max(case ds.ItemNumber
when 29 then ds.Item else null end) [packets_in],
max(case ds.ItemNumber
when 30 then ds.Item else null end) [packets_out],
max(case ds.ItemNumber
when 31 then ds.Item else null end) [packets_lost],
max(case ds.ItemNumber
when 32 then ds.Item else null end) [header_id],
max(case ds.ItemNumber
when 33 then ds.Item else null end) [rtp_id],
max(case ds.ItemNumber
when 34 then convert(datetime,replace(substring(right(ds.Item, 12),5,3) + left(right(ds.Item, 12),4) + right(right(ds.Item, 12),5) + ' ' + left(ds.Item, 13),'.',':'),113) else null end) [callsetup_datetime],
max(case ds.ItemNumber
when 35 then convert(datetime,replace(substring(right(ds.Item, 12),5,3) + left(right(ds.Item, 12),4) + right(right(ds.Item, 12),5) + ' ' + left(ds.Item, 13),'.',':'),113) else null end) [callconnect_datetime],
max(case ds.ItemNumber
when 36 then convert(datetime,replace(substring(right(ds.Item, 12),5,3) + left(right(ds.Item, 12),4) + right(right(ds.Item, 12),5) + ' ' + left(ds.Item, 13),'.',':'),113) else null end) [callrelease_datetime],
max(case ds.ItemNumber
when 37 then ds.Item else null end) [rtp_delay],
max(case ds.ItemNumber
when 38 then ds.Item else null end) [rtp_jitter],
max(case ds.ItemNumber
when 39 then ds.Item else null end) [localrtp_source],
max(case ds.ItemNumber
when 40 then ds.Item else null end) [remotertp_source],
max(case ds.ItemNumber
when 41 then ds.Item else null end) [redirect_reason],
max(case ds.ItemNumber
when 42 then ds.Item else null end) [redirect_number],
max(case ds.ItemNumber
when 43 then ds.Item else null end) [redirect_numplan]--,
--max(case ds.ItemNumber
--when 44 then ds.Item else null end),
--max(case ds.ItemNumber
--when 45 then ds.Item else null end)
from
SampleData sd
cross apply dbo.DelimitedSplit8K(SampleRec,'|') ds
group by
sd.SampleRec;
June 29, 2012 at 1:14 pm
rjbirkett (6/29/2012)
Lynn, you have don more than enough. I need to keep reading, but I get the general principle, and I think you can see why CDR processing can be such a pain in the rear end. Never standard, never the same output, and always difficult to work with. I appreciate all the help and the pointers. I'm off to do some reading, I need to get to grips with T-SQL SELECT, WHILE and a few other text processing bits as well as the INSERT statement. So different from SQL language.....Rob
Unless you really need to use it, stay away from loops (CURSORS, WHILE LOOPS, etc). They have their place but not necessarily with this process.
As for the delimited split routines, PLEASE read the article and the discussion thread here, http://www.sqlservercentral.com/Forums/Topic1101315-203-4.aspx.
June 29, 2012 at 1:16 pm
Bill, I'm sorry, I was not ignoring you, I was trying to figure out if I could use that function. There are 45 separate fields once the string is split, I would need to edit that to get it to work I think. I appreciate ALL the help from all who take the trouble to reply. I am a real newbie and trying something I probably should have spent more time learning.
Lynn,
MsgDate is in the original syslog table along with the CDR detail record. in C# I'm selecting all the records by MsgDate and processing them all at once. Sorry for the confusion. So, I'm goint to select * from dbo.syslogd where MsgDate = today and then run your script to process the data into a newly created sandbox and then process THAT data and insert it into dbo.CDR so wish me luck:-) I'm going to need it. What else am I going to do this weekend? It's July 4th celebrations, I'm in Chicago, and i'm ENGLISH! One of the rare time I keep my mouth shut:-)
Rob
June 29, 2012 at 1:17 pm
Another good article to read by the same author is the first one in my signature block. It shows you what to post and how to post it to get the best possible answers to many questions.
Viewing 15 posts - 16 through 30 (of 94 total)
You must be logged in to reply to this topic. Login to reply