Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Concantenate qvd with Excel
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
bobbydave
Creator III
2019-01-11
10:46 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Concantenate qvd with Excel
So I have an excel file that I load in weekly
Load *,
'' as Comment
'' as Action
, it then loads this into NPrinting, creates a report, user gets report, adds his Comments and Action (Open/Closed/In Progress), this then is reloaded into Qlikview and stored as a QVD
The following Monday, I receive an excel file again which will load into Qlikview.
I have a Unique Identifier which is made up of QID and Hostname. The UI should check the UI of the QVD and the UI of the new file; if they match and Action = Closed, to remove both UI from the new excel file and the QVD.
FindDataHeaderTemp:
First 500 // set this value large enough that the Header can be found
load
if(A='IP',rowno()) as Header //Row 'A' in an Excel load
From
//'$(vSource)\Scan_Report_*.csv'
//(txt, codepage is 1252, no labels, delimiter is ',');// header is $(vDataHeader) lines);
//(ooxml, no labels, table is Scan_Report_AXA_CLP_Qualys_Repo);
[$(vSource)\Test\Scan_Report_*.xlsx]
(ooxml, explicit labels, table );
FindDataHeader:
NoConcatenate load
//Start with Header = IP
Header
resident FindDataHeaderTemp
where Header>0;
drop table FindDataHeaderTemp;
vDataHeader = peek('Header');
drop table FindDataHeader;
// end data start
Keyword_Mapping:
LOAD RowNo() as KeyID,
[Vulnerability Keywords],
APP
FROM
[$(vMapping)\Keywords.xlsm]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);
Qualys:
//Load 'Table2' as Qualys;
LOAD %Vulnerability,
Description,
Hostname,
Owner,
App,
[IP Address],
[First Detected],
[Last Detected],
[Remediation Date],
Severity,
[Upper Time Limit (Days)],
CVSS,
DNS,
NetBIOS,
[Tracking Method],
[IP Status],
QID,
Title,
[Vuln Status],
Type,
Threat,
Impact,
Solution,
Exploitability,
Category,
[Action Status],
Comment,
[Vulnerability Date]
FROM
[$(vQVDs)\Vulnerability_Reloader.qvd]
(qvd);
Concatenate
//Load 'Table2' as Qualys;
LOAD
G & '-' & ApplyMap('Qualys_Hostname', A, NULL()) as %Vulnerability,
ApplyMap('Qualys_Description', A, 'OTHER') as Description,
ApplyMap('Qualys_Hostname', A, 'OTHER') as Hostname,
ApplyMap('Qualys_IT_App_Owner',A, 'OTHER') as Owner,
A as [IP Address],
Date(Date#(left(P,10), 'MM/DD/YYYY'), 'DD/MM/YYYY') as [First Detected],
Date(Date#(left(Q,10), 'MM/DD/YYYY'), 'DD/MM/YYYY') as [Last Detected],
//Remediation Date
if(
if(AA > W, AA, if(IsNull(AA), W, if(AA < W, W))) = 0, Date(Date#(left(P,10), 'MM/DD/YYYY'), 'DD/MM/YYYY'),
if(
if(AA > W, AA, if(IsNull(AA), W, if(AA < W, W))) >= 0.1 and if(AA > W, AA, if(IsNull(AA), W, if(AA < W, W))) <= 3.9,'31/12/2020',
if(
if(AA > W, AA, if(IsNull(AA), W, if(AA < W, W))) >= 4.0 and if(AA > W, AA, if(IsNull(AA), W, if(AA < W, W))) <= 6.9, Date(Date(Date(Date#(left(P,10), 'MM/DD/YYYY'), 'DD/MM/YYYY')+90), 'DD/MM/YYYY') ,
if(
if(AA > W, AA, if(IsNull(AA), W, if(AA < W, W))) >= 7.0 and if(AA > W, AA, if(IsNull(AA), W, if(AA < W, W))) <= 8.9, Date(Date(Date(Date#(left(P,10), 'MM/DD/YYYY'), 'DD/MM/YYYY')+30), 'DD/MM/YYYY') ,
if(
if(AA > W, AA, if(IsNull(AA), W, if(AA < W, W))) >= 9.0 and if(AA > W, AA, if(IsNull(AA), W, if(AA < W, W))) <= 10.0, Date(Date(Date(Date#(left(P,10), 'MM/DD/YYYY'), 'DD/MM/YYYY')+14), 'DD/MM/YYYY')
)))))
as [Remediation Date],
K as Severity,
if(
if(AA > W, AA, if(IsNull(AA), W, if(AA < W, W)))=0.0, '0',
if(
if(AA > W, AA, if(IsNull(AA), W, if(AA < W, W))) >=0.1 and if(AA > W, AA, if(IsNull(AA), W, if(AA < W, W))) <=3.9, 'Best Effort',
if(
if(AA > W, AA, if(IsNull(AA), W, if(AA < W, W))) >=4.0 and if(AA > W, AA, if(IsNull(AA), W, if(AA < W, W))) <=6.9, 'Less than 90 days',
if(
if(AA > W, AA, if(IsNull(AA), W, if(AA < W, W))) >=7.0 and if(AA > W, AA, if(IsNull(AA), W, if(AA < W, W))) <=8.9, 'Less than 30 days',
if(
if(AA > W, AA, if(IsNull(AA), W, if(AA < W, W))) >=9.0 and if(AA > W, AA, if(IsNull(AA), W, if(AA < W, W))) <=10.0, 'Less than 14 days',
if(
if(AA > W, AA, if(IsNull(AA), W, if(AA < W, W))) >=10.0, 'Over 90 days'
))))))
as [Upper Time Limit (Days)],
//Scoring
if(AA > W, AA,
if(IsNull(AA), W,
if(AA < W, W))) as [CVSS Value],
B as DNS,
C as NetBIOS,
D as [Tracking Method],
F as [IP Status],
G as QID,
H as Title,
I as [Vuln Status],
J as Type,
AD as Threat,
AE as Impact, //[PCI Vuln],
AF as Solution, //[Ticket State],
AG as Exploitability,// Instance,
AM as Category,
'' as [Action Status],
'' as Comment,
Date(Date#(left(SubField(FileName(), '_', 3),8), 'DDMMYYYY'),'DD-MM-YYYY') as Vulnerability_Date,
ApplyMap('Qualys_OS', A, NULL()) as OS_1,
ApplyMap('Qualys_Env', A, NULL()) as Env,
ApplyMap('Qualys_Location', A, NULL()) as Location,
ApplyMap('Qualys_OS_Type', A, NULL()) as [OS Type],
Interval (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') as Detction,
round(if(AA > W, AA, if(IsNull(AA), W))) as [# of Days],
E as OS,
L as Port,
M as Protocol,
N as FQDN,
O as SSL,
R as [Times Detected],
S as [Date Last Fixed],
T as [CVE ID],
U as [Vendor Reference],
V as [Bugtraq ID],
W as CVSS,
X as [CVSS Base],
Y as [CVSS Temporal],
Z as [CVSS Environment],
AA as CVSS3,
AB as [CVSS3 Base],
AC as [CVSS3 Temporal],
AH as [Associated Malware],
AI as [Results],
AJ as [PCI Vuln],
AK as [Ticket State],
AL as Instance
FROM
[$(vSource)\Test\Scan_Report_*.xlsx]
(ooxml, explicit labels, table is *);
//Inner join (Qualys)
//LOAD *
//Where 'No Comment'
//or 'Comment Available';
//
//Load
// //G & '-' & ApplyMap('Qualys_Hostname', A, NULL()),
// %Vulnerability,
// if(count(Comment)<0,-1) as "No Comment",
// if(count(Comment)>0,-1) as "Comment"
// Resident Qualys
// Group by %Vulnerability;
ISSUES_MAP:
MAPPING LOAD
[Vulnerability Keywords],
//APP
'§' & RowNo() & '¨' AS Symbol
RESIDENT
Keyword_Mapping;
Result:
LOAD
Title,
MapSubString( 'ISSUES_MAP', Title ) AS KeywordText,
SubStringCount( MapSubString( 'ISSUES_MAP', Title), '§' ) AS NoKeywords,
TextBetween( MapSubString( 'ISSUES_MAP' , Title), '§', '¨' , IterNo() ) AS KeyID
Resident Qualys
WHILE
IterNo() <= SubStringCount( MapSubString( 'ISSUES_MAP',Title ) , '§' );
//DROP Table Keyword_Mapping;
/*
Concatenate
Load 'Table2' as Qualys;
LOAD %Vulnerability,
Comment,
[Vulnerability Date]
FROM
[$(vQVDs)\Vulnerability_Reloader.qvd]
(qvd);
Inner join (Qualys)
LOAD *
Where "No Comment"
or "Comment Available";
Load
G & '-' & ApplyMap('Qualys_Hostname', A, NULL()),
if(len(Comment)<0,-1) as "No Comment",
if(len(Comment)>0,-1) as "Comment"
Resident Qualys
Group by %Vulnerability;*/
//exit SCRIPT
//STORE Qualys into $(vQVDs)\Qualys.qvd (qvd);
Any ideas?
267 Views
0 Replies
