Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Highlighted
bobbydave
Not applicable

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?

Labels (2)