Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?