Concantenate qvd with Excel

bobbydave · ‎2019-01-11

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?

General Question

QlikView App Dev