Key points -
SMS
most of the data is available in GE_MONEY, if not found then check in other tables
SMS, Email
Data is available from 2021 to 2025
********************** Netcore ( SBICARDS ) ClickHouse ********************
ACDS Connection Name – Netcore ClickHouse
Host - 10.250.33.46
Port - 8125
SBICARDS logs data available in below s3 path.
SMS - netcore-history-data/NETCORE/
Email - netcore-history-data/NETCORE_EMAIL/
-------------------------------SMS logs------------------------------------
S3 SMS Folder Path
-------------------
netcore-history-data/NETCORE/SBICARD
netcore-history-data/NETCORE/GESBICARDLONGCODE_RESPONAGE
netcore-history-data/NETCORE/sbicrdapi
netcore-history-data/NETCORE/sbicardbilldeskapi
netcore-history-data/NETCORE/sbismsapi
netcore-history-data/NETCORE/SBICARDEXCALIBUR
netcore-history-data/NETCORE/GESBISMART
netcore-history-data/NETCORE/SBICARDAPI
netcore-history-data/NETCORE/sbicard_api
netcore-history-data/NETCORE/GE_MONEY
SBICARDS SMS logs available in netcore database in below following tables.
DB - netcore
SMS Tables
-----------
SBICARD_vw
GESBICARDLONGCODE_RESPONAGE_vw
sbicrdapi_vw
sbicardbilldeskapi_vw
sbismsapi_vw
SBICARDEXCALIBUR_vw
GESBISMART_vw
SBICARDAPI_vw
sbicard_api_vw
GE_MONEY_vw
#Single day logs
-----------------
- Fetch single day SMS logs.
- Expected runtime will be ~30 Sec to 3 Mins
Sample Query,
SELECT PublishDate,MobileNo,DeliveryStatus,DeliveryTime,SenderID,Keyword,NoOfSMS,RequestID,TransactionID,MessageID,MessageTag,concat('"', replaceAll(replaceAll(replaceAll(Message,'\n',' '),'\r',' '),'"','""'), '"') as Message,Operator,Circle,ReasonCode,Url,MsgMode
FROM netcore.GE_MONEY_vw
WHERE _path LIKE 'netcore-history-data/NETCORE/GE_MONEY/date=2025-09-06%'
AND MobileNo = '919415125569'
#Month wise query
------------------
- Fetch entire month SMS logs.
- Expected runtime will be ~5 Mins to 20 Mins
Sample Query,
SELECT PublishDate,MobileNo,DeliveryStatus,DeliveryTime,SenderID,Keyword,NoOfSMS,RequestID,TransactionID,MessageID,MessageTag,concat('"', replaceAll(replaceAll(replaceAll(Message,'\n',' '),'\r',' '),'"','""'), '"') as Message,Operator,Circle,ReasonCode,Url,MsgMode
FROM netcore.GE_MONEY_vw
WHERE _path LIKE 'netcore-history-data/NETCORE/GE_MONEY/date=2025-09-%'
AND MobileNo = '919415125569'
-------------------------------Email logs------------------------------------
S3 Email Folder path
---------------------
netcore-history-data/NETCORE_EMAIL/sbiacmspepi
netcore-history-data/NETCORE_EMAIL/sbicardcom
netcore-history-data/NETCORE_EMAIL/sbicardinternal
netcore-history-data/NETCORE_EMAIL/sbicardfal
netcore-history-data/NETCORE_EMAIL/sbicardmpin
netcore-history-data/NETCORE_EMAIL/sbicom
netcore-history-data/NETCORE_EMAIL/sbicardturbine
netcore-history-data/NETCORE_EMAIL/sbicardin
netcore-history-data/NETCORE_EMAIL/sbicardcomfal
netcore-history-data/NETCORE_EMAIL/sbicardcomfalc
netcore-history-data/NETCORE_EMAIL/sbicardexcaliburpep
netcore-history-data/NETCORE_EMAIL/sbicardeapplypepi
netcore-history-data/NETCORE_EMAIL/sbicardsmartech20160516
netcore-history-data/NETCORE_EMAIL/sbicardfalconide
SBICARDS Email logs available in netcore_email database in below following tables.
DB - netcore_email
Email Tables
-------------
sbiacmspepi_vw
sbicardcom_vw
sbicardinternal_vw
sbicardfal_vw
sbicardmpin_vw
sbicom_vw
sbicardturbine_vw
sbicardin_vw
sbicardcomfal_vw
sbicardcomfalc_vw
sbicardexcaliburpep_vw
sbicardeapplypepi_vw
sbicardsmartech20160516_vw
sbicardfalconide_vw
#Single day logs
-----------------
- Fetch single day Email logs.
- Expected runtime will be ~30 Sec to 3 Mins
#Sample Query
select Date,XAPI_Header,RecipientEmail,FromEmail,LastModified,FromDomain,RecipientDomain,MailSize,Status,TrID,Subject,RequestedOn,Remarks,LastClick,LatestClickURL,Device,OS,Browser,Tag,MessageID
from netcore_email.sbicardcomfalc_vw
where _path LIKE 'netcore-history-data/NETCORE_EMAIL/sbicardcomfalc/date=2025-09-02%'
and RecipientEmail ='pavanks.space@gmail.com'
#Month wise logs
-----------------
- Fetch entire month Email logs.
- Expected runtime will be ~5 Mins to 20 Mins.
#Sample query,
select Date,XAPI_Header,RecipientEmail,FromEmail,LastModified,FromDomain,RecipientDomain,MailSize,Status,TrID,Subject,RequestedOn,Remarks,LastClick,LatestClickURL,Device,OS,Browser,Tag,MessageID
from netcore_email.sbicardcomfalc_vw
where _path LIKE 'netcore-history-data/NETCORE_EMAIL/sbicardcomfalc/date=2025-09-%'
and RecipientEmail ='pavanks.space@gmail.com'